Try This:
Sub ConsolidateAllSheets()
Dim wksConsolidate As Worksheet
Dim wksSheet As Worksheet
Dim lngLastRow As Long
Set wksConsolidate = ThisWorkbook.Worksheets.Add
lngLastRow = 1
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Consolidated").Delete
On Error GoTo 0
wksConsolidate.Name = "Consolidated"
If Not ThisWorkbook.ProtectStructure Then
With wksConsolidate
For Each wksSheet In ThisWorkbook.Worksheets
If Not wksSheet Is wksConsolidate Then
If lngLastRow = 1 Then
wksSheet.UsedRange.Copy .Range("A" & lngLastRow)
lngLastRow = .UsedRange.Rows.Count + 1
Else
Intersect(wksSheet.UsedRange,
wksSheet.UsedRange.Offset(1)).Copy .Range("A" & lngLastRow)
lngLastRow = .UsedRange.Rows.Count + 1
End If
End If
Next wksSheet
End With
End If
MsgBox "Done"
End Sub
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Greg
Sent: 07 June 2012 8:10
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Thank you Rajan,
This macro worked successfully. :-)
Is it possible to add a feature to the macro that will remove the header row
so that I don't have repetitive header information in the final worksheet?
Regards,
Greg
On Thursday, June 7, 2012 9:11:07 AM UTC-4, Rajan_Verma wrote:
Hi
Use this Macro :
Sub ConsolidateAllSheets()
Dim wksConsolidate As Worksheet
Dim wksSheet As Worksheet
Dim lngLastRow As Long
Set wksConsolidate = ThisWorkbook.Worksheets.Add
lngLastRow = 1
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Consolidated").Delete
On Error GoTo 0
wksConsolidate.Name = "Consolidated"
If Not ThisWorkbook.ProtectStructure Then
With wksConsolidate
For Each wksSheet In ThisWorkbook.Worksheets
If Not wksSheet Is wksConsolidate Then
wksSheet.UsedRange.Copy .Range("A" & lngLastRow)
lngLastRow = .UsedRange.Rows.Count + 1
End If
Next wksSheet
End With
End If
MsgBox "Done"
End Sub
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Greg
Sent: 07 June 2012 4:42
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi David,
Thanks for your reply. However, I tried this formula and it didn't work. The
result of the cell is 0. I wish to have the contents of each sheet copied
into one large worksheet.
-Greg
On Wednesday, June 6, 2012 6:49:01 PM UTC-4, David Grugeon wrote:
You can use a formula like
=sum(Sheet1:sheet17!A1)
and it will add all the cells A1 in the sheets called Sheet1, Sheet17 and
all sheets physically between them.
On 7 June 2012 05:52, Greg <gamoun...@gmail.com> wrote:
Hello,
I am trying to process an Excel macro I found online from the following
weblink:
http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
Sub Combine()
Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"
' copy headings
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub
According to the description, I can either use the Excel "Consolidate" tool
or the above macro. Unfortunately, I didn't have much luck with the
"Consolidate" tool because I don't understand how to reference each of the
worksheet ranges.
Once I run the macro, it appears to be doing something because I can see it
trans-versing through each worksheet. When the macro has completed, the
first sheet in the workbook is not named Combined, and it contains no data
from the other worksheets.
Thanks in advance for any assistance you can provide.
Kind regards,
Greg
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros%2bunsubscr...@googlegroups.com>
--
David Grugeon
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
