Copy Range from workbook with variable file name

[johnny_p]

Hi all

Could someone please help?

I'm looking to copy ranges from one workbook to another, however I don’t know how to reference the workbook with a variable file name and pass it to the other workbook.

The code will start in workbook 1 which has the variable file name. It then calls a subroutine in workbook 2 with a static name.

The code in workbook 2 then copies ranges from workbook 1. It has to work this way.

Any help is greatly appreciated

Thanks

[Kenneth Hobson]

Use Application.run.

I passed the workbook object as the input parameter. If you want to pass the workbook name pass Thisworkbook.Name or Thisworkbook.Fullname.

In this example, it is assumed that the slave and master XLSM files are in the same folder and that both are open. Data is copied from sheet2 starting at A2 to the last cell with data in column A. It is pasted into sheet2 of the master file in the next empty cell after the last data cell in column A.

In a Module in the slave file:

Please Login or Register  to view this content.

You can also use the code above in your Personal workbook if you don't want to add it to all of your slave workbooks. Pass Activeworkbook as the input parameter in that case where the slave workbook is open and active as-well-as the master workbook.

In a Module named mSlaveMaster in the master file:

Please Login or Register  to view this content.

[johnny_p]

I have used this to identify the file name but not sure how to use this in workbook 2

Please Login or Register  to view this content.

[johnny_p]

We seemed to reply at the same time.

I can see how your code works, however I will be working with variable "slave" files to read from.

Use Application.run.

I passed the workbook object as the input parameter. If you want to pass the workbook name pass Thisworkbook.Name or Thisworkbook.Fullname.

In this example, it is assumed that the slave and master XLSM files are in the same folder and that both are open. Data is copied from sheet2 starting at A2 to the last cell with data in column A. It is pasted into sheet2 of the master file in the next empty cell after the last data cell in column A.

In a Module in the slave file:

Please Login or Register  to view this content.

You can also use the code above in your Personal workbook if you don't want to add it to all of your slave workbooks. Pass Activeworkbook as the input parameter in that case where the slave workbook is open and active as-well-as the master workbook.

In a Module named mSlaveMaster in the master file:

Please Login or Register  to view this content.

[Kenneth Hobson]

You need to choose to use the string or the object to pass. You dimmed your variable as the workbook object but then assigned it the value as a string.

So, if you are passing the workbook name, use ActiveWorkbook.Name to pass. IF you are passing the workbook object, then pass ActiveWorkbook.

You could pass the function name if you fix the issue I first explained here.

If your master workbook is always going to use the activeworkbook, you can just use that directly without passing it as a parameter with a value.

Without see what you are doing with other code, I don't know what other advice to give.