Link to home
Start Free TrialLog in
Avatar of Ed_CLP
Ed_CLP

asked on

VBA to copy multiple Excel 2007 worksheets from one workbook to another

I need to copy a fixed number (say 25) of worksheets from an Excel file to another Excel file.
The first XLS contains raw data and the second is a template that formats the data and creates charts.  The worksheets have different names in each XLS file.  I don't need to copy the entire worksheet, just a range such as A1:E10.
 I know I can hard code the worksheet names and do this, but I'm hoping there's a way to just loop through and get the first 25 worksheets from the first file and add the data to the first 25 worksheets in the second file.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ed_CLP
Ed_CLP

ASKER

Thanks for the fast reply.  I'm getting a Run-time error 9, Subscript out of range on
Set DestWb = Workbooks("destination.xls")
Any idea why?  

You need to update the names of the 'source' and 'destination' workbooks :)
Ed_CLP: Also one quick question. When you say fixed number of worksheets, what do you exactly mean? Because if you have 30 worksheets then the above code will copy only the first 25 and not the 25 sheets that you actually want to copy...

Sid
Avatar of Ed_CLP

ASKER

@matthewspatrick
I actually named my files source and destination;
Set SourceWb = Workbooks("source.xlsm")
    Set DestWb = Workbooks("destination.xlsm")
Ed_CLP: Seems like you ignored my question...

Anyways here is another question based on your above post.

Is your destination workbook closed or open?

Sid
Avatar of Ed_CLP

ASKER

@SiddharthRout - I tried it both with the destination.xlsm open and closed and get the same error.
On the number of worksheets, I want a fixed number not all of them.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ed_CLP

ASKER

Setting the file path and using the closed workbook is working.
I split the points because you were both very helpful in answering my question.
Thanks!
Avatar of Ed_CLP

ASKER

Great answers!