-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Macro to combine different workbooks to a single work book with multiple tabs (worksheets)
- Thread starter desire32
- Start date
hey there,
Most certainly possible, would you be able to provide some details.
1) are the workbooks all located in the same folder?
2) if not, how is the folder structure presented?
3) is there a naming convention used on the files (all start with "Day")
4) are they mixed in with other excel files? (you only want 1 of 5 excel files from each directory)
other options
5) do you want to be able to add to the master? once you have a master do you need to continue to add to it without duplicating? or you need to create separate masters? what of duplication?
6) all tabs from all these workbooks or only specific tabs? (please say all)
if you could please,
jc
Most certainly possible, would you be able to provide some details.
1) are the workbooks all located in the same folder?
2) if not, how is the folder structure presented?
3) is there a naming convention used on the files (all start with "Day")
4) are they mixed in with other excel files? (you only want 1 of 5 excel files from each directory)
other options
5) do you want to be able to add to the master? once you have a master do you need to continue to add to it without duplicating? or you need to create separate masters? what of duplication?
6) all tabs from all these workbooks or only specific tabs? (please say all)
if you could please,
jc
Upvote
0
Thanks:
1) The files are inthe same folder
2) there is no naming convention
Each workbook have say worksheets name "LARRYKING", ANDERSONCOOPER", CAMPBELBROWN" etc
I want to combine all "LARRYKING"s, ANDERSONCOOPER"s etc into a Master Workbook named as in the individual workbooks and continue adding from similar workbooks.
1) The files are inthe same folder
2) there is no naming convention
Each workbook have say worksheets name "LARRYKING", ANDERSONCOOPER", CAMPBELBROWN" etc
I want to combine all "LARRYKING"s, ANDERSONCOOPER"s etc into a Master Workbook named as in the individual workbooks and continue adding from similar workbooks.
Upvote
0
Hiya
This is what I have so far
Drop that into your master file and change the strPath to what you need it to be. A cell reference could be sheets("Ref").Cells(1,2) & "\" if need be to change the location of the files.
this will currently rip all pages out of everything in a directory into your current file.
don't make the accident of running this in a folder with ~70 ish files, you'll be there for a bit.
Someone may have another way of doing this, but it streamlines having to do dynamic arrays for what sheets are present in your workbook.
Worked for me, test it out and come back to me with your complaints.
Regards,
jc
This is what I have so far
Code:
Sub attemptedsimplification()
Dim strPath As String
Dim originalfile, currentfile As Workbook
Set originalfile = ActiveWorkbook
strPath = "c:\jc\temp\" 'where files are temporarily stored
ChDir strPath
'change extension
StrExtension = Dir(strPath & "*.xls")
Do While StrExtension <> ""
Workbooks.Open (strPath & StrExtension)
Set currentfile = ActiveWorkbook
For a = 1 To currentfile.Worksheets.Count
Sheets(a).Copy Before:=originalfile.Sheets(1)
currentfile.Activate
Next a
ActiveWorkbook.Close
StrExtension = Dir
Loop
End SubDrop that into your master file and change the strPath to what you need it to be. A cell reference could be sheets("Ref").Cells(1,2) & "\" if need be to change the location of the files.
this will currently rip all pages out of everything in a directory into your current file.
don't make the accident of running this in a folder with ~70 ish files, you'll be there for a bit.
Someone may have another way of doing this, but it streamlines having to do dynamic arrays for what sheets are present in your workbook.
Worked for me, test it out and come back to me with your complaints.
Regards,
jc
Upvote
0
jc,
This code works great. Just that all "LARRYKING"s from the 50 different workbooks (containing 15 tabs each) should all be merged together on just one tab (one worksheet) in the Master Workbook but rather it is copying them onto different tabs. So we are having ,say, 50 different tabs for "LARRYKING" named LARRYKING(1) .........LARRYKING(50).
So the Master Workbook should have just 15 in all.
This code works great. Just that all "LARRYKING"s from the 50 different workbooks (containing 15 tabs each) should all be merged together on just one tab (one worksheet) in the Master Workbook but rather it is copying them onto different tabs. So we are having ,say, 50 different tabs for "LARRYKING" named LARRYKING(1) .........LARRYKING(50).
So the Master Workbook should have just 15 in all.
Upvote
0
okay, that is a bit more difficult and will expand the macro a fair chunk
I need the following answered:
what are the names of the spreadsheets? do they change?
what is the top left cell of data to what bottom left cell of data is required to be copied? does this change? are there gaps? (ought to give me the range to be copied and methodology to do this.
regards,
jc
I need the following answered:
what are the names of the spreadsheets? do they change?
what is the top left cell of data to what bottom left cell of data is required to be copied? does this change? are there gaps? (ought to give me the range to be copied and methodology to do this.
regards,
jc
Upvote
0
I need the following answered:
1. what are the names of the spreadsheets? do they change?
2. what is the top left cell of data to what bottom left cell of data is required to be copied? does this change? are there gaps? (ought to give me the range to be copied and methodology to do this.
jc,
1. the file names are varying alphanumeric characters but the good news is all the files end with .quote.xlsx
2. most of the data on the worksheets have varying spaces before the data begins: some begins from cell A17, some from A26 etc. In this case I am thinking of manually formatting the data so all data will begin from A1 or writing a macro to do that.
Also the data size (range to be copied) is nonuniform (varies on different spreadsheets).
There are no gaps in the data.
1. what are the names of the spreadsheets? do they change?
2. what is the top left cell of data to what bottom left cell of data is required to be copied? does this change? are there gaps? (ought to give me the range to be copied and methodology to do this.
jc,
1. the file names are varying alphanumeric characters but the good news is all the files end with .quote.xlsx
2. most of the data on the worksheets have varying spaces before the data begins: some begins from cell A17, some from A26 etc. In this case I am thinking of manually formatting the data so all data will begin from A1 or writing a macro to do that.
Also the data size (range to be copied) is nonuniform (varies on different spreadsheets).
There are no gaps in the data.
Upvote
0
okay,
pardon about the first question, I should have specified that it is the names of the tabs.
What I am thinking is providing Excel with the names of the tabs already in the macro, because it would need to try and match up tabs from all of those files into the tabs of your master file.
As for finding the data, using the .End(xldown) (xlright) will help to grab your data as it has not spaces inside of it.
jc
pardon about the first question, I should have specified that it is the names of the tabs.
What I am thinking is providing Excel with the names of the tabs already in the macro, because it would need to try and match up tabs from all of those files into the tabs of your master file.
As for finding the data, using the .End(xldown) (xlright) will help to grab your data as it has not spaces inside of it.
jc
Upvote
0
okays,
Try the following, change the cells(10,10) so that it is in the middle of your data and it will go find the top left and bottom right.
Ensure that your master file has the tab names already present and have A1 and A2 as headers in the file, so A1 = "Sales Report", A2 = "Date"
Let me know if anything goes terribly wrong. I put in 1 errorhandler for if a sheet does not exist.
Regards,
jc
Try the following, change the cells(10,10) so that it is in the middle of your data and it will go find the top left and bottom right.
Ensure that your master file has the tab names already present and have A1 and A2 as headers in the file, so A1 = "Sales Report", A2 = "Date"
Let me know if anything goes terribly wrong. I put in 1 errorhandler for if a sheet does not exist.
Regards,
jc
Code:
Sub attemptedsimplification()
Dim strPath As String
Dim originalfile, currentfile As Workbook
Set originalfile = ActiveWorkbook
strPath = "S:\Joshua\!Daily Reports from SBUs\To be filed\" 'where files are temporarily stored
ChDir strPath
'change extension
StrExtension = Dir(strPath & "*.xls")
Do While StrExtension <> ""
Workbooks.Open (strPath & StrExtension)
Set currentfile = ActiveWorkbook
originalfile.Activate
For a = 1 To currentfile.Worksheets.Count
thename = Sheets(a).Name
currentfile.Activate
On Error GoTo errorhandler1
Sheets(thename).Select
Range(Cells(10, 10).End(xlToLeft).End(xlUp), Cells(10, 10).End(xlToRight).End(xlDown)).Copy
originalfile.Activate
Sheets(a).Cells(1, 1).End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Next a
currentfile.Activate
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
StrExtension = Dir
Loop
Exit Sub
':::::::::::::::::::::::::::::::::::
'::::::::: Error Hanlder 1 ::::::::
':::::::::::::::::::::::::::::::::::
errorhandler1:
MsgBox "Could not find sheet named " & thename & vbCrLf & "in file " & currentfile.Name, vbOKOnly
End Sub
Upvote
0
Similar threads
- Question
