After copying in Macro - come back to previous sheet

I want to copy info from one sheet to another, come back and copy info to another sheet. I want to do this from alot of sheets. My goal is to remember the first page as a variable so if I do this on other sheets it will remember the sheet to come back to without using the tab name.

example of what I want

HTML Code: 

then copy again to another sheet...but I do not want to use the name because if the macro is on a different sheet than I need that sheet name which would be way too much to do

HTML Code: 

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.

You can get the required raange by recording a macro

    Range("A1:Z100, C1:C5").Copy

I'm not sure why you are doing this, can you explain? There might be a simpler way to do it. You could use Named Ranges for example.

I would start by using Data validation instead of the comboboxes

Also get rid of the merged cells, they should be avoided because they will cause problems sooner or later.

Instead of multiple Ifs, use Select Case, it's easier to follow.

Here's how your case should look, but I don't understand what you mean by "for each selection" ... each selection of what?

    Select Case Range("N5")
        Case 1 To 5
            Set Ws = Worksheets("Sheet2")
        Case 2, 8, 14
            Set Rng2 = Range("C36:C41")
    End Select

    Select Case Range("Q5").Value
        Case 1
            Set Rng1 = Range("C28:C33")
    End Select

Not sure if this is what you want, sheet references might need changing, but I have attempted to change your code to use Select Case

Sub AutoShape1_Click()

    With Application
        .ScreenUpdating = False

        Dim Ws As Worksheet
        Dim Rng1 As Range
        Dim Rng2 As Range


        Select Case Range("N5").Value
            Case 1 To 5: Set Ws = Worksheet("Sheet2")
            Case 2, 8, 14: Set Rng2 = Range("C36:C41")
            Case Else: MsgBox "Incorrect entry"
        End Select

        Select Case Range("Q5").Value
            Case 1: Set Rng1 = Range("C28:C33")
            Case Else: MsgBox "Incorrect entry"
        End Select

        'The next part would do the copying and pasting depending upon what I selected

        Rng1.Copy Destination:=Rng2
        .ScreenUpdating = False
        .CutCopyMode = False
    End With
End Sub

I have simplified this, it shoulod give you an idea what you need to do.

I think you may have missed what Phil_V and I were saying ... It is possible that if your conditions are not met in the case statements, that neither Ws or Rng1 or Rng2 are ever set (so their value would be 'Nothing'). If this is right, then the attempt to copy/paste at the end (which is not conditional) is hit and your objects are not set ... Make sense? Think about running through this where N5 = 6 ...

... Actually ... that's not it ... I simplified the code to test as:

Sub AutoShape4_Click()

Dim Ws As Worksheet
Dim Rng1 As Range, Rng2 As Range

    Range("N5").Value = 1
    Range("Q5").Value = 1
    Set Ws = Worksheets("Sheet2")
    Set Rng1 = Range("C28:C33")
    Set Rng2 = Ws.Range("C28:C33")
    
    ActiveSheet.Rng1.Copy Destination:=Rng2

End Sub

But it doesn't like that either, which I didn't expect ... sorry