Trying to copy existing data from WB1 to WB2 and overwrite values

[DipDip]

Hiya,
I am hoping someone can help me with this issue. I have a workbook I deal with daily and I want to create a copy of it, but data only. No formulas or macros. It only needs to update the copy on the current sheet.

This is what I have so far, but it's goes wrong. Comes up with an error saying Run-time error '9': Subscript out of range

This is the code it comes back with highlighted.

'--modify to actual workbook name
Set wkbDestination = Workbooks("K:\HR\COVID 19\Covid 19 test tracker 230720 onwards.xlsm")

The code I am using is this (which I found on an old post):

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("K:\HR\COVID 19\Covid 19 test tracker 230720 onwards.xlsx")

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

If anyone could help that would be great. TIA

 

[CephasOz]

To open the workbook use:

Set wkbDestination = Workbooks.Open("K:\HR\COVID 19\Covid 19 test tracker 230720 onwards.xlsx")

If the workbook is already open, you can use:

Set wkbDestination = Workbooks("Covid 19 test tracker 230720 onwards.xlsx")