2 worksheet variables in VB

santa12345

Board Regular
Joined
Dec 2, 2020
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Hello.

I have a macro which is working great thus far.
My last step is...copying sheet1 contents from one workbook to another workbook. Both workbooks are open and are named properly based on a script.
Variables are being used to name the xls files since the VB loops and the filenames are changing based on month, customer, fields within the file...etc..
when you hover over the variables (str123filename & str123filename1) within the vb, you will see the actual filename, etc..

So my question is, how do I activate one workbook using its variable name? And then copy sheet 1 to the other workbook.
I've tried things like.... str123filename.("sheets").sheet1.activate..etc..
I can't seem to get past this last step.
Any input would be greatly appreciated.
Thanks.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
See if this helps. It is bad practice to use activate, it just slows the macro down.

VBA Code:
Sub testOtherWB()

    Dim thisWB As Workbook
    Dim otherWB As Workbook
  
    Set thisWB = ThisWorkbook                               ' Workbook containing the code
    Set otherWB = Workbooks("Name of other workbook.xlsx")
  
    thisWB.Worksheets("Sheet1").Range("A1:B3").Copy otherWB.Worksheets("Sheet1").Range("D1")

  
End Sub

You might find this helpful.
It is 13 mins Paul Kelly Excel Macro Mastery

 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,479
Messages
5,831,915
Members
430,091
Latest member
Generally_confused

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