VBA to move worksheets to a new workbook if they don't exist, without moving all named ranges.

sjesiono

New Member
Joined
Jun 20, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that gets updated every year. The user is supposed to save the previous year's workbook to their desktop using a common target name, and then run a macro in the destination workbook. The macro then imports all important data from the old workbook to the new one. A recent addition has been to try and import any user-created tabs. Unfortunately, the workbook also has hundreds of named ranges, and simply moving sheets to the new workbook creates a bunch of duplicate names referring the original target. We've added code to delete the old named ranges before moving the sheets over, but this process takes an absurd amount of time. I've had it take anywhere from 10-90 minutes to complete this step.

I feel like I must be missing a smarter way to do this, but have been unable to figure it out.

VBA Code:
'   Delete Named Ranges from Target

    Dim ws As Worksheet
    Workbooks("Target.xlsm").Activate
    On Error Resume Next
    Dim nm As name
    For Each nm In Workbooks("Target.xlsm").names
    nm.Delete
    Next
    On Error GoTo 0
    
'   Copy over any tabs, except the Instruction tab, that aren't in the new workbook already
    Dim found
    Dim name
    For Each ws In Workbooks("Target.xlsm").Worksheets
    found = False
    
    name = ws.name
    If name <> "Instructions" Then
        For Each nws In Workbooks("Destination.xlsm").Worksheets
            If name = nws.name Then
                found = True
            End If
        Next nws
        
        If found = False Then
            ws.Copy After:=Workbooks("Destination.xlsm").Sheets(Workbooks("Destination.xlsm").Sheets.count)
        End If
    End If
    Next ws
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top