Updated list from multiple sheets

[ningo]

Hi Folks,

I am new to the forum, but not completely new to Excel

I have a question to which I can't find an answer myself.

a workbook containing 4 sheets, all sheets have the same format, only the number of rows and the content of the cells changes.

What I would like is a new worksheet with a macro, so that when i push the button update (or whatever)
that all 4 existing worksheets are copied to the new one, without leaving blanks etc. and this even if you have previously added items to one of the 4 sheets.
Is that possible. Can someone please guide me in the right direction.

The final goal is to have 1 huge list which is composed of the 4 sheets

many thx

 

[Redwolfx]

a workbook containing 4 sheets, all sheets have the same format, only the number of rows and the content of the cells changes.

When you say the content changes Are you saying that Column A may have a "name" on sheet 1 and a "date" on sheet 2?

If so how are you looking to format the consolidated sheet?

-Red

 

[ningo]

Hi Redwolfx,

thx for the reply!
No the columns always contain the same data in all 4 sheets!
I.E:
Title Description Number Price

This is the same for every worksheet. What I need is a macro which copy/pastes the values (not the title) in each of the 4 sheets to one new sheet so that i get a complete list of all items on all 4 lists...without blanks in between
Is this understandable? I hope I am clear on this...

 

[danzon]

This should do it

http://www.mrexcel.com/forum/excel-questions/44997-copy-multiple-sheets-into-one.html

 

[Redwolfx]

So I've done something similar,

Sub NewButton()
Dim Sht As Worksheet
Dim Rng As Range

Worksheets("Master List").Range("A3:AB" & Range("AB" & Rows.Count).Row).Clear

For Each Sht In Sheets
If Sht.Name = "Master List" Then
Else
Set Rng = Sht.Range("A2:AB" & Sht.Range("AB" & Rows.Count).End(xlUp).Row)
Rng.Copy Destination:=Sheets("Master List").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Sht
With Sheets("Master List").Range("A2:AB" & Range("AB" & Rows.Count).End(xlUp).Row)
End With
ThisWorkbook.Save
End Sub

This assumes that worksheet "Master List" has headings in row 2 (Left room for a button in row 1 to run the code)

It also assumes all the data from sheets 1 - 4 have data starting on row 2 (row 1 is headings)

It clears the data from the master list then copies and pastes the data from each sheet 1 - 4 into the master sheet.

And Saves.

I hope this is what you were looking for.

-Red

 

[dmt32]

Hi,
have a look here should give some guidance. Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel
If you sure how to adjust code to meet your need post back plenty of us here to give support.

Dave

 

[ningo]

Ahhh, yes that is more or less what I need.
The exception is that I want it to start reading in row 5 and also places the first value in the summary in row 5

how can i get that done?

 

[ningo]

redwolfx code works for as far as copying a list from different sheet into one.
The problem I have now is that it is copying the title and only 1 row (title is in row 1, so it copies row 1 and row 2 and finishes)

Ahhh, yes that is more or less what I need.
The exception is that I want it to start reading in row 5 and also places the first value in the summary in row 5

how can i get that done?

 

[ningo]

please someone help me... it is very urgent

 

[Redwolfx]

Can you include the code as you have altered it (If you have altered it)?

Also How many Columns do Sheets 1 - 4 have?