Copy the active sheet and the sheet with the next sequentially numbered code name

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

See if this helps. After the end of the For ... Next loop, the nextSheet variable refers to the sheet with the next sequential code name number after the active sheet.

Public Sub Copy_Sheets()


    Dim codeNameNumber As Integer
    Dim ws As Worksheet, nextSheet As Worksheet
    
    'Extract number 'n' from active sheet's code name "Sheetn"
    
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\d+)"
        With .Execute(ActiveSheet.CodeName)(0)
            codeNameNumber = .Value
        End With
    End With
    
    'Find sheet with next sequential code name number
    
    Set nextSheet = Nothing
    For Each ws In ThisWorkbook.Worksheets
        If ws.CodeName = "Sheet" & codeNameNumber + 1 Then Set nextSheet = ws
    Next
        
    If Not nextSheet Is Nothing Then
    
        'Copy active sheet and sheet with next sequential code name number before the last sheet
        
        Worksheets(Array(ActiveSheet.Name, nextSheet.Name)).Copy Before:=Worksheets(Worksheets.Count)
        
    Else
    
        MsgBox "Active sheet is the last sequentially numbered code name"
        
    End If


End Sub

The code checks if nextSheet is Nothing (which it will be if the active sheet is the last sequentially numbered code name), and only copies the 2 sheets otherwise.

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

Hi,
I'm using the above code, however, it only works when I choose to make 1 copy of the two selected sheets. The goal is to allow the user to indicate they want to make 'x' number of copies of the 2 original worksheets. If the copy works, then each set of copies will be tied to each other vs. to the original Sheet 1.

a. If I only request 1 copy of the 2 worksheets, the copy function works fine. The order of the sheets stays the same (Copy of Sheet 1, then Copy of Sheet 2 - and Sheet 2 pulls data from Sheet 1).

b. If I request 2 copies, the order is as follows after the two original sheets: 1st Copy of Sheet 1, then 1st Copy of Sheet 2, then 2nd Copy of Sheet 2, then 2nd Copy of Sheet 1. Additionally, 1st Copy of Sheet 2 pulls data from 1st Copy of Sheet 1, 2nd Copy of Sheet 1 pulls data from Original Sheet 1.

c. If I request 3 copies, the order is as follows after the two original sheets: 1st Copy of Sheet 1, then 1st Copy of Sheet 2, then 2nd Copy of Sheet 2, then 2nd Copy of Sheet 1, then 3rd Copy of Sheet 2, then 4th Copy of Sheet 2. Additionally, 1st Copy of Sheet 2 pulls data from 1st Copy of Sheet 1, 2nd, 3rd, and 4th Copies of Sheet 1 pull data from Original Sheet 1.

d. and so on

Your guidance is greatly appreciated. Below is the above code combined with other code that gives the user a popup msg box asking how many copies they need.

[/COLOR]Sub SelectTwoTabs()'' SelectTwoTabs Macro'On Error GoTo Out'    Dim codeNameNumber As Integer    Dim ws As Worksheet, nextSheet As Worksheet          'Extract number 'n' from active sheet's code name "Sponsorn"         With CreateObject("VBScript.RegExp")        .Pattern = "(\d+)"        With .Execute(ActiveSheet.CodeName)(0)            codeNameNumber = .Value        End With    End With          'Find sheet with next sequential code name number         Set nextSheet = Nothing    For Each ws In ThisWorkbook.Worksheets        If ws.CodeName = "Sponsor" & codeNameNumber + 1 Then Set nextSheet = ws    Next         If Not nextSheet Is Nothing Then         ' Copy active sheet and sheet with next sequential code name number before the last sheet. The codenames were updated so that when the macro button is'  placed on the Sponsor CRE Portfolio tab the macro will select the Sponsor CRE Portfolio tab and Tools - RE Charts.  If the macro button on one of'  the copied sheets is clicked, then the macro will make a copy of the "copied" Sponsor CRE Portfolio tab and Tools - RE Charts.  The user can rename'  the sheets without interferring with the macro's operation.        Dim i As Integer    Dim Sure As Integer    Dim p As Integer        i = InputBox("How many copies do you want?", "Making Copies")    If i = 0 Then GoTo Out    Sure = MsgBox("Remember to revisit the instructions worksheet for Tips and Tricks on working with duplicate 'sets'." & vbCrLf & " " & vbCrLf & "Are you ready to continue?", vbYesNo)    If Sure = 7 Then GoTo Out            p = 0            Application.ScreenUpdating = False        Application.DisplayAlerts = False    Do                Worksheets(Array(ActiveSheet.Name, nextSheet.Name)).Copy After:=Worksheets(Worksheets.Count)            p = p + 1                Loop Until p = i        Application.ScreenUpdating = True                Exit SubOut:    MsgBox "Copy was cancelled."        Application.ScreenUpdating = True        Application.DisplayAlerts = True                     Else                 MsgBox "Active sheet is the last sequentially numbered code name"             End If    End Sub

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

I suspect the problem is due to the code using the built-in ActiveSheet object, because after a Worksheets.Copy the ActiveSheet object references the new sheet, whereas you want to reference the original active sheet.

To fix this, add the following code near the top of the routine:

Dim currentSheet As Worksheet
    Set currentSheet = ActiveSheet

and change all occurrences of 'ActiveSheet' in the code to 'currentSheet'.

PS please use CODE tags - see the forum rules.

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

.........'

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

Hi John,

Thank you so much for your quick reply...

1. In my "test" file the program with your changes works perfectly, running as a macro or stepping through it with the debug function.
2. In my actual file the program only works if I use the debugger to step through it. So, if I pick 2 or 10 copies while in debug mode, the sheets show up in the correct order and properly reference the sheet it was copied with.
3. In my actual file the program does the same thing it did before: make a copy of sheet 1 and a copy of sheet 2, then a 2nd copy of sheet 2 and a 2nd copy of sheet 1, then a 3rd and 4th copy (etc.) of sheet 2.

Do you think the macro is running too quickly that the program is getting confused as to where it is in the process? Here's what the code looks like now:

Sub SelectTwoTabs()
'
' SelectTwoTabs Macro
'
    On Error GoTo Out
'
        Dim codeNameNumber As Integer
        Dim ws As Worksheet, nextSheet As Worksheet
' added following two lines per John
        Dim currentSheet As Worksheet
        Set currentSheet = ActiveSheet
 
'   Extract Number 'n' from active sheet's code name "Sponsorn"
 
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\d+)"
' Changed ActiveSheet.CodeName to currentSheet.CodeName 
        With .Execute(currentSheet.CodeName)(0)
            codeNameNumber = .Value
        End With
    End With
 
'  Find sheet with next sequential code name number
 
    Set nextSheet = Nothing
    For Each ws In ThisWorkbook.Worksheets
        If ws.CodeName = "Sponsor" & codeNameNumber + 1 Then Set nextSheet = ws
    Next
 
    If Not nextSheet Is Nothing Then
     
'  Copy active sheet and sheet with next sequential code name number before the last sheet. The codenames were updated so that when the macro button is
'  placed on the Sponsor CRE Portfolio tab the macro will select the Sponsor CRE Portfolio tab and Tools - RE Charts.  If the macro button on one of
'  the copied sheets is clicked, then the macro will make a copy of the "copied" Sponsor CRE Portfolio tab and Tools - RE Charts.  The user can rename
'  the sheets without interferring with the macro's operation.
     
        Dim i As Integer
        Dim Sure As Integer
        Dim p As Integer
     
        i = InputBox("How many copies do you want?", "Making Copies")
        If i = 0 Then GoTo Out
        Sure = MsgBox("Remember to revisit the instructions worksheet for Tips and Tricks on working with duplicate 'sets'." & vbCrLf & " " & vbCrLf & "Are you ready to continue?", vbYesNo)
        If Sure = 7 Then GoTo Out
     
        p = 0
     
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    
    Do
         
' Changed ActiveSheet.Name to currentSheet.Name per John
        
        Worksheets(Array(currentSheet.Name, nextSheet.Name)).Copy After:=Worksheets(Worksheets.Count)
         
        p = p + 1
         
    Loop Until p = i
     
    Application.ScreenUpdating = True
     
    Exit Sub
    
Out:
        MsgBox "Copy was cancelled."
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
     
    Else
     
        MsgBox "Active sheet is the last sequentially numbered code name"
     
    End If
 
End Sub

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

There is nothing in this particular code which would make it work differently in the VBA debugger or as a macro from the UI or called from a command button.

Why are you using the On Error GoTo statement? What error(s) do you expect it to trap and where? Try it without the On Error and without the Application.DisplayAlerts = False line.

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

Instead of creating a new Worksheets object every time through the loop try setting it once and re-using it as the copy source

Dim WorksheetsToCopy As Sheets


Set WorksheetsToCopy = Worksheets(Array(currentSheet.Name, nextSheet.Name))
i = 1
Do 
    WorksheetsToCopy.Copy After:=Worksheets(Worksheets.Count)
    p = p+1
Loop until p = i

Re: Copy the active sheet and the sheet with the next sequentially numbered code name

Hi John,

First...I greatly appreciate your help. You identified the problem and fixed it in your original solution. When I originally set this macro up, I forgot that when I added a form and a button that I assigned the same set of code (first message of mine above) to the button. Your last comments reminded me of that. When I click my macro button it actually opens a form with a button on it. Once I dug into the button code, I realized that that is where my issue was coming from. I've fixed the code to match my second post above and it works perfectly.

Thank you again for solving this for me!