Excel Basic VBA

Question

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.

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

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

edited May 23 '17 at 11:56

Community

1
1

answered Nov 29 '14 at 18:31

Doug Glancy

27,214
6
67
115

simplicity is so nice. – peege Nov 29 '14 at 18:40

score 0 · Answer 2 · answered Nov 29 '14 at 18:31

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

score -2 · Answer 3 · edited Aug 11 '23 at 14:15

It looks like the issue you're facing is related to how the macro is returning to the correct sheet after execution. The problem arises because you're using Application.Goto "StartCell" to navigate back, but this doesn't remember the context of the sheet you're coming from. Instead, you're always returning to the cell named "StartCell" in whatever sheet is currently active.

To fix this, you can store the name of the sheet where the macro is activated before switching to the "Data" sheet, and then return to that sheet after completing the updates. Here's the modified code:

Sub sheetupdate()

    Dim startSheet As Worksheet
    Set startSheet = ActiveSheet ' Store the current sheet
    Application.Goto Range("A1"), True
    Range(ActiveCell.Address).Name = "StartCell"

    ' Dataupdate Macro
    Sheets("Data").PivotTables("PivotTable1").RefreshTable

    Application.Goto "StartCell"

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

    ActiveWorkbook.RefreshAll
    Application.Goto "StartCell"

    startSheet.Select ' Return to the original sheet
    Range("StartCell").Select ' Go back to the cell where the macro was activated
End Sub

In this modified code, startSheet is used to store the active sheet before switching to the "Data" sheet. After completing the updates, the code selects the original sheet using startSheet.Select, and then selects the cell named "StartCell" in that sheet.

This way, regardless of which month-sheet you're running the macro from, it should return to the correct sheet and cell.

Excel Basic VBA

3 Answers3