Excel macro modification ( or maybe VBA ) to update the last included worksheet

2
votes

Situation

Trying to copy a range of cells that include formulas from worksheet called "Sheet1" to the rest of other worksheets I found that I could do it performing "Fill Across Worksheets". It worked fine, so my next step was to record a Macro for it to be more efficient and worked just fine too.

The Problem

The problem is that when I include a new worksheet and run the Macro, the Macro does not consider the new worksheet so this last worksheet doesn't get updated.

I am including below the code created by the macro. In it I can see that it's including only the worksheets I have now in the workbook, so this is where I need help.

( My excel is in Spanish so when you read Ctrl+Mayus+Q, Mayus means Shiftkey )

Help

What I need is a way to modify this Macro so when it runs it will check and update all worksheets. Or, maybe it's because a Macro can't do this I may need a VBA code ? If this VBA is the way to resolve it, can you help me here with this ?

I appreciate all help

Thank you

Javier

Sub Macro2()
'
' Macro2 Macro
'
' Acceso directo: Ctrl+Mayús+Q
'
    Range("A5:D12").Select
    Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.FillAcrossSheets Range:=Selection, 
Type:=xlAll
    Sheets("Sheet1").Select
End Sub
1
vbaexcel
The last sheet is not included in the array probably. You might want to search into looking a For Each loop. – Luuklag

1 Answers

1
votes

This is a work around, concerning that you want all the worksheets to have the value of the first worksheet in range A5:D12:

Sub TestMe()

    Dim ws As Worksheet
    Dim selAddress As String
    selAddress = "A5:D12"

    For Each ws In Worksheets
        'ws.Range(selAddress).Value2 = Worksheets(1).Range(selAddress).Value2
        ws.Range(selAddress).Formula = Worksheets(1).Range(selAddress).Formula
    Next ws

 End Sub

See How to avoid using Select in Excel VBA.