Simultaneously run Macro on Multiple worksheet? Please Help!

[jassyphee]

I'm having the hardest time trying to figure out how to run the macro on multiple sheets simultaneously. Therefore, the data should update for all 4 sheets, but that doesn't happen. Instead only the first worksheet gets update. I know the issue has to do with the first couple of lines. Here's the code I've been using:

Sub AutoNumber()    Worksheets("Cap. Rec.").Select
    Worksheets("Cap. Disb.").Select False
    Worksheets("Rev. Rec.").Select False
    Worksheets("Rev. Disb.").Select False
    
    Dim bottomrow As Long
   
    bottomrow = Cells(Rows.Count, "B").End(xlUp).row
    
    Range("A5").Formula = "=A4+1"
    Range("'Cap. Disb.'!A4").Formula = "=INDEX('Cap. Rec.'!A5:A1048576, COUNTA('Cap. Rec.'!A5:A1048576), 1)"
    Range("'Rev. Rec.'!A4").Formula = "=INDEX('Cap. Disb.'!A5:A1048576, COUNTA('Cap. Disb.'!A5:A1048576), 1)"
    Range("'Rev. Disb.'!A4").Formula = "=INDEX('Rev. Rec.'!A5:A1048576, COUNTA('Rev. Rec.'!A5:A1048576), 1)"
    
    Application.ScreenUpdating = False
    Range("A5").AutoFill Destination:=Range("A5:A" & bottomrow), Type:=xlFillDefault
    Application.ScreenUpdating = True
    
   
End Sub

Thank you for the help! GREATLY APPRECIATE IT.

 

[Teeroy]

You can't run a macro on multiple sheets simultaneously but you can run them sequentially. You've given the code you've tried but not really indicated what you want it to do. Can you supply a brief statement of intent as to what you want to achieve?

 

[jassyphee]

Alright, what i'm trying to do is have sequential numbering in 4 of my worksheets, so that as soon as the numbering ends in column A of sheet1, next worksheet will continue the count/numbering.

You can't run a macro on multiple sheets simultaneously but you can run them sequentially. You've given the code you've tried but not really indicated what you want it to do. Can you supply a brief statement of intent as to what you want to achieve?

 

[Teeroy]

Try the following:

Sub AutoNumber2()
    Application.ScreenUpdating = False
    Sheets("Cap. Rec.").Range("A4").Value = 1
    Sheets("Cap. Disb.").Range("A4").Formula = "=MAX('Cap. Rec.'!A5:A" & Rows.Count & ")+1"
    Sheets("Rev. Rec.").Range("A4").Formula = "=MAX('Cap. Disb.'!A5:A" & Rows.Count & ")+1"
    Sheets("Rev. Disb.").Range("A4").Formula = "=MAX('Rev. Rec.'!A5:A" & Rows.Count & ")+1"
    For Each shtName In Array("Cap. Rec.", "Cap. Disb.", "Rev. Rec.", "Rev. Disb.")
        With Sheets(shtName)
            .Range("A5").Formula = "=A4+1"
            .Range("A5").AutoFill Destination:=.Range("A5:A" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        End With
    Next
    Application.ScreenUpdating = True
End Sub