Running macro in multiple sheets

[jperry123]

Hi all, I have a big excel file with over 20 sheets, each with a large amount of formulae. As it takes too long to open the worksheet, calculate the formulae and even saving the file, I've devised a VBA code to perform the formulae of each worksheet in a macro. Annoyingly this macro takes too long to run (sometimes over 5 minutes) whilst calculating the old file takes half that time.

I was wondering if there was a away to execute the code to each worksheet but not take a long time.

A sample of the code which I am using is as follows:

Sub updateall()
Dim t As Double
t = Timer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveWorkbook.Sheets("Cold").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (2)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (2)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (3)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (3)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (4)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (4)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (5)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (5)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (6)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (6)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (7)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (7)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (8)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (8)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (9)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (9)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Cold (10)").Activate
Call updatesheet
ActiveWorkbook.Sheets("Hot (10)").Activate
Call updatesheet
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Calculate
MsgBox (Timer - t)
End Sub
Sub updatesheet()
Dim i As Integer
Dim j As Integer
For j = 0 To 51
    For i = 0 To 4
        Cells(9 + j, 4 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C4,""Vegetable"",R7C4,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 4 + i) = Cells(9 + j, 4 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 10 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C4,""Vegetable"",R7C10,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 10 + i) = Cells(9 + j, 10 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 16 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C4,""Vegetable"",R7C16,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 16 + i) = Cells(9 + j, 16 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 29 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C29,""Vegetable"",R7C29,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 29 + i) = Cells(9 + j, 29 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 35 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C29,""Vegetable"",R7C35,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 35 + i) = Cells(9 + j, 35 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 41 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C29,""Vegetable"",R7C41,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 41 + i) = Cells(9 + j, 41 + i).Value
    Next i
Next j
End Sub

I've heard about arrays and tried to implement them like I have my other macros but cannot seem to as I am pretty new to VBA

Apologies if this post has contained errors as it is my first, many thanks in advance.

 

[AkaTrouble]

i am not going to be able to help with actual solution but could i suggest

you reply with plain in normal words what you have and what you want to achieve

for example

"i have 20 sheets and want to loop through each one and total the first column in the last cell i have a sheet called master that i want to exclude"

this will maybe get you more responses than your existing code as we do not know layout and what the updatesheet macro does

 

[jperry123]

apologies akatrouble, the updatesheet macro is the second half of the code I posted, i'll repost this macro to make it easier to view

Sub updatesheet()
Dim i As Integer
Dim j As Integer
For j = 0 To 51
    For i = 0 To 4
        Cells(9 + j, 4 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C4,""Vegetable"",R7C4,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 4 + i) = Cells(9 + j, 4 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 10 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C4,""Vegetable"",R7C10,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 10 + i) = Cells(9 + j, 10 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 16 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C4,""Vegetable"",R7C16,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 16 + i) = Cells(9 + j, 16 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 29 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C29,""Vegetable"",R7C29,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 29 + i) = Cells(9 + j, 29 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 35 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C29,""Vegetable"",R7C35,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 35 + i) = Cells(9 + j, 35 + i).Value
    Next i
    
    For i = 0 To 4
        Cells(9 + j, 41 + i) = "=IFERROR(GETPIVOTDATA(""Sales"",Pivot!R3C1,""Date"",RC3,""Country"",R6C29,""Vegetable"",R7C41,""Year"",R8C,""Weather"",R7C3),0)"
        Cells(9 + j, 41 + i) = Cells(9 + j, 41 + i).Value
    Next i
Next j
End Sub
[code/]

Basically I want to fill in cells using the GetPivotData formula. but there are around 2000 cells per worksheet which have this formula.  I have around 30 worksheets and only want to run this macro (above) on 20 of them (namely worksheets 5 to 25), so that the cells are not filled with this formula, so it can run a lot smoother.

If I could i'd attach my sample excel file which I created to try and explain the problem a lot easier.

There are also other formulas in the worksheet that I want to keep, hence the calculate command at the end of the code so it can calculate all the other formula (which is not taking too much space so don't mind leaving there)

 

[AkaTrouble]

if you remove any sensetive data and upload to file hosting site like dropbox or to your cloud account and set public you can post a link here to the file

many can d/l but some help on forum at work and have security restrictions on external websites just for information

 

[jperry123]

oh yeah brilliant idea akatrouble thank you! i attach the dropbox link with the sample data.

https://www.dropbox.com/s/v7c6xp2w9r0lm2s/sample data (1).xlsm?dl=0