Refer back to correct worksheet mid loop

Viewed 29

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

The issue is that ActiveSheet.Paste brings the process back to the start sheet, but instead, it needs to switch to the next sheet in the sequence. The error is occurring in that the paste of data is happening in the same worksheet repeatedly instead of moving to the next sheet. I am not sure how to change this line of code to make it work.

Please let me know if I can help be more clear, I am new to VBA. Thanks!

0 Answers
Related