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

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

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

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?

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?

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

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

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.

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

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

That would be great! Here is the share link to the workbook. I put my email in the Aggregate sheet. I will try to find where you are doing this later but would appreciate if you could make it work for now. Thanks Thanks Thanks so much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

That would be great! Here is the share link to the workbook. I put my email
in the Aggregate sheet. I will try to find where you are doing this later but
would appreciate if you could make it work for now. Thanks Thanks Thanks so
much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file! I
submitted a request and now waiting on a reply. Nice service worth looking into
as I use gmail...

--
Garry

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

On Tuesday, April 4, 2017 at 1:03:52 PM UTC-5, GS wrote:
That would be great! Here is the share link to the workbook. I put my email
in the Aggregate sheet. I will try to find where you are doing this later but
would appreciate if you could make it work for now. Thanks Thanks Thanks so
much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file! I
submitted a request and now waiting on a reply. Nice service worth looking into
as I use gmail...

--
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
Sorry. I think I have it accessible to you now. Initially, if wouldn't let me allow you. Can you try now?

On Tuesday, April 4, 2017 at 1:03:52 PM UTC-5, GS wrote:
That would be great! Here is the share link to the workbook. I put my email
in the Aggregate sheet. I will try to find where you are doing this later but
would appreciate if you could make it work for now. Thanks Thanks Thanks so
much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file! I
submitted a request and now waiting on a reply. Nice service worth looking into
as I use gmail...

--
Garry

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

Shoot. Help says I can make it available to anyone who has the shareable link but it still shows will only allow to peeps at Habitat. I can download it but then it becomes an excel document.

On Tuesday, April 4, 2017 at 1:03:52 PM UTC-5, GS wrote:
That would be great! Here is the share link to the workbook. I put my email
in the Aggregate sheet. I will try to find where you are doing this later
but would appreciate if you could make it work for now. Thanks Thanks
Thanks so much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file! I
submitted a request and now waiting on a reply. Nice service worth looking
into as I use gmail...

--
Garry

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

Shoot. Help says I can make it available to anyone who has the shareable link
but it still shows will only allow to peeps at Habitat. I can download it but
then it becomes an excel document.

Ok, it says they sent you a request to allow me access. This may include my
gmail email address; -if so send it direct as an attachment.

Otherwise, they say they'll notify me "if and when" you grant access...

Where is siouxland?

--
Garry

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

On Tuesday, April 4, 2017 at 1:03:52 PM UTC-5, GS wrote:
That would be great! Here is the share link to the workbook. I put my email
in the Aggregate sheet. I will try to find where you are doing this later but
would appreciate if you could make it work for now. Thanks Thanks Thanks so
much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file! I
submitted a request and now waiting on a reply. Nice service worth looking into
as I use gmail...

--
Garry

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

Ok Garry. I downloaded the folder and uploaded it into another google drive account. I added your email to who it could be share with on that account and it worked. You should be able to view and edit on that drive.

On Tuesday, April 4, 2017 at 1:03:52 PM UTC-5, GS wrote:
That would be great! Here is the share link to the workbook. I put my email
in the Aggregate sheet. I will try to find where you are doing this later
but would appreciate if you could make it work for now. Thanks Thanks
Thanks so much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file! I
submitted a request and now waiting on a reply. Nice service worth looking
into as I use gmail...

--
Garry

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

Ok Garry. I downloaded the folder and uploaded it into another google drive
account. I added your email to who it could be share with on that account and
it worked. You should be able to view and edit on that drive.

Ok.., I downloaded a zip file labelled 'Volunteer Sign Up' just shared with me
today. I'll take a look and email you...

--
Garry

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

On Tuesday, April 4, 2017 at 1:03:52 PM UTC-5, GS wrote:
That would be great! Here is the share link to the workbook. I put my
email in the Aggregate sheet. I will try to find where you are doing this
later but would appreciate if you could make it work for now. Thanks
Thanks Thanks so much!

https://docs.google.com/a/siouxlandh...it?usp=sharing

Hmmm.., that requires some kind of permission for me to access your file!
I submitted a request and now waiting on a reply. Nice service worth
looking into as I use gmail...

-- Garry

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

Ok Garry. I downloaded the folder and uploaded it into another google drive
account. I added your email to who it could be share with on that account
and it worked. You should be able to view and edit on that drive.

Ok.., I downloaded a zip file labelled 'Volunteer Sign Up' just shared with
me today. I'll take a look and email you...

It extracts an empty folder so I added it to MyDrive and there's nothing in the
folder. Add your file and I'll look for 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