I am trying to run the following query with VBA when the worksheet in Excel is opened:
Private Sub Workbook_Open()
Dim mine As Worksheet
Dim runsheet As Worksheet
Dim sheetcount As Integer
sheetcount = 2
Do Until sheetcount = ThisWorkbook.Sheets.Count + 1
Set runsheet = Sheets(sheetcount)
runsheet.Visible = xlSheetHidden
sheetcount = sheetcount + 1
Loop
Run ("Josh1"
End Sub
Where "Josh1" is:
Sub Josh1()
'
' Josh1 Macro
' Macro recorded 17/01/02 by M700NTBUILD
'
'Refreshes data in pivot table
Sheets("Data".Visible = True
Sheets("Data".Select
ActiveSheet.PivotTables("PivotTable2".RefreshTable
Sheets("Data".Visible = False
End Sub
It all works fine except that the pivottable refresh runs twice when the worksheet is opened. Anyone have any ideas as to how to make it only run the once?
Private Sub Workbook_Open()
Dim mine As Worksheet
Dim runsheet As Worksheet
Dim sheetcount As Integer
sheetcount = 2
Do Until sheetcount = ThisWorkbook.Sheets.Count + 1
Set runsheet = Sheets(sheetcount)
runsheet.Visible = xlSheetHidden
sheetcount = sheetcount + 1
Loop
Run ("Josh1"
End Sub
Where "Josh1" is:
Sub Josh1()
'
' Josh1 Macro
' Macro recorded 17/01/02 by M700NTBUILD
'
'Refreshes data in pivot table
Sheets("Data"
.Visible = TrueSheets("Data"
.SelectActiveSheet.PivotTables("PivotTable2"
.RefreshTableSheets("Data"
.Visible = FalseEnd Sub
It all works fine except that the pivottable refresh runs twice when the worksheet is opened. Anyone have any ideas as to how to make it only run the once?
