After copying in Macro - come back to previous sheet

There is no need to select the range and then copy the range. You can specify the range to be copied in one step. (the blue line below)

The following will copy the A1:Z100 range and then paste that information into sheets 2 and 3 in the same range of cells.

Sub MoveToMultipleSheets()

Worksheets("Sheet1").Range("A1:Z100").Copy
With Worksheets(1)
    .Paste Destination:=Worksheets("Sheet2").Range("A1")
    .Paste Destination:=Worksheets("Sheet3").Range("A1")
End With

Application.CutCopyMode = False

End Sub

Originally Posted by JK1234

This should work but my source page may change from sheet1 to another sheet and I was hoping to use this same macro on each sheet. So then sheet1 would be the active sheet. Then after it copied into the other two sheets then come back to this same active sheet.
So depending upon what sheet it is on would depend what is in the macro.

But I like the new expression, going to start using that instead.

Maybe something like this change to mdbct's code

Option Explicit

Sub MoveToMultipleSheets()
    Dim i      As Integer
    i = ActiveSheet.Index

    ActiveSheet.Range("A1:Z100").Copy
    With Worksheets(i)
        .Paste Destination:=Worksheets(i + 1).Range("A1")
        .Paste Destination:=Worksheets(i + 2).Range("A1")
    End With
    Application.CutCopyMode = False

End Sub

If I understand correctly from this:

This should work but my source page may change from sheet1 to another sheet and I was hoping to use this same macro on each sheet. So then sheet1 would be the active sheet. Then after it copied into the other two sheets then come back to this same active sheet. So depending upon what sheet it is on would depend what is in the macro.

... you're saying that you want the same code to run for different source sheets? You seem to be thinking that as you copy to other sheets those become active, but with the code mcbct provided, this is not the case and the source remains the active sheet. So - if this understanding is right, you want it to run where the source is the activesheet ... try this:

Activesheet.Range("A1:Z100").Copy
With Activesheet
    .Paste Destination:=Worksheets("Sheet2").Range("A1")
    .Paste Destination:=Worksheets("Sheet3").Range("A1")
End With

Or you can pass the name into the routine as a parameter based on however you determine the source; e.g. call like this:

Sub YourControlRoutine()
Dim wshSource As Worksheet
    ' your code to the point where you initiate the copy, then ...
    
    Set wshSource = ActiveWorkbook.Worksheets(1)    'or whatever ...
    Call MoveToMultipleSheets(wshSource)
    
    ' etc etc etc ...
End Sub

Sub MoveToMultipleSheets(sht As Worksheet)
    sht.Range("A1:Z100").Copy
    With sht
        .Paste Destination:=Worksheets("Sheet2").Range("A1")
        .Paste Destination:=Worksheets("Sheet3").Range("A1")
    End With
    Application.CutCopyMode = False
End Sub

Hope that helps.

Hi - I've changed the code below just to show you some options about this sort of thing; I do recommend though that you have a think about how your ranges are defined (static or dynamic) and you might be able to create a loop that does the same thing. I've made this as generic as I can to demonstrate and you can choose the most appropriate option for what you're doing ...

Sub YourControlRoutine()
Dim wshSource As Worksheet, wshTarget1 As Worksheet, wshTarget2 As Worksheet
Dim strSourceRange1 As String, strSourceRange2 As String
    'your code to where you initiate the copy/paste, then ...
    
    'if these are static then just set them:
    strSourceRange1 = "A1:A5"
    strSourceRange2 = "C1:C5"
    '(... but if they aren't static, and (say) you select the ranges
    'before copying, then you could use just one variable (setting
    'it as the range is selected or the address is otherwise determined,
    'such as:
    'strSourceRange = Selection.Address).
    
    Set wshTarget1 = ActiveWorkbook.Worksheets(2)
    Set wshTarget2 = ActiveWorkbook.Worksheets(3)
    
    Set wshSource = ActiveWorkbook.Worksheets(1)
    
    Call MoveToMultipleSheets(wshSource, wshTarget1, wshTarget2, strSourceRange1)
    Call MoveToMultipleSheets(wshSource, wshTarget1, wshTarget2, strSourceRange2)
    
    'depending on what you're doing, you could call the other sub 4 times and
    'just pass in a source & one target each time, which would look like:
    'Call MoveToMultipleSheets(wshSource, wshTarget1, strSourceRange1)
    'Call MoveToMultipleSheets(wshSource, wshTarget2, strSourceRange1)
    'Call MoveToMultipleSheets(wshSource, wshTarget1, strSourceRange2)
    'Call MoveToMultipleSheets(wshSource, wshTarget2, strSourceRange2)
    
    ' etc etc etc ...
    
End Sub

Sub MoveToMultipleSheets(shtS As Worksheet, shtT1 As Worksheet, shtT2 As Worksheet, strAddress As String)
    shtS.Range(strAddress).Copy
    With shtS
        .Paste Destination:=shtT1.Range(strAddress)
        .Paste Destination:=shtT2.Range(strAddress)
    End With
    Application.CutCopyMode = False
End Sub

Hope that helps ... MM.