1

Here is my code.

Sub AddNewMonthToJPR()

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate

    If ws.Name <> "XSummary" And ws.Name <> "Summary" And ws.Name <> "Legend" And ws.Name <> "DATA" And ws.Name <> "XMastersheet" And ws.Name <> "MasterSheet" Then

        Range("C2").EntireColumn.Insert
        Range("C2").EntireColumn.Insert
        Range("C2").EntireColumn.Insert
        Range("C2").EntireColumn.Insert
        Range("C2").EntireColumn.Insert

        Columns("C:G").Select
        Selection.Clear

        Range("C1").Select
        Sheets("MasterSheet").Select
        Columns("C:G").Select
        Range("C2").Activate
        Selection.Copy
        Range("CT1").Select

        Range("CT1").Select
        Call sourceSheet.Activate
        Range("C1").Select
        ActiveSheet.Paste 'NEED TO UPDATE SO IT REFERENCES APPROPRIATE SHEET EACH TIME
        Range("E4").Select
    
    End If
    
Next ws
    
End Sub

ActiveSheet.Paste brings the process back to the start sheet, but instead, it needs to switch to the next sheet in the sequence.

The paste of data is happening in the same worksheet repeatedly instead of moving to the next sheet.

Community
  • 1
  • 1
sugarmellons
  • 11
  • 2
  • 2
    The way to make this work is to use the reference you have, `ws`, to avoid using `.Activate`, and to fully qualify any `Range` calls: so `Range("C2")` ---> `ws.Range("C2")` and so on. – BigBen Aug 31 '22 at 19:32
  • 3
    Pretty much mandatory reading for anyone new to VBA in Excel: [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Aug 31 '22 at 19:34

0 Answers0