Subscription out of range when trying to copy worksheet data into existing workbook

Question

I am quite new to Excel VBA and trying to copy the data from a worksheet into an existing workbook, but not being able to. In summary, I have a workbook running a macro, within this same workbook there is the source worksheet, from where I need to retrieve the record (A2:H2), then I check if the destination workbook exists, if not create it, otherwise it should copy/insert the records into the existing workbook.

The VBA macro code is as follows

Sub process()

    Dim fName As String
    Dim fExists As String
    
    Dim wb As Excel.Workbook
    
    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet
    'Dim lCopyLastRows As Long
    Dim lDestLastRow As Long
    
    
    fName = "C:\TACs\ResumoTACs_" & Format(Date, "MM-YYYY") & ".xlsx"
    fExists = Dir(fName)
    
    If fExists = "" Then
        '\\ Create a new workbook
        Set wb = Workbooks.Add
        
        '\\ Copy sheet to the new workbook
        ThisWorkbook.Sheets("TAC Data").Copy Before:=wb.Sheets(1)
        
        '\\ Delete unused sheet
        Application.DisplayAlerts = False
        wb.Sheets(2).Delete
        Application.DisplayAlerts = True
        
        '\\ Save new workbook
        wb.SaveAs fileName:=fName, FileFormat:=xlOpenXMLStrictWorkbook
        ActiveWorkbook.Save
        ActiveWorkbook.Close

        MsgBox "New file " & fName & " created!"
    Else
        '\\ Set variables for copy and destinnation sheets
        Set wsCopy = ThisWorkbook.Worksheets("TAC Data")
        Set wsDest = Workbooks(fName) 'Worksheets("TAC Data")
        
        '\\ Find first blank row in the destination range based on data in column B
        lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row
        
        '\\ Copy & Paste Data
        wsCopy.Range("A2:H2").Copy _
            wsDest.Range("A" & lDestLastRow)

        MsgBox "The file exists"
    End If

    On Error Resume Next
    Application.Dialogs(xlDialogPrint).Show
    
End Sub

When I try to run the VBA macro I get the following error when executing the code Set wsDest = Workbooks(fName) 'Worksheets("TAC Data"): Run-time error '9': Subscription out of range

enter image description here

Any idea about the issue, or how I could effectively copy the worksheet data into an existing workbook?

Subscript out of range indicates that the sheet wasn't found, but you're trying to use it anyway. If you separate out the ThisWorbook.Sheets("TAC Data") into a separate WorkSheet variable, you'll find it wasn't found, and therefore you can't call its .Copy method. — Ken White
Thanks for the help. Any suggestion on how to append data from a worksheet into another workbook? — Edrei Lima

chris neilsen chris neilsen · Accepted Answer · 2020-10-29T04:13:56

Sevreral issues here:

The Destination Workbook may or may not be open. If it's not, open it
The Destination Workbook may or may not have a sheet called "TAC data".
wsDest is a Worksheet, not a Workbook, so you need specify a Worksheet (as in your comment)
When referencing an open Workbook, only specify the book name, without the path

Dim fPath as String
'...
fPath = "C:\TACs\"
fName = "ResumoTACs_" & Format(Date, "MM-YYYY") & ".xlsx"
fExists = Dir(fPath & fName)
'...
If fExists = "" Then
    '...
Else
    On Error Resume Next
    Set wbDest = Workbooks(fName) ' now that fName contains only the file name
    On Error GoTo 0
    If wbDest Is Nothing Then
        'Open it
        Set wbDest = Workbooks.Open(fPath & fName)
    End If
End If
'...
On Error Resume Nest
Set wsDest = wbDest.Worksheets("TAC data")
On Error GoTo 0
If wsDest Is Nothing Then
    ' Sheet missing.  What now?
Else
    '...

Subscription out of range when trying to copy worksheet data into existing workbook

1 Answers