On Friday, December 4, 2009 at 1:02:01 PM UTC-6, JBeaucaire wrote:
Here's a macro I use for this, it will create a sheet called "CONSOLIDATE"
and copy all data from all sheets into it.
==========
Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
If Not Evaluate("ISREF(Consolidate!A1)") Then _
Worksheets.Add(After:=Worksheets(Worksheets.Count) ).Name = "Consolidate"
Set cs = Sheets("Consolidate")
cs.Cells.ClearContents
NR = 1
For Each ws In Worksheets
If ws.Name < "Consolidate" Then
ws.Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:BB" & LR).Copy
cs.Range("A" & NR).PasteSpecial xlPasteValues
Application.CutCopyMode = False
NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
End If
Next ws
cs.Activate
Range("A1").Select
End Sub
==========
Hope that helps...
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)
Your feedback is appreciated, click YES if this post helped you.
"Eva" wrote:
Hi
I have three sheets called:"Matched",Unmatched", "Other" with the same
columns and headings.
I need to combine all in one sheet called "Data All"
I think I need the macro, but I am not so experienced (I started learning
VBA) so I don't know how to do it
Can you help me?
--
Greatly appreciated
Eva
JBeaucaire
Thank you so much for this code. However, where do I enter this code? Into Script Editor of my workbook?
If yes, the code flags as follows:
Missing ; before statement. (line 1, file "Code")
I changed 'Consolidated' to the name of my sheet which is 'Aggregate' and change A1 references to A2 since my data starts on A2.
Can you help guide me in the right direction?
Linda, Habitat for Humanity