Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 4th 09, 07:48 PM posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 197
Default Combine sheets into one

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

  #2   Report Post  
Old December 4th 09, 08:02 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
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

  #3   Report Post  
Old December 4th 09, 08:13 PM posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 197
Default Combine sheets into one

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?
--
Greatly appreciated
Eva


"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

  #4   Report Post  
Old December 4th 09, 08:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 96
Default Combine sheets into one

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?


  #5   Report Post  
Old December 4th 09, 11:59 PM posted to microsoft.public.excel.worksheet.functions
Eva Eva is offline
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
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?




  #6   Report Post  
Old April 3rd 17, 01:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2017
Posts: 6
Default Combine sheets into one

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
  #7   Report Post  
Old April 3rd 17, 03:26 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Combine sheets into one

Paste this into a standard module:

Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.


Option Explicit

Sub ConsolidateSheets()
Dim lRow&, lRow2&, vData, vSh, wsTgt As Worksheet
Dim CalcMode As XlCalculation

'Get a ref to the target sheet
Set wsTgt = ThisWorkbook.Sheets("Consolidate")
wsTgt.Cells.ClearContents: lRow2 = 1

'EnableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False
CalcMode = .Calculation: .Calculation = xlCalculationManual
End With 'Application

'Transfer data from the other sheets to the next empty row
On Error GoTo Cleanup
For Each vSh In ThisWorkbook.Sheets
If Not vSh = wsTgt Then
lRow = vSh.Range("A" & vSh.Rows.Count).End(xlUp).Row + 1
vData = vSh.Range("A1:BB" & lRow)
wsTgt.Cells(lRow2, "A").Resize(UBound(vData), UBound(vData, 2)) = vData
lRow2 = lRow2 + UBound(vData) '//set next blank row
End If
Next 'vSh

Cleanup:
Set wsTgt = Nothing
'DisableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False: .Calculation = CalcMode
End With 'Application
End Sub 'ConsolodateSheets

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #8   Report Post  
Old April 4th 17, 12:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2017
Posts: 6
Default Combine sheets into one

On Monday, April 3, 2017 at 8:26:21 AM UTC-5, GS wrote:
Paste this into a standard module:

Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.


Option Explicit

Sub ConsolidateSheets()
Dim lRow&, lRow2&, vData, vSh, wsTgt As Worksheet
Dim CalcMode As XlCalculation

'Get a ref to the target sheet
Set wsTgt = ThisWorkbook.Sheets("Consolidate")
wsTgt.Cells.ClearContents: lRow2 = 1

'EnableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False
CalcMode = .Calculation: .Calculation = xlCalculationManual
End With 'Application

'Transfer data from the other sheets to the next empty row
On Error GoTo Cleanup
For Each vSh In ThisWorkbook.Sheets
If Not vSh = wsTgt Then
lRow = vSh.Range("A" & vSh.Rows.Count).End(xlUp).Row + 1
vData = vSh.Range("A1:BB" & lRow)
wsTgt.Cells(lRow2, "A").Resize(UBound(vData), UBound(vData, 2)) = vData
lRow2 = lRow2 + UBound(vData) '//set next blank row
End If
Next 'vSh

Cleanup:
Set wsTgt = Nothing
'DisableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False: .Calculation = CalcMode
End With 'Application
End Sub 'ConsolodateSheets

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Hi Garry
Thanks for the quick response. Unfortunately, you are probably going to tell me I have no business try to do this.......
I don't know what you mean by
'Standard Module' or 'Project Explorer pane'.
Paste this into a standard module:

Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.

I have a folder in my google drive with three forms which are feeding into one workbook. In the workbook, I have three tabs/sheets of data feeding in from the forms and I have made an additional tab/sheet called Aggregate where I would like the data from the three sheets/forms to feed into from the individual sheets. I will continue to add more forms, hence more tabs/sheets of data will be added to the workbook on a continual basis.

Does this make sense and are you able to clarify Module and Project Explorer pane?

I so much appreciate your input.
  #9   Report Post  
Old April 4th 17, 04:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Combine sheets into one

On Monday, April 3, 2017 at 8:26:21 AM UTC-5, GS wrote:
Paste this into a standard module:

Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.


Option Explicit

Sub ConsolidateSheets()
Dim lRow&, lRow2&, vData, vSh, wsTgt As Worksheet
Dim CalcMode As XlCalculation

'Get a ref to the target sheet
Set wsTgt = ThisWorkbook.Sheets("Consolidate")
wsTgt.Cells.ClearContents: lRow2 = 1

'EnableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False
CalcMode = .Calculation: .Calculation = xlCalculationManual
End With 'Application

'Transfer data from the other sheets to the next empty row
On Error GoTo Cleanup
For Each vSh In ThisWorkbook.Sheets
If Not vSh = wsTgt Then
lRow = vSh.Range("A" & vSh.Rows.Count).End(xlUp).Row + 1
vData = vSh.Range("A1:BB" & lRow)
wsTgt.Cells(lRow2, "A").Resize(UBound(vData), UBound(vData, 2)) =
vData lRow2 = lRow2 + UBound(vData) '//set next blank row
End If
Next 'vSh

Cleanup:
Set wsTgt = Nothing
'DisableFastCode
With Application
.ScreenUpdating = False: .EnableEvents + False: .Calculation = CalcMode
End With 'Application
End Sub 'ConsolodateSheets

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Hi Garry
Thanks for the quick response. Unfortunately, you are probably going to tell
me I have no business try to do this....... I don't know what you mean by
'Standard Module' or 'Project Explorer pane'.
Paste this into a standard module:

Key Alt+F11 to open the VBE:
Right-click your file in the Project Explorer pane;
InsertModule.

I have a folder in my google drive with three forms which are feeding into
one workbook. In the workbook, I have three tabs/sheets of data feeding in
from the forms and I have made an additional tab/sheet called Aggregate where
I would like the data from the three sheets/forms to feed into from the
individual sheets. I will continue to add more forms, hence more tabs/sheets
of data will be added to the workbook on a continual basis.

Does this make sense and are you able to clarify Module and Project Explorer
pane?

I so much appreciate your input.


Ok then, you'll need to change the name of the sheet in the code by editing
this line as shown...

Set wsTgt = ThisWorkbook.Sheets("Aggregate")


Code goes in the Visual Basic Editor (VBE) component. You can open it using
keyboard combo 'Ctrl+F11'.

The 'Project Explorer' pane looks like the folderview pane of the Files
Explorer. In there you'll see your workbook listed; -right-click its name or
any item under that to access a popup menu. Click 'Insert' to display a
submenu, then click 'Module'.

You should now have a window titled "<yourfilename - Module1 (Code). This is
where you paste the code.

To run the code press 'Ctrl+F8' and select the macro in the listbox; -then
click 'Run'. That's it!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #10   Report Post  
Old April 4th 17, 04:31 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 763
Default Combine sheets into one

Optionally, you could upload your file to a publlic share and post a download
link here so I can retrieve it. Make sure your email address is inside the file
so I can send the finished project directly to you.

If your data is confidential then provide sample data to work with!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
combine two work sheets -NTW Sonny Excel Discussion (Misc queries) 1 September 11th 09 12:08 PM
Compare two sheets and combine into one Walt Moeller Excel Worksheet Functions 2 December 17th 08 07:26 PM
problems trying to combine two sheets [email protected] Excel Discussion (Misc queries) 0 June 29th 06 02:21 PM
Combine to Sheets... JFALK Excel Discussion (Misc queries) 2 June 29th 05 02:58 PM
HELP!!! combine 4 different sheets ??? TonyKA Excel Discussion (Misc queries) 0 May 13th 05 03:05 PM


All times are GMT +1. The time now is 03:02 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017