VBA to send a worksheet to another workbook?

[RedditGold]

Hi Excel Pros,

I have some code that I'm using to save a particular workbook's worksheet to a new workbook. It works great but what I'm wanting to know is if it's possible to save an individual sheet from multiple workbooks to a new workbook and have it retain the individual worksheets.

Please Login or Register  to view this content.

Basically, I need to modify this code to run across an individual worksheet in several workbooks and have these individual worksheets each saved to a new sheet in the new designated workbook. Is such a thing even possible?

Thanks!

[kev_]

Yes- its possible
Need a way to get a handle on the workbooks and individual worksheets
(The easiest situation would be that the same sheets are being copied from each workbook and that those workbooks are all in the same folder, which contains nothing else)

Are all the workbooks in the same folder? Are they the only ones there?
If not obvious how do we identify to VBA which workbooks to open?

Is it one sheet to be copied from each workbook?
Is it always the first sheet?
Do the sheets all have the same name?
If not obvious how do we identify to VBA which worksheet(s) to copy in each workbook?

[RedditGold]

Hi kev,

Yes, all the workbooks are in the same folder and they are the only workbooks present in the folder.
Yes, the sheets in these four workbooks are all named the same. For security purposes, they're named "New York City".
Only one sheet is to be copied. The "New York City" sheet from each of the four workbooks. There are eight worksheets total in each of these four workbooks. The "New York City" sheet is the 7th sheet.

[sintek]

Hi RedditGold

Can you supply us:
1. The names of the 4 workbooks and the folder they are in.
2. The name of the workbook that the 4 New York sheets must be copied into.
3. Must we rename the Sheets to New York1, New York2 etc

[RedditGold]

Hi,

The name of the folder is 'Total Cost 2017' and the name of the four workbooks are

47_New_York_State_1
47_New_York_State_2
47_New_York_State_3
47_New_York_State_4

The name of the new workbook can simply be 'All New York'

The sheets can be renamed anything. That naming convention works fine: New York1, New York2, etc.

Thanks!

[kev_]

Try this

AMEND:
fileFolder = where the 4 files are
myPath = where the new file is to be saved to
myFile = new file name
.Name = "NYC" & x ( each sheet needs a different name)

Please Login or Register  to view this content.

EDIT:
I added a variable late and forgot to amend the code fully

This line:

Please Login or Register  to view this content.

should now be:

Please Login or Register  to view this content.

[kev_]

@sintek - file names not required - it is every file in one folder

[sintek]

@ kev_

Thanks once again for the lessons....Need to go through this one in more detail...A lot I do not understand.

[kev_]

@sintek DIR function is very useful - look here for several examples

[RedditGold]

Thanks for the help guys. I'll see if I can get this working!