trouble running macro from personal WB

Snabelhund

New Member
Joined
Nov 11, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi, im stuck again.. I have a piece of code that when it is inserted in a standard module in the active workbook is running fine.

However i have a need to use it on several computers and also in different workbooks and because of this i´ve saved it in my "personal" workbook.
However when it is run from the macroworkbook it fails in some parts, as it is performing some of the copy and paste parts in the macroworkbook rather than on the preferred sheet.

As far i can tell it is because im referencing ThisWorkbook as wb1. rather than to the workbook where the operations should be performed (myWorkBook)

an easy solution would probably be to just reference the workbbook name but it will be changing depending on who is executing the macro.

Is there anyway to reference a workbook with a changing or unknown name?
VBA Code:
Sub CopyFromWorkbook()
' Define variables'
Dim wb1 As Workbook, wb2 As Workbook
 
' Disable screen updating to reduce screen flicker'
Application.ScreenUpdating = False
 
' Define which workbook is which, i guess that the problem i here_
'sine wb1 will have a name that might change'
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("H:\TB.xlsx")
 
' Copy range A1:D1000 from the Data sheet of wb2
wb2.Sheets("sourceData").Range("A1:D7000").Copy
 
'to avoid clipboard prompt'
 
Application.DisplayAlerts = False
 
' Paste the copied data to the column C of the target sheet in wb1'
 
  'Basically here it pastes the copied range in my personal workbook rather than in wb1 i´ve tried running it without
 ' line below but it doesen´t seem to help'

wb1.Activate

 

   Range("C1").Select
    ActiveSheet.Paste
' Close wb2
    wb2.Close
Application.DisplayAlerts = True
 
' Re-enable screen updating
Application.ScreenUpdating = True
End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
If you change ThisWorkbook to ActiveWorkbook it will paste to whichever workbook was active when the code is run.
 
Upvote 0
Solution
Basically, when you have a file of unknown name, and you want to "capture" it in a workbook object variable, the way that I often use is that when that workbook is the active workbook (i.e. right after you open it), have a command like this:
VBA Code:
Set wb1 = ActiveWorkbook

EDIT: Looks like fluff and I posted the same thing at about the same time!
 
Upvote 0
Cheers! thanks to the both of you for the explanations. Everything is running smoothly now!

On another note, how did you guys learn VBA? im mostly recyling code and learning bits by trial and error. Is there any online resources or similar that you would recommend for a beginner?
 
Upvote 0
I learned using a combination of learning from books, and asking questions and reading posts on forums like this.
The MrExcel bookstore does have some books on VBA, such as this: Microsoft Excel 2019 VBA and Macros
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top