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
0
Utf
12/4/2009 6:48:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
840 Views

Similar Articles

[PageSpeed] 3

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
0
Utf
12/4/2009 7:02:01 PM
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
0
Utf
12/4/2009 7:13:01 PM
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?

0
Utf
12/4/2009 7:24:01 PM
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?
> 
0
Utf
12/4/2009 10:59:02 PM
Reply:

Similar Artilces:

Two AD Domains, One Exchange Server
Hello, I have two companies that need to share resources between one another. I'm currently in the redesign phase of the first company and the second is a startup. We will have an Active Directory Domain Controller for each company. Each company will be in a physically different location with a point to point fiber network connection between. I would like to have the ability to share files and printers between each domain. I know I can accomplish this via a two way trust relationship. What I'm confused about is sharing a single Exchange 2003 Server between both domains. After I ...

sheets #2
Hello, I have a problem. I have sheet1,sheet2 and sheet3. In sheet1 i have 2 columns ( column1 and column2). Column1 Column2 text1 3 text2 5 1. problem In sheet2 I need to create drop down box so I can choose if I want text1 or text2. But how can I import to drop down box column1 and column2 data from sheet1? Example. If I choose on sheet2 from drop down box text1, it needs to write text1 and number 3 (text1 in one column and number 3 in other column) 2. problem In sheet 3 I have simple drop down box which reads Column1 from sheet1. But I need manually to ...

Print 3 receipts in one register
I have 2 printers, one for receipt and the other for reports. In the Store Operation Manager I have configured Printer1 for sales receipt format and printer 2 for gift receipt format. Physically they share the same printer,the receipt printer. How can I print a third full page receipt by the report printer? -- Nothing Unfortunately you can't, you can only have the 2 defined per Register. Rob "NothingMuch" <NothingMuch@discussions.microsoft.com> wrote in message news:8ABF2B40-5DD7-489D-852C-6A1ABC8E59B5@microsoft.com... >I have 2 printers, one for receipt and t...

Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have no use for all of the rows at the same time and have to keep hiding and showing them as need arises. Is there a macro to hide chunks of it based on the value of one cell of the row? In other words, en each row I will have a formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to hide the row or not. I tried case.select but it takes a LONG time and I would have to write a piece of code for every line. FYI, the rows that need hiding will be in sequence, in other words, fro...

Get $500 to your PAYPAL Account by just one CLICK #2
i have hidden the form of getting $500 to your PAYPAL ACCOUNT....CLICK on the IMAGE which is on the RIGHT SIDE of the website http://tricks-4u.4-all.org/ ...

problems with forming sheets
PHP code ------------------- Hello I have a problem that is a bit complicated for me. Is there anybody who has idea? I have 2 excel sheets. In the first sheet , there is a lot of data like Column A Column B Column E Model Name Price1 1000 ABC 20 1003 ASC 10 1004 WSX 30 1005 EDF 20 And in the second one, there is also a lot of data with price 2. Column A Column B Column F Model Name Price2 1000 ABC 40 ...

how to print 4x1 on one using publisher
Hi There How do I print 4 copies on one sheet of A4 using microsoft publisher? Yours P D Is your publication at least one-quarter of the size of the sheet you're printing on? Or is your design a full-size and you are trying to scale it down to fit 4 on a page? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "paulantpat" <paulantpat@discussions.microsoft.com> wrote in message news:8288E435-9741-4139-AD12-FD987A148CE5@microsoft.com... > Hi There > > How do I print 4 copies on one sheet of A4 us...

Multiple E-Mail Addresses On One Account: Identities
A question: my ISP (Earthlink) provides me with eight e-mail addresses for my account. I use one address for personal mail, another for business, etc. I'd like to set up each of these e-mail addresses as a separate identity, but don't see how to do that (it appears that the identities are linked to the ISP). Can anyone help? Thanks. Are you using Outlook or Outlook Express? Outlook uses profiles, not identities. If you are using Outlook Express, post in an Outlook Express news group. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact...

S.O.S. Need Assistance ~ Is this one possible & how?
I'm trying to create a formula in a range that basically equals designated percentage which will be used to graph a chart on a separat worksheet. The example would be: if a date text is entered into the range, an once that is populated that particular filled in range will then equa 10% (the date text shows in the range not the percentage) Thank you -- Message posted from http://www.ExcelForum.com FIGURED IT OUT...IT WORK -- Message posted from http://www.ExcelForum.com ...

Cannot open Price Sheet
Hi All, We encounter an error message that says "This record is currently in use by another user" when trying to open a Price Sheet although no one is using such record. Kindly advise how to fix this problem. Thank you. Hi All, I already found the solution. I cleared the tables in Dynamics: ACTIVITY SY00800 SY00801 And in TempDb: DEX_LOCK DEX_SESSION -- Yani "Yani" wrote: > Hi All, > > We encounter an error message that says "This record is currently in use by > another user" when trying t...

Mailbox permssions from one storage group to another
I have two storage groups, each with one mailbox store. The first storage group contains a public folder store. All of the permissions for the users in the first storage appear to be correct and functioning normally. Everyone in the second storage group looks to have much different inherited mailbox permissions that don't look to be correct. Almost all of the groups including administrator, enterprise admin, exchange domain server have inherited allow AND deny for full mailbox access. Is this correct? I doesn't seem right to me... Where do you change what inherited rights are aqu...

Index/Contents Sheet
I use Excel for 90% of the day. I use and create lots of sheets. Nothing is worse than when your Boss asks you a question and you say "I have that info in a sheet I created last month", but because it i over a month ago you either cannot remember the name or in which of th multitude of folders it lives in. and he is standing there looking mor and more frustrated as you do a search on *.xls at a rough date. I got really sick of this scenario and decided to do something about i and this might help you! Save a sheet called 'Commonly Used Files' or anything you want i Program...

Print 2 pages on one page in Excel
I need to print 2 pages on one page, I have 3 colunms of data on each page 400 pages, I want to print 6 columnms wich would be 2 pages of data on one page how do i do this?? Hi BIGCHAD36 Look on David's site http://www.mvps.org/dmcritchie/excel/snakecol.htm -- Regards Ron de Bruin http://www.rondebruin.nl "BIGCHAD36" <BIGCHAD36@discussions.microsoft.com> wrote in message news:BD9FC46D-2DBC-403D-9B96-EFA2A503A0C3@microsoft.com... >I need to print 2 pages on one page, I have 3 colunms of data on each page > 400 pages, I want to print 6 columnms wich would be 2 p...

Missing Sheet tab #2
The tab where I select Sheet1, Sheet2, Sheet3 etc is missing how can I get it back??? I am using Excel 2007 Thanks Kamal Try Changing your display resoulution up a step? And then open the file. Kamal wrote: > The tab where I select Sheet1, Sheet2, Sheet3 etc is missing how can I get > it back??? > > I am using Excel 2007 > > Thanks > Kamal > > ...

2 Cells on 2 Sheets
Hello! I have a workbook with 2 sheets. Currently - Sheet 1, A1 is a dollar amount entered by the user Sheet 2, B1 references Sheet 1, A1 and is locked. Now - We would like the user to be able to change Sheet 2, B1 and have it update Sheet 1, A1 and vice versa. So that the user has the option of changing the dollar amount in 2 places, rather than on Sheet 1 only. Is this possible? Thanks in advance! Sarah You can do it by using an event macro that looks for changes. But this can easily break if the user doesn't allow macros to run--or even turns off events. I wouldn't use this....

How can I print only select sheets in my workbook?
My workbook has 12 sheets, but I want to print the second through ninth sheet exclusively, each time I hit print. (I do this once a week.) How do I do it? Excel 2007 Select sheet2 then hold SHIFT key and click on sheet9 File>Print>Active Sheet(s) Gord Dibben MS Excel MVP On Thu, 27 Mar 2008 19:14:00 -0700, MVictoreen <MVictoreen@discussions.microsoft.com> wrote: >My workbook has 12 sheets, but I want to print the second through ninth sheet >exclusively, each time I hit print. (I do this once a week.) How do I do it? >Excel 2007 Hi MVictoreen! As an optional m...

transfer shares from one a/c to another, lose history
Hi, To transfer shares from one investment account to another, which is the best option to use? I have been using the transfer out option, but by doing that, all the transaction history is lost. Is there a way to preserve it? Thanks. It's still preserved in the original account. "ab" <nospam@nospam.hotmail.com> wrote in message news:26qqf.4339$Ou3.1708@dukeread09... > To transfer shares from one investment account to another, which is the > best option to use? I have been using the transfer out option, but by > doing that, all the transaction history is l...

copy a pivot table to another sheet in a macro
Hi there, I created a macro that will copy copy the information from a created pivot table to another sheet. I seemed ok when I recorded the macro. But there's no pivot table on the new sheet when I run the macro. What should I do to correct the problem? Thanks, Aline -- Aline Record another macro when you try it again? If it fails again, you may want to describe what you did to copy the data (copy the cells to a new sheet or copy the sheet???). And share the code (that fails) that does the copy. Aline wrote: > > Hi there, > > I created a...

sharing one .pst file on 2 networked computers
what i want to do is have 2 computers on a network share the same outlook.pst file - i have opened the outlook data file on the second computer but it cant be open on both computers at the same time - is there a way to do this. i hope you understand what im trying to do :) thanks MS does not support the use of a pst over a network - it will likely lead to corruption. OL requires exclusive use of the pst. "chris" <someone@here.com> wrote in message news:ex1dau5LFHA.3452@TK2MSFTNGP10.phx.gbl... > what i want to do is have 2 computers on a network share the same > ...

Combox Box in an excel s/sheet , is it possible ?
Hi all, I want to be able to choose a value from a combo box within a cell, is it possible without vba etc ? cheers, Adam Hi try using data validation ('Data - Validation') have a look at http://www.contextures.com/xlDataVal01.html or the Excel help for some information how to use this -- Regards Frank Kabel Frankfurt, Germany Bomber wrote: > Hi all, > > I want to be able to choose a value from a combo box within a cell, > is it possible without vba etc ? > > cheers, > > Adam ...

sheet tabs as page number and in a cell page of pages?
i have looked a little but can't find what i think is the right thing. i have a workbook with 3-4 pages in it. in the upper righthand corner is a page of pages cells set up. i want to have it grab the current tab(page) for one cell and the rest of the tabs(pages) for the other cell. i am not very good at the coding VB thing so exact instructions would be grately appreciated. i have tried a few of the suggestions but cannot get them to work. i am using excel 2002. TIA ...

how to merge data from multiple columns to one column
I have first, middle, and last name in different columns. How do I merge these three into one column? Try... =A1&" "&B1&" "&C1 for the format John T. Smith OR =C1&", "&A1&" "&B1 for the format Smith, John T. Hope this helps! In article <24D1088D-0AB1-4910-BF0E-37D8D7A4C056@microsoft.com>, "w8ting4hlp" <w8ting4hlp@discussions.microsoft.com> wrote: > I have first, middle, and last name in different columns. How do I merge > these three into one column? ...

One cell from several sheets to one column
I have a spreadsheet with over 200 tabs/worksheets. I want to copy the same cell from every worksheet into one sheet into a single column. The worksheets have unique names. Is a macro the easiest way to move the data? If so, how can I set the macro up to go through each tab, so I don't have to go to each tab and run the macro separately? Thanks, Kel Examples of sheet names and what cell you want? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------------------------------------...

Copying emails from one os to another
I recently upgraded the os on my computer and I was wondering how to copy messages from one os to the other. Jamie White <jamie.boss@jatos.co.uk> wrote: > I recently upgraded the os on my computer and I was > wondering how to copy messages from one os to the other. http://www.slipstick.com/config/backup.htm -- Brian Tillman ...

Summarising large amt of data across sheets
Hi, Ok .. we deal with large amounts of data across multiple sheets. W have data in this form SHEET 1 a b c d e f 1 a1 b1 c1 d1 e1 f1 2 a2 b2 c2 d2 e2 f2 3 a3 b3 c3 d3 e3 f3 4 a4 b4 c4 d4 e4 f4 5 a5 b5 c5 d5 e5 f5 6 a6 b6 c6 d6 e6 f6 7 a7 b7 c7 d7 e7 f7 and SHEET 2 a b c d e f 1 aa1 bb1 cc1 dd1 ee1 ff1 2 aa2 bb2 cc2 dd2 ee2 ff2 3 aa3 bb3 cc3 dd3 ee3 ff3 4 aa4 bb4 cc4 dd4 ee4 ff4 5 aa5 bb5 cc5 dd5 ee5 ff5 6 aa6 bb6 cc6 dd6 ee6 ff6 7 aa7 bb7 cc7 dd7 ee7 ff7 8 aa8 bb8 cc8 dd8 ee8 ff8 Now, we use this format because we have many such rows and colums (i'v used only 8 rows and 6 columns fo...