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

[GeoBa]

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:

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

 

[NdNoviceHlp]

This seems to work. HTH. Dave

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]

This seems to work. HTH. Dave

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]

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.