View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JBeaucaire[_131_] JBeaucaire[_131_] is offline
external usenet poster
 
Posts: 96
Default Combine sheets into one

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