(Solved) – How to apply this VBA code to two specified sheets?

  • by

I am new to VBA, I need help applying the below VBA code to two specific work sheets by Region and by Model. The code simply finds the last column which has the name total for the year and copies the previous months values into a new column. The code works fine if executed on each worksheet separately.

Sub Insert_New_Col()
Dim R As Range, BeforeR As Long

'Find 'Totals' in row 5 of active sheet
Set R = Rows(3).Find(what:="Total for the Year", lookat:=xlWhole)

'identify the column to copy (last month)
BeforeR = R.Column - 1

    If R Is Nothing Then
      MsgBox ("The word 'Totals' was not found in Row 5 - macro terminated!")
        Exit Sub
    ElseIf Not R Is Nothing Then

    'copy last month's column

    'insert copied cells before the Totals column
    Columns(R.Column).Insert Shift:=xlRight

    Application.CutCopyMode = False
    End If

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *