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