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.
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