Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

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.
0
Ed_CLP
Asked:
Ed_CLP
  • 5
  • 3
  • 2
2 Solutions
 
Patrick MatthewsCommented:
Something like this should work:


Sub MakeItHappen()

    Dim SourceWb As Workbook, DestWb As Workbook
    Dim Counter As Long

    Set SourceWb = Workbooks("Source.xls")
    Set DestWb = Workbooks("Destination.xls")

    For Counter = 1 To 25
        SourceWb.Worksheets(Counter).[a1:e10].Copy DestWb.Worksheets(Counter).[a1]
    Next

    MsgBox "Done"

End Sub
0
 
Ed_CLPAuthor Commented:
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?  

0
 
Patrick MatthewsCommented:
You need to update the names of the 'source' and 'destination' workbooks :)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SiddharthRoutCommented:
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
0
 
Ed_CLPAuthor Commented:
@matthewspatrick
I actually named my files source and destination;
Set SourceWb = Workbooks("source.xlsm")
    Set DestWb = Workbooks("destination.xlsm")
0
 
SiddharthRoutCommented:
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
0
 
Ed_CLPAuthor Commented:
@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.
0
 
SiddharthRoutCommented:
If the workbook is closed then you will have to refer to it as

Set DestWb = Workbooks.Open("C:\destination.xls")

Regarding "fixed number" do you want the first 25 sheet or 25 sheets that you want to select?

Sid
0
 
Ed_CLPAuthor Commented:
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!
0
 
Ed_CLPAuthor Commented:
Great answers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now