2

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
Community
  • 1
  • 1
Javier_Caceres
  • 48
  • 7
  • The last sheet is not included in the array probably. You might want to search into looking a For Each loop. – Luuklag May 28 '18 at 16:00

1 Answers1

1

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    I had just copied the same stuff for ws in worksheets... But nice example of improving the code... +1 – Solar Mike May 28 '18 at 16:15
  • Almost. It copied the values that I have in the range in my Sheet1. The thing is that in that range there were some cells with formulas so the formulas in the cells were not copied. So the VBA should consider copying the formulas , sorry for not alerting the need to copy formulas too in my request for help – Javier_Caceres May 28 '18 at 16:44
  • @Javier_Caceres - you can change `.Value2` to `.Formula` and give it a try. – Vityata May 28 '18 at 16:46
  • 1
    Works Great Thank both Vityata and Solar Mike – Javier_Caceres May 28 '18 at 17:02