Below is a portion of VB that I use in Access to populate an excel worksheet.
Everything works just fine until: wksRTPT.Range("A1").Select. I continually errored out (error 1004: select method of range class failed) UNTIL I added the line wksRTPT.Activate before it. Whey would the lines of code before work and this one little line not work until I activate the worksheet explicity? To update the pivot tables I had to implicitly be on that worksheet. I'm confused. Marie
Dim wkbTemplate As Excel.Workbook
Dim wksRTPT As Excel.Worksheet
Set wkbTemplate = GetObject(strFilePath & "DailyFlash.xlt")
Set wksRTPT = wkbTemplate.Worksheets("RTPT")
'Refresh Pivot Tables
For i = 1 To wksRTPT.PivotTables.Count
wksRTPT.PivotTables(i).RefreshTable
Next
wksRTPT.Cells.EntireColumn.AutoFit
wksRTPT.Columns("A:A").ColumnWidth = 15.57
wksRTPT.Activate
wksRTPT.Range("A1").Select
Everything works just fine until: wksRTPT.Range("A1").Select. I continually errored out (error 1004: select method of range class failed) UNTIL I added the line wksRTPT.Activate before it. Whey would the lines of code before work and this one little line not work until I activate the worksheet explicity? To update the pivot tables I had to implicitly be on that worksheet. I'm confused. Marie
Dim wkbTemplate As Excel.Workbook
Dim wksRTPT As Excel.Worksheet
Set wkbTemplate = GetObject(strFilePath & "DailyFlash.xlt")
Set wksRTPT = wkbTemplate.Worksheets("RTPT")
'Refresh Pivot Tables
For i = 1 To wksRTPT.PivotTables.Count
wksRTPT.PivotTables(i).RefreshTable
Next
wksRTPT.Cells.EntireColumn.AutoFit
wksRTPT.Columns("A:A").ColumnWidth = 15.57
wksRTPT.Activate
wksRTPT.Range("A1").Select