Avoiding #REF issues when (VBA) Copy sheets from book1 to book2 and replace if the sheet exists in book2

GeoBa

New Member
Joined
Dec 31, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi there,

After using successfully a VBA code founded here from (thanks to "Jerry") regarding: "Copy sheets from book1 to book2 and replace if the sheet exists in book2" i want to solve a new problem..
How can i avoid the #REF issue???
Appears during the deletion/replacement of the copyed worksheet...
i have a workbook with many worksheets. At the worksheets that a have formulas related to cells of replaced sheets a receive that error: #REF
Any idea how to solve it?

The code i use is the below one:
Rich (BB code):
Sub CopyReplaceWorksheet()
'--copies activesheet into specified destination workbook
'  if sheet with same name exists in destination workbook, then
'    deletes existing sheet and locates copy in same order

Dim lSheetIndex As Long
Dim sErrMsg As String
Dim wkbDestination As Workbook
Dim wksSource As Worksheet, wksTemp As Worksheet

On Error GoTo ErrProc
Application.EnableCancelKey = xlErrorHandler
Application.EnableEvents = False

'--modify to actual workbook name
Set wkbDestination = Workbooks("Model_2021.xlsm")

Set wksSource = ActiveSheet
'--validate destination workbook is not activeworkbook
If ActiveWorkbook.Name = wkbDestination.Name Then
   MsgBox "This macro won't copy Active Sheet in destination workbook."
   GoTo ExitProc
End If

lSheetIndex = lGetSheetIndex(sSheetName:=wksSource.Name, wkb:=wkbDestination)

If lSheetIndex Then
   If lSheetIndex = wkbDestination.Sheets.Count Then
      '--if existing sheet is last in workbook, add temp sheet.
      '  this handles problem of trying deleting only sheet and
      '  simplifies ordering of copied sheet.
      Set wksTemp = wkbDestination.Worksheets.Add( _
         After:=wkbDestination.Sheets(lSheetIndex))
   End If
 
   '--delete existing worksheet with same name
   Application.DisplayAlerts = False
   wkbDestination.Sheets(wksSource.Name).Delete
   Application.DisplayAlerts = True
Else
   '--if no existing sheet, order copy as first sheet
   lSheetIndex = 1
End If
 
wksSource.Copy Before:=wkbDestination.Sheets(lSheetIndex)

ExitProc:
On Error Resume Next
'--delete temp worksheet if exists
If Not wksTemp Is Nothing Then
   Application.DisplayAlerts = False
   wkbDestination.Sheets(wksTemp.Name).Delete
   Application.DisplayAlerts = True
End If

Application.EnableEvents = True
If Len(sErrMsg) Then MsgBox sErrMsg
Exit Sub

ErrProc:
sErrMsg = Err.Number & ": " & Err.Description
Resume ExitProc]

End Sub

Function lGetSheetIndex(sSheetName As String, wkb As Workbook) As Long
'--returns sheet index within workbook if found, else returns 0
On Error Resume Next
lGetSheetIndex = wkb.Sheets(sSheetName).Index
End Function
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,767
This seems to work. HTH. Dave
Code:
Sub CopyReplaceWorksheet()
'--copies activesheet into specified destination workbook
'  if sheet with same name exists in destination workbook, then
'    deletes existing sheet and locates copy in same order

Dim lSheetIndex As Long
Dim sErrMsg As String
Dim wkbDestination As Workbook
Dim wksSource As Worksheet, wksTemp As Worksheet

On Error Resume Next
'--modify to actual workbook name
Set wkbDestination = Workbooks("Model_2021.xlsm")
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "The workbook is not open!"
Exit Sub
End If

On Error GoTo ErFix
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
'--validate destination workbook is not activeworkbook
If ActiveWorkbook.Name = wkbDestination.Name Then
   MsgBox "This macro won't copy Active Sheet in destination workbook."
   GoTo ErFix
End If

Set wksSource = ActiveSheet
lSheetIndex = lGetSheetIndex(sSheetName:=wksSource.Name, wkb:=wkbDestination)
With wkbDestination.Sheets(wksSource.Name)
.Name = "TEMP"
End With
ActiveSheet.Copy wkbDestination.Sheets(lSheetIndex)
'remove temp sheet
wkbDestination.Worksheets("TEMP").Delete
wksSource.Activate
ErFix:
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Error"
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

Function lGetSheetIndex(sSheetName As String, wkb As Workbook) As Long
'--returns sheet index within workbook if found, else returns 0
On Error Resume Next
lGetSheetIndex = wkb.Sheets(sSheetName).Index
End Function
 

GeoBa

New Member
Joined
Dec 31, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
This seems to work. HTH. Dave
Code:
Sub CopyReplaceWorksheet()
'--copies activesheet into specified destination workbook
'  if sheet with same name exists in destination workbook, then
'    deletes existing sheet and locates copy in same order

Dim lSheetIndex As Long
Dim sErrMsg As String
Dim wkbDestination As Workbook
Dim wksSource As Worksheet, wksTemp As Worksheet

On Error Resume Next
'--modify to actual workbook name
Set wkbDestination = Workbooks("Model_2021.xlsm")
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "The workbook is not open!"
Exit Sub
End If

On Error GoTo ErFix
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
'--validate destination workbook is not activeworkbook
If ActiveWorkbook.Name = wkbDestination.Name Then
   MsgBox "This macro won't copy Active Sheet in destination workbook."
   GoTo ErFix
End If

Set wksSource = ActiveSheet
lSheetIndex = lGetSheetIndex(sSheetName:=wksSource.Name, wkb:=wkbDestination)
With wkbDestination.Sheets(wksSource.Name)
.Name = "TEMP"
End With
ActiveSheet.Copy wkbDestination.Sheets(lSheetIndex)
'remove temp sheet
wkbDestination.Worksheets("TEMP").Delete
wksSource.Activate
ErFix:
If Err.Number <> 0 Then
On Error GoTo 0
MsgBox "Error"
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

Function lGetSheetIndex(sSheetName As String, wkb As Workbook) As Long
'--returns sheet index within workbook if found, else returns 0
On Error Resume Next
lGetSheetIndex = wkb.Sheets(sSheetName).Index
End Function
Unfirtunately didn't work... :(
thanks for your effort
 

GeoBa

New Member
Joined
Dec 31, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Dave,
i have try your code but i contunue to receive #REF issues at the other worksheets due to replacement/copy of a specific worksheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,376
Messages
5,624,334
Members
416,021
Latest member
simbonile

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
Top