Copy from 1 Workbook to Another

aks005

New Member
Joined
Jun 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
    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.
 
Last edited by a moderator:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

aks005

New Member
Joined
Jun 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Eep, this formatting looks horrible! Sorry, I will try to figure out how to edit that
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,723
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Is the code located in the workbook you are copying to?
 

aks005

New Member
Joined
Jun 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,723
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

aks005

New Member
Joined
Jun 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
The name changes each month but the naming structure is: CBS Data Fixed + Month/Year
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,723
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case try
VBA Code:
Set wbThisWB = ActiveWorkbook
just make sure that the correct workbook is active when you run the code.
 
Solution

aks005

New Member
Joined
Jun 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
In that case try
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,723
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,842
Messages
5,721,119
Members
422,340
Latest member
canadianbacon357

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