Consolidate sheets [vba]
Question:
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.
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 […]
Merge Ranges is an add-in for Excel that lets you easily merge multiple ranges into one master sheet. The Master […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]
This article explains how to merge values row by row based on a condition in column A using an array […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
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 […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel formula categories
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"?