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.

Community · Accepted Answer · 2017-05-23 11:56:58Z

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, 2017 at 11:56

CommunityBot

11 silver badge

answered Nov 29, 2014 at 18:31

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

peege Over a year ago

simplicity is so nice.

cronos2546 · Accepted Answer · 2014-11-29 18:31:27Z

0

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

answered Nov 29, 2014 at 18:31

cronos2546

1,1067 silver badges16 bronze badges

Collectives™ on Stack Overflow

Excel Basic VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related