Copy range between workbooks using PERSONAL.XLSB

perki307

New Member
Joined
Dec 30, 2016
Messages
3
I am trying to use personal.xlsb macros to copy ranges between two workbook.
The source workbook "Master Costing 2016 ver (f).xlsx" is static and the name will not change
The destination workbook's name will change based on the project.
The Macros are located in personal.xlsb

When I run the macro from the destination workbook it writes to the personal.xlsb sheet1 and not the destination workbook. If I hardcode the destination workbook name it works just fine.

Any help is greatly appreciated.

Below is the code:

Sub ReImaging_License()
'
' ReImaging_License Macro
'
'
Sheets("Parts List").Select
Windows("Master Costing 2016 ver(f).xlsx").Activate
Sheets("Virtual Software").Select
Range("A7:K8").Select
Selection.Copy
ThisWorkbook.Activate
' Windows("Test Target.xlsm").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Sheets("Select_Items").Select
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You don't need to select and activate. If you run the macro with the destination workbook active and the Master workbook open, see if this works (untested):
Code:
Sub ReImaging_License()
'Assumes Destination workbook is the active workbook when the macro is run
Dim Wb As Workbook
Set Wb = ActiveWorkbook
With Wb
    Workbooks("Master Costing 2016 ver(f).xlsx").Sheets("Virtual Software").Range("A7:K8").Copy _
        Destination:=.Sheets("Parts List").Range("A" & _
        .Rows.Count).End(xlUp).Offset(1)
    .Sheets("Select_Items").Select
End With
End Sub
 
Last edited:
Upvote 0
Just change "thisworkbook.activate" to "activeworkbook.select" will be suffice...

Also try the code given below

Thanks
Ashik
 
Upvote 0
I get this same error when trying both the suggested code and by using activeworkbook.select
Run-time error '438'
Object doesn't support this property or method.

But thanks for the replies.
 
Upvote 0
I get this same error when trying both the suggested code and by using activeworkbook.select
Run-time error '438'
Object doesn't support this property or method.

But thanks for the replies.
On what line do you get that error?
 
Upvote 0
On what line do you get that error?
I had a "." typo. Try this:
Code:
Sub ReImaging_License()
'Assumes Destination workbook is the active workbook when the macro is run
Dim Wb As Workbook
Set Wb = ActiveWorkbook
With Wb
    Workbooks("Master Costing 2016 ver(f).xlsx").Sheets("Virtual Software").Range("A7:K8").Copy _
        Destination:=.Sheets("Parts List").Range("A" & _
        Rows.Count).End(xlUp).Offset(1)
    .Sheets("Select_Items").Select
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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