×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBS script to copy excel sheets into new excel file

VBS script to copy excel sheets into new excel file

VBS script to copy excel sheets into new excel file

(OP)
I am trying this code .vbs but am getting an error of "Copy Method of Class Worksheet failed". Existing File1 has two sheets called "Test1" and "Test 2" while File2 has two sheets called "Test3" and "Test4". However generically the number of sheets may be different with different names and it more the process of copying all over regardless of name.

Any help on what could be tweaked would be appreciated.



Dim eapp
Dim wkbk_from
Dim wkbk_to
Dim wksh
Dim J

Set eapp = CreateObject("Excel.Application")
Set wkbk_from = eapp.Workbooks.Open("D:\Excel_VB_Test\File2.xlsx")
Set wkbk_to = eapp.Workbooks.Open("D:\Excel_VB_Test\File1.xlsx")
eapp.Visible = true

For j = 1 to wkbk_from.Worksheets.count
wkbk_from.worksheets(j).Copy wkbk_to
Next

wkbk_to.SaveAs "NewFile.xlsx"

RE: VBS script to copy excel sheets into new excel file

In excel VBA the Copy method syntax is WorksheetFrom.Copy (Before, After), where Before and After are optional, if specified, they are a sheet the copy is related to, and cannot be defined both.

So, first, you have to refer to sheet, secondly, properly handle arguments:
wkbk_from.worksheets(j).Copy(wkbk_to.Worksheets(1), Null)

combo

RE: VBS script to copy excel sheets into new excel file

(OP)
@combo

Thanks for your help. Note that the Before and After syntax will not work in a .vbs script execution as I would be calling it from a command line but that order of the sheets doesn't matter too much. I was able to use your suggestion on the wkbkt_to.worksheets(1) to have it work. Thanks so much for your assistance.

RE: VBS script to copy excel sheets into new excel file

>the Before and After syntax will not work in a .vbs script

??? How do you mean?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close