VBA Macro to loop through repetitive task

[Peter Davison]

I have a simple task of copying a row of formulas into a variable length of rows determined by "LastRow"
I have to do this on 20 different sheets (called Master1 to Master 20)
Would you be able to help me set up a loop to do this?

This is my copy and paste values formula I need to do on the 20 sheets

Range("B21").Select
Range("B21:L" & LastRow).Formula = Range("B21:L21").Formula
Range("B22:L" & LastRow).Value = Range("B22:L" & LastRow).Value

Thank you for your help

 

[RudRud]

Is it what you're looking for? CMIIW

Option Explicit
Sub Test()

Dim ws As Worksheet
Dim LRow As Long, LCol As Long
 
  For Each ws In ThisWorkbook.Worksheets
  
  If ws.Name Like "Master*" Then
  
  If ws.Cells(21, 2) <> "" Then
  'Last row based in Column B
    LRow = ws.Cells(Rows.Count, "b").End(xlUp).Row
    ws.Cells(21, 2).Resize(LRow - 20, 12).FillDown
  Else
    MsgBox ws.Name & "No Formula"
  End If
  
  End If

  Next ws

End Sub

 

[Peter Davison]

Thank you for your help.
I did keep trying and I came up with this.
What do you think?
It's picking up around 50 sheets, which do you think would be faster.
I ran the one below and it was 1 minute


Sub CopyFormulas()

Application.ScreenUpdating = False
Dim LastCol As Long, LastRow As Long

'Find Length of Column A
Worksheets("Rules and Results").Activate
Range("A21").Select
'Variables
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim ws_count As Integer
Dim i As Integer

ws_count = ActiveWorkbook.Worksheets.Count

For i = 1 To ws_count
If Left(ActiveWorkbook.Worksheets(i).Name, 6) = "Master" Then

ActiveWorkbook.Worksheets(i).Select
Range("B21").Select
Range("B21:L" & LastRow).Formula = Range("B21:L21").Formula
Range("B22:L" & LastRow).Value = Range("B22:L" & LastRow).Value
End If
Next i
End Sub

 

[RudRud]

You may put these two lines in the beginning and see if can shorten the time

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

And before end sub:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[Peter Davison]

Thank you that is very helpful.
Appreciate your time.

 

[RudRud]

Thank you that is very helpful.
Appreciate your time.

Glad to assist, If you have any further questions, please don't hesitate to ask/share with us