VBA to merge multiple Workbooks with multiple sheets retaining headers

**LauraN1** · 08-16-2010, 10:34 AM

VBA to merge multiple Workbooks with multiple sheets into one workbook with multiple sheets, each with a different header row.

Hello

I am using Excel 2007 and want to achieve the following.

I have 75 Workbooks('xlsm) in a directory, with the same 4 worksheets in each.
The names of the 4 worksheets are "Wins","Pitches","Awards","Promo"

Each worksheet has header rows starting at A3 which are unique to that Sheet i.e the headers for "Wins" are different to the headers for "Pitches"etc.
Data always commences at Row A4 regardless of which sheet it is.

Also the data within each sheet contains references to "named Ranges" which need to be retained in the transfer if possible.,

I have tried lots of different VBA's and the RDB Merge Macro, but I come up against any one of the following issues.

a) Sometimes it asks me if I want to replace the Named Range or keep it as it is. (This is very tiresome as I have about 150 named ranges and it asks me over and over for each occurence within the sheets and the workbooks.)

b) I can get pretty close by using RDB Merge, but that restricts me to converting to "values" and does not import the headers or the formats orginally used. Also this has to be repeated for each worksheet separately.

I don't mind if the overall result converts to values as long as the headers and formats are retained for each worksheet.

I would really appreciate any help here.

**JBeaucaire** · 08-16-2010, 12:24 PM

'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS
I have a macro that may be pretty close to "ready to use" for collecting data from all files in a specific folder. (and/or subfolders). Published here:

Workbooks to Sheets

=========
I've edited it for what I believe are your needs in terms of sheets, but you will need to edit the fPath string in line 22 This macro goes into the consolidation workbook, and that workbook should not be in the same folder as the files being imported.

Please Login or Register  to view this content.

**LauraN1** · 08-18-2010, 06:35 AM

Hi Jerry

Thank you so much for your reply.

I'm not sure if I am doing this right but I changed the fPath = "C:\2010\" to
fPath = "C\countries\merging\" which is where all the spreadsheets are held

I also changed fName = Dir("*.xls") to fName = Dir("*.xlsm") as these are the extensions to my files.

I have copied the programming to a new empty file in a different directory. (Am I supposed to copy all the headers across first or not?)

When I run the macro I get the message - "Compile Error" "Syntax Error" and then the degugger highlights the top line - Sub ConsolidateWBsToSheets() and goes no further.

What am I doing wrong?

Thanks for all your help

Laura

**JBeaucaire** · 08-18-2010, 08:26 AM

Post up your workbook with the misbehaving macro in it along with a copy of one of your import files. I'll take a look directly.

**LauraN1** · 08-18-2010, 11:22 AM

Hi Jerry

Thanks so much for your help.
I am attaching the following files:
Consolidated empty file with macro - "consolidate.xlsm"
two examples of files to be merged - "australia.xls","belgium.xls"
Empty workbook with headers and formatted columns. - "forum.xlsm"

Look forward to your reply

Laura

consolidate.xlsm

forum.xlsm

australia.xlsx

belgium.xlsx

**JBeaucaire** · 08-18-2010, 11:32 AM

Remove everything after END SUB in the macro module. That's extra garbage and Excel can't find the END SUB.

Also, the macro would be IN the workbook you are creating.

**LauraN1** · 08-18-2010, 12:12 PM

Hi Jerry

Have removed everything after the End Sub.
Have put the macro in a blank template.
Now when I run the macro I get the following:-
"Run time error "91" Object variable or with block variable not set.
The debugger then sends me to this line in the macro:-
LR = ws.Cells.Find("*", Cells(ws.Rows.Count, ws.Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

I also notice that it has opened up one of the import files: "Australia" and is just replicating what is in the Australia Wins and Losses Line in all the other worksheets i.e current Pitches, Awards, Promotion.
Each of these different worksheets within the workbook must not be amalgamated together only the same named worksheets from each of the other files i.e
Australia Wins and Losses should be amalgamated with Belgium Win & Losses.
Australia Current Pitches should be amalagamated with Belgium Current Pitches etc.

Hope this makes sense.

As always looking forward to your reply.

Many thanks

Laura

**JBeaucaire** · 08-18-2010, 01:47 PM

Oops, one big booboo in the targeting code and I added the need to look and see if there is any data to copy in the first place.

Please Login or Register  to view this content.

**LauraN1** · 08-18-2010, 02:34 PM

OK

Have put the new coding into the empty spreadsheet.
I now get: "Run-time error "9" Subscript out of range.
The debugger points at
ws.Range("A4:A" & LR).EntireRow.Copy _
wbkNew.Sheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Also the first imported file - Australia opens up with its own data in it.

Am I right to have put this macro into an empty file with no headers?

Looking forward to your response

Laura

**JBeaucaire** · 08-18-2010, 03:18 PM

I put the macro into the Consolidation book you uploaded.

**LauraN1** · 08-18-2010, 03:37 PM

Ok ,that is where I put it.
I have set the fpath to the directory where australia.xlsx and belgium.xlsx are held for this test purpose (i.e in the excel test sub folder).Line reads as follows:
fPath = "C:\Documents and Settings\NealL\My Documents\\excel test\"

When the macro out of the consolidate workbook I still get the same errors as the last post.
"run time errror "9" Subscript out of range
ws.Range("A4:A" & LR).EntireRow.Copy _
wbkNew.Sheets(ws.Name).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

As before Australia.xlsx opens in its original format.

Am I doing anything else wrong?

**JBeaucaire** · 08-18-2010, 04:33 PM

Yes, Australia workbook should be open at that moment, the macro is trying to copy FROM that workbook, so it should be open. The macro is bailing before it has a chance to copy from and close that workbook...moving on to Belgium next.

There are variables in that line of code. When you DEBUG, what are the values in each of the variables? Hover your mouse over each to read them:

LR =
ws.Name =

**JBeaucaire** · 08-18-2010, 04:36 PM

Ah, I didn't actually know about those hidden sheets (tsk-tsk).

This version will NOT look at any other sheets. Try this:

Please Login or Register  to view this content.

**LauraN1** · 08-18-2010, 05:40 PM

Sorry about the hidden sheets. I use those for the name ranges.

I have copied the new coding and it fails again. Same error message

I think these are the values you need from the debug

LR =5
ws.Range("A"&ws.Rows.Count).End(xlUp).Rows=5

I don't know if this is of any relevance, but all the empty rows are formatted and most include drop down options and data validation, so in a sense are not exactly empty.

I look forward to your response.

I think here in the UK we are 8 hrs ahead of California, so if you don't mind, I will look at your post tomorrow. Look forward to it.

Laura

**JBeaucaire** · 08-18-2010, 10:28 PM

Originally Posted by JBeaucaire

LR =
ws.Name =

The second variable you got wrong. What is the ws.name?

**LauraN1** · 08-19-2010, 03:27 AM

Sorry - thanks for being patient with me.

w.sName="Wins & Losses"

**JBeaucaire** · 08-19-2010, 08:36 AM

Subscript error 9 and the ws.Name = "Wins & Losses" leads me to believe the macro can't find the "Wins & Losses" sheet in your wbkNew workbook.

**LauraN1** · 08-19-2010, 10:50 AM

OK Now I get it

This time I have put the macro into my blank spreadsheet which has all the worksheet names, the headers and the formatting and hey presto it works like a dream !
You are a genius. Nobody else has managed to get anywhere close.

I can't thank you enough for all your time and effort.

Since I am new to this forum, how do I go about rating your postings .

Laura

**JBeaucaire** · 08-19-2010, 01:51 PM

Use the BLUE SCALES icon in the upper right corner of one of my posts.

**LauraN1** · 08-19-2010, 03:47 PM

That's Done

Thanks once again

Laura

VBA to merge multiple Workbooks with multiple sheets retaining headers

LinkBack

Thread Tools

Rate This Thread

Display

VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Re: VBA to merge multiple Workbooks with multiple sheets retaining headers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions