Snabelhund
New Member
- Joined
- Nov 11, 2021
- Messages
- 20
- Office Version
- 2016
- Platform
- 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?
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