Copy from 1 Workbook to Another

[aks005]

I'm pretty new to vba and usually able to google my way through solutions. I've even done this process before successfully in other workbooks but I'm stuck

What I'm trying to do is:
From original WB (wbThisWB), ask user to open file (wbImportWB), copy data from A3:AB Last Row. Paste to the end of rows in the first workbook (wbThisWB)

    Dim wbThisWB          As Workbook
    Dim wbImportWB     As Workbook
    Dim strFullPath         As String
    Dim ImportLR           As Long
    Dim ThisLR               As Long
   
    Set wbThisWB = ThisWorkbook
   
    wbThisWB.Activate
    Sheets("Sheet1").Select
    ThisLR = Range("A" & Rows.Count).End(xlUp).Row
   
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Please select a file to open:"
        .Show
        On Error Resume Next
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                wbThisWB = Nothing
                Exit Sub
            End If
        On Error GoTo 0
    End With
   
    Application.ScreenUpdating = False
   
    Set wbImportWB = Workbooks.Open(strFullPath)
   
    wbImportWB.Activate
   
    ImportLR = Range("A" & Rows.Count).End(xlUp).Row
   
   ' On Error Resume Next
         wbImportWB.Sheets("CBS").Range("A3:AB" & ImportLR).Copy
[COLOR=rgb(184, 49, 47)]  [B]       wbThisWB.Sheets("CBS").Range("A" & ThisLR).PasteSpecial Paste:=xlPasteValues[/B][/COLOR]
           
    'On Error GoTo 0
   
    wbImportWB.Close False
   
    Set wbThisWB = Nothing
    Set wbImportWB = Nothing

If I remove error handling, on the red line above, I am getting the error:
Runtime Error: '9':
Subscript out of range

Note that the sheet name is the same on both workbooks. I've also tried changing Sheets("CBS") to Sheets("Sheet1") and in this case, it runs to the end but does not paste the data.

If anyone can can see where I've gone wrong, I'd appreciate some insight so much.

 

[aks005]

Eep, this formatting looks horrible! Sorry, I will try to figure out how to edit that

 

[Fluff]

Hi & welcome to MrExcel.
Is the code located in the workbook you are copying to?

 

[aks005]

Hi & welcome to MrExcel.
Is the code located in the workbook you are copying to?

Hi and thanks! I've been using the resource for a while, happy to participate .

The code is saved as an add-in. Oh! That's the difference from the other times I've tried this. Is there a way to define the current/original workbook so it can run as an add-in still, or would I be better creating a workbook to run it out of?

 

[Fluff]

What is the name of the workbook you need to copy to?

 

[aks005]

The name changes each month but the naming structure is: CBS Data Fixed + Month/Year

 

[Fluff]

In that case try

Set wbThisWB = ActiveWorkbook

just make sure that the correct workbook is active when you run the code.

 

[aks005]

In that case try

Set wbThisWB = ActiveWorkbook

just make sure that the correct workbook is active when you run the code.

It worked! I can't tell you how much I appreciate the help, I've been sitting on this since February. Thank you thank you thank you!

 

[Fluff]

You're welcome & thanks for the feedback.