Excel Basic VBA

0
votes

I have a minor issue with my VBA code. The purpose of the code is to update first one "data" pivot-table and then all other pivot tabels afterwards.

Basically I have a data sheet, and one sheet for every month. From any month-sheet it should be possible to press a button that actives a macro, that firstly updates the datasheet (the pivot-table ), and then afterwards update all other pivottables, and then finally returning to the month sheet, where the macro was activated. So far I have the following VBA-code:

Sub sheetupdate()
'
' sheetupdate Macro
'
    Application.Goto Range("A1"), True
    Range(ActiveCell.Address).Name = "StartCell"
'   Dataupdate Macro
'
    Sheets("Data").Select
    ActiveSheet.PivotTables("PivotTable1").RefreshTable

    Application.Goto "StartCell"

    ActiveCell.Offset(7, 22).Range("A1").Select


    ActiveWorkbook.RefreshAll
    Application.Goto "StartCell"

End Sub

The only issue I have is returning to the correct sheet after running the macro. E.g. when running the macro from the February sheet, it returns to cell A1 in the January sheet, and not cell A1 in the February sheet, as I would like it to.

2
vbaexcel

2 Answers

1
votes

Happily for you, you can, and should, do all this without having to Activate or Select:

Sub sheetupdate()
'
' sheetupdate Macro
    Sheets("Data").PivotTables("PivotTable1").RefreshTable
    ActiveWorkbook.RefreshAll

End Sub
0
votes
Sub sheetupdate(sheetname as Excel.Worksheet)
'
' sheetupdate Macro
'
'   Dataupdate Macro
'
    Sheets("Data").PivotTables("PivotTable1").RefreshTable
    ThisWorkbook.RefreshAll
    sheetname.Activate

End Sub

should accomplish what you would like. Pass in the sheetname in you _Click event