VBA code run time error 1004

[kaseyleigh]

Hi,

I get Run-Time error '1004' and I think it's because my sheet doesn't have focus when trying to select the Pivot Tables, I need some assistance with correcting my code.

I have 3 sheets, 1, 2 & 3 and there are Pivot Tables on 2 & 3 only.

I need to amend my code so it can run more efficiently. Also, if you have any tips I'd be very grateful too.

Much appreciated,
K

[cpp]Sub Refresh_PTables_Print()

'Refresh all Pivot Tables in this Workbook
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
pt.TableRange2.Select
Selection.EntireRow.Hidden = False

Next pt
Next ws

'Then Print out Expense Claim
With Worksheets("Expense Claim")
.PageSetup.PrintArea = "Print_Area_Exp_Claim"
.PageSetup.Orientation = xlLandscape
.PageSetup.FitToPagesTall = 1
.PageSetup.FitToPagesWide = 1
.PageSetup.PaperSize = xlPaperA4
.PageSetup.TopMargin = 28
.PageSetup.CenterHorizontally = True
.PageSetup.BlackAndWhite = True
.PrintOut
End With

'Then Print out Payment Requisition
With Worksheets("Payment Requisition")
.PageSetup.PrintArea = "Print_Area_Pay_Req"
.PageSetup.Orientation = xlPortrait
.PageSetup.FitToPagesTall = 1
.PageSetup.FitToPagesWide = 1
.PageSetup.PaperSize = xlPaperA4
.PageSetup.TopMargin = 28
.PageSetup.CenterHorizontally = True
.PrintOut
End With

'Then Print out GL Posting
With Worksheets("GL Posting")
.PageSetup.PrintArea = "Print_Area_GL_Post"
.PageSetup.Orientation = xlPortrait
.PageSetup.FitToPagesTall = 1
.PageSetup.FitToPagesWide = 1
.PageSetup.PaperSize = xlPaperA4
.PageSetup.TopMargin = 28
.PageSetup.CenterHorizontally = True
.PrintOut
End With

End Sub[/cpp]

 

[nikorr]

Try http://support.microsoft.com/kb/210684

 

[kaseyleigh]

Thanks nikorr, but this isn't the issue I am having.

If I run the code from Sht 2 it partly works as the sheet is active but fails when needs to refresh the pt on Sht 3. If I start the code from Sht 1 I get the Run-Time error.

 

[nikorr]

Good luck, someone will help u soon.

 

[diddon]

I've just had a very similar problem, and it was because my code was written against the first worksheet.

Moving the code to a module fixed the problem.