A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Combine sheets into one



 
 
Thread Tools Display Modes
  #1  
Old December 4th 09, 06:48 PM posted to microsoft.public.excel.worksheet.functions
Eva
external usenet poster
 
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
Ads
  #2  
Old December 4th 09, 07:02 PM posted to microsoft.public.excel.worksheet.functions
JBeaucaire[_131_]
external usenet poster
 
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  
Old December 4th 09, 07:13 PM posted to microsoft.public.excel.worksheet.functions
Eva
external usenet poster
 
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  
Old December 4th 09, 07:24 PM posted to microsoft.public.excel.worksheet.functions
JBeaucaire[_131_]
external usenet poster
 
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  
Old December 4th 09, 10:59 PM posted to microsoft.public.excel.worksheet.functions
Eva
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?

>

  #6  
Old April 3rd 17, 01:20 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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  
Old April 3rd 17, 03:26 PM posted to microsoft.public.excel.worksheet.functions
GS[_6_]
external usenet poster
 
Posts: 697
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;
Insert>>Module.


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  
Old April 4th 17, 12:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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;
> Insert>>Module.
>
>
> 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;
Insert>>Module.

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  
Old April 4th 17, 04:28 AM posted to microsoft.public.excel.worksheet.functions
GS[_6_]
external usenet poster
 
Posts: 697
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;
>> Insert>>Module.
>>
>>
>> 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;
> Insert>>Module.
>
> 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  
Old April 4th 17, 04:31 AM posted to microsoft.public.excel.worksheet.functions
GS[_6_]
external usenet poster
 
Posts: 697
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 06: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 02:48 PM.


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