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

DipDip

Board Regular
Joined
Jan 23, 2015
Messages
71
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
'--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):

VBA 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("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 :)
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

CephasOz

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
To open the workbook use:
VBA Code:
Set wkbDestination = Workbooks.Open("K:\HR\COVID 19\Covid 19 test tracker 230720 onwards.xlsx")

If the workbook is already open, you can use:
VBA Code:
Set wkbDestination = Workbooks("Covid 19 test tracker 230720 onwards.xlsx")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,139
Members
414,292
Latest member
kingshuk963

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