You are here:

Excel/VBA to consolidate multiple worksheets into one report

Advertisement


Question
QUESTION: Tom,

First of all, I want to thank you for all your help in advance.

I have a primitive program that is a database. When I print reports, I am able to save them as a text file and I then import them into Excel by using the Text to Columns tool.

The problem is the program only lets me print 6 columns at a time. So, my report has a total or 8 reports with 6 columns each.

Each report has it's own sheet in Excel when I am done importing. I want to be able to combine all the sheets into one MASTER sheet so that I can compare all the fields on one large printout.

The first column of each report contains IDs that do not duplicate. Each report has the same unique IDs. The following 5 columns are various fields that do not repeat.

I have been able to use VLookUp until this point, but I have to do this multiple times a month and I wish to make it automated. VLookUp works perfectly because no ID is duplicated.

Here's an example:
8 sheets all have the same 1st column called ID. These ID's are simply 111,112,113,114,115,116,117,118,119,120 and so on.

The following 5 columns in the first report are Color, Size, Type, Quantity, Source.
The next report has the same ID's in the first column and then the following 5 columns have New Characteristics that are unique from the first report. (Example : Yes/No, Fruit, Candy...etc.)

Is there a way to copy all these reports onto one sheet (the first sheet which has the first part of the report)? The logic would be to Look for the ID on the second sheet and then copy the remaining columns until a blank one is reached and then paste it into the first sheet where the ID matches.

Thank you for your time.

TJ

ANSWER: TJ,

First, are the rows in the same order in every report?  It seems logical that they would be.  If they are, then you just need to copy  the 4 columns to the first report and paste them in so that they line up properly.

If they are not in the same order, then I would copy all the columns into the combined sheet and sort them so they are in ID sequence.  Then delete the redundant ID columns (except for the first).

Another way would be to put in the vlookup formulas, then replace them with the values they show.

Or if the IDs are in the same order

Sub abc()
Dim v As Variant
Dim sh As Worksheet, lastrow As Long, j As Long
Dim i As Long

v = Array("sheet1", "sheet2", "Sheet3", "Sheet4", _
         "Sheet5", "Sheet6", "Sheet7", "Sheet8")
Set sh = Worksheets(v(LBound(v)))
lastrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
j = 7
For i = LBound(v) + 1 To UBound(v)
 sh.Cells(1, j).Formula = "=" & v(i) & "!" & "B1"
 sh.Cells(1, j).Copy sh.Cells(1, j).Resize(lastrow, 5)
 j = j + 5
Next
sh.Cells.Copy
sh.Cells.PasteSpecial xlValues
End Sub


worked for me.  Change the sheet names in the array to match your sheet names.  If you sheet name has a space in it, then you need to do this.  Say instead of Sheet1 you first sheet is named Sheet 1.  then you would have

v = Array("'sheet 1'", "sheet2", "Sheet3", "Sheet4", _
         "Sheet5", "Sheet6", "Sheet7", "Sheet8")

so that when it is used in the formula it will be enclosedin single quotes.  

So it is hard to write any code without knowing the details.  Hopefully I have guessed correctly and the above will work for you (text it on a copy of your workbook).

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom,

Thank you so much! It works like a charm.

I realized that not EVERY report has exactly 6 columns, so if there were more (or less) is there a way to tell it to copy every column (after the first column) that has data in it? This way it would copy only columns with data in it.

Also, this does work perfectly, but what if my report was to somehow throw out an ID by mistake (EG. 111,112,114,115.... 113 is missing)

This is probably not going to ever happen, but is there a way to check to make sure that ID in the first sheet (MASTER) matches the ID in the corresponding sheet before copying and pasting?

The macro could simply just end with an error or it could continue to copy only the rows that have matching IDs on both reports.

I only want this for a sanity check, and it is another form of preventing wrong data to be represented.

Thanks so much for your help!

Answer
TJ,

If you don't have exactly 6 columns, then the easiest way to do this would be address the largest number of columns that might be on any one sheet.  That would put blank columns on the master sheet - so I would alter the code to have a last set of instructions that would loop through row 1 and delete any column that was blank in row 1.  

So that would be a simple fix.

Any other type of change would require much more work.  for the simplest,  I could go to a vlookup, but then if the first sheet did not have 113 but other sheets did, the vlookup would miss the data on the other sheets.  Using the vlookup would also use the max column approach above.

So if you want the max columns or the max columns and the vlookup, then post back with the max columns to copy.  Also what version of excel you are using.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.