View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
Posts: 197
Default Combine sheets into one

Hi JBeaucaire
I am back (I was on a Christmas party) and I tested it and works beutifully.
You are awsome!
Thank you!
--
Greatly appreciated
Eva


"JBeaucaire" wrote:

Use this instead, edit the ARRAY() to the sheet names you want to include:
=========
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 Sheets(Array("Data1", "Data2", "Data3"))
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.Range("A1:BB" & LR).Copy
cs.Range("A" & NR).PasteSpecial xlPasteValues
Application.CutCopyMode = False
NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
Next ws

End Sub
========

Your feedback is appreciated, click YES if this post helped you.
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

"Eva" wrote:

Hi JBeaucaire
This is a great macro, but it copies all sheets into consolidate sheet. I
need only copy three specific sheets and I have in my workbook many other
sheets that I don't need to combine. Can you help?