For the entire used range simply remove the .offset(1)
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Greg
Sent: Thursday, June 07, 2012 1:54 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi Don,
This macro works and accomplishes what I need. And I agree with your KISS
principle whenever possible.
BTW, if I want to use this same macro and keep the headers what must I change?
Thanks,
Greg
On Thursday, June 7, 2012 12:52:25 PM UTC-4, Don Guillett wrote:
I understood you wanted the first or second row only. If you want all but the
header row.
Option Explicit
Sub copytoprowofallsheets()
'fire from the First sheet(sheet to far left)
Dim i As Long
For i = 2 To Sheets.Count
Sheets(i).UsedRange.Offset(1).Copy Cells(Rows.Count, 1).End(xlUp)(2)
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Greg
Sent: Wednesday, June 06, 2012 6:09 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
Hi Don,
Thanks for your reply. I want to do exactly what it says. I have identically
structured data in each worksheet, and wish to combine the multiple worksheets
into a single, large worksheet. The names of each worksheet is different.
However, they all have content in the first row. I wish to copy the first row
content one after the other into one large worksheet.
Regards,
Greg
On Wednesday, June 6, 2012 6:59:27 PM UTC-4, Don Guillett wrote:
I would have written it a bit more efficiently but what it is doing is
copying all of current region of each sheet to a new sheet.
What do you want to do???
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Wednesday, June 06, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
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 SubAccording 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
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
On Wednesday, June 6, 2012 6:59:27 PM UTC-4, Don Guillett wrote:
I would have written it a bit more efficiently but what it is doing is
copying all of current region of each sheet to a new sheet.
What do you want to do???
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: David Grugeon
Sent: Wednesday, June 06, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Condensing Multiple Worksheets Into One ...
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 SubAccording 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
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
--
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
