Consolidate sheets [vba]
Question:
I have multiple worksheets in a workbook. Each worksheets is project specific. Each worksheet contains almost identical format. The columns I am interested in each workshets are "Date Plan", "Date Compelted" and "variance" and "Project Code"I then want data from all these column to be extracted in a Report worksheet and later want to do a trend chart by sorting all dates in chronological order.
Key bit to start is how do I get data out from worksheets. Esentially I want all the data without any loss. There are chances that some of the data between worksheet1 & 2 could be identical, apart from project code.
Also, preference is that this coding or formula should work for any future addition to worksheet data and workbook worksheets.
Answer:
This vba code copies all values from each column header in each sheet to "consolidate" sheet. You can choose which sheets to consolidate, cell A2 and down. See picture above.
You can also choose what column headers to consolidate. Cell B1 and cells to the right. Remember column headers must be on row 1 in each sheet. Cell values don't have to be contiguous in each sheet.
VBA
Sub Consolidate() Application.ScreenUpdating = False 'Dim Dim csShts As Range Dim clmnheader As Range Dim sht As Worksheet Dim LastRow As Integer Dim i As Long Set csShts = Worksheets("Consolidate").Range("A2") Set clmnheader = Worksheets("Consolidate").Range("B1") 'Iterate sheet cells on sheet "consolidate" Do While csShts <> "" ' Iterate all sheets to find a match between sht and csShts For Each sht In Worksheets 'Find a matching sheet i = 0 If sht.Name = csShts Then 'Select sheet sht.Select 'Select cell A1 on sheet Range("A1").Select 'Iterate columnheaders on sheet Do While Selection <> "" 'Iterate column headers on consolidate sheet Set clmnheader = Worksheets("Consolidate").Range("B1") Do While clmnheader <> "" 'Find matching column headers on consolidate sheet against column headers on current sheet If clmnheader.Value = Selection.Value Then 'Find last row in column LastRow = ActiveSheet.Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row End If 'Save maximum last row number on current sheet If LastRow > i Then i = LastRow End If 'Move to next column header on consolidate sheet Set clmnheader = clmnheader.Offset(0, 1) Loop ActiveCell.Offset(0, 1).Select Loop sht.Select Range("A1").Select Set clmnheader = Worksheets("Consolidate").Range("B1") 'Iterate columnheaders from beginning on current sheet Do While Selection <> "" Set clmnheader = Worksheets("Consolidate").Range("B1") 'Iterate column headers on consolidate sheet Do While clmnheader <> "" If clmnheader.Value = Selection.Value Then Set clmnheader = clmnheader.Offset(1, 0) 'Copy range Do While Selection.Row <= i ActiveCell.Offset(1, 0).Select Selection.Copy clmnheader.Insert Shift:=xlDown Loop ActiveCell.Offset(-i, 0).Select Set clmnheader = clmnheader.Offset(-i, 0) 'Set clmnheader = clmnheader.End(xlUp) End If 'Move to next column header on consolidate sheet Set clmnheader = clmnheader.Offset(0, 1) Loop 'Move to next cell on current sheet ActiveCell.Offset(0, 1).Select Loop End If Next sht 'Move to next cell Set csShts = csShts.Offset(1, 0) Loop Sheets("Consolidate").Select End Sub
Now you can easily sort dates in chronological order and create a trend chart.
Get excel file
(Excel 97-2003Â Workbook *.xls)
Remember to backup your original excel file. You can´t undo a macro.
Useful links
Combine data from multiple sheets - Microsoft
Consolidate data in Excel and merge multiple sheets into one worksheet
Consolidate Data From Multiple Worksheets in a Single Worksheet in Excel
Combine merge category
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
11 Responses to “Consolidate sheets [vba]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have down loaded the consolidated file and it does not appear to work the way I expected.
I am looking to combine cashflow worksheets for multiple projects. The row headings are the same for each project but the column heading varies (they are set as end of month dates)
The issue with normal consolidation is that each sheet has to be identical. But because each project starts and stops at different months the consolidation is messy
Can I use your example and modify it.
You can use it and modify it.
It is an interesting question and I´ll post an answer as soon as possible.
the code has it that all the headers must be in row 1 of each sheet. How would you modify if the headers all on the 13th row on each sheet?
Neville Ash,
Read https://www.get-digital-help.com/2010/09/06/consolidate-sheets-in-excel-part-2/
This is great! Almost exactly what I was looking for.
I got the consolidate sheet and I am able to get all of my data to consolidate fine. If I update something and press consolidate again, it displays a second set of data instead of replacing what showed up the upon the first press with left mouse button. Is there a way to replace the existing data on the consolidate sheet when ever I press with left mouse button on "consolidate"?
also, after the 540th row, only half of the columns show up. any ideas? all told, I have 14 columns and 921 rows.
Stephen,
This line deletes all values in range B2:E65536:
Worksheets("Consolidate").Range("B2:E65536") = ""
Copy and paste after this line:
Dim i As Long
Get the Excel file
stephen.xls
hi
i have two sheets in my workbook
sheet1 :
A14:A200 = item code
E14:E200 = quantity
sheet2 :
column "C" = item code
column "I" = Quantity
i want if
sheet1 :
A14=101, A15=102 & E14=4&E15=5
sheet 2 :
C15=101, c22=102 & C15=20, C22=25
then,,,
vba search for 101 nd 102 in sheet 2, column c
and add quantity
ans could be
C15=24 & E15=30
can any1 help me for this
Deepak,
Can you provide som sample data?
Hello Oscar,
thanks for updating this code...
can you do a favour,,,, if i am using this code to consolidate the data,it is working fine for me but can i get the sheet(project) name as well against the data, so that i can track, from which project i pick the number,
This code does exactly what I was looking for, except in one case: if the fields being consolidated have formulas that refer to columns I'm not consolidating (ie it returns #ref or #value errors). Is there any way to amend the code to "paste values"?