combining data from multiple worksheets into one

[yowzers]

I have several worksheets in one workbook. Each worksheet is for a different
store location (i.e. Store 1, Store 2, Store 3, etc). I have dates listed in
Column A and profits listed in Column B.

I would like to create a another worksheet that combines entries from all
these stores into one master log but listed in sequential dates. I want this
master log to update automatically every time I enter an entry into one of
the individual stores. I don't want to just copy and paste old data into the
master log. I also want column C to state which store this came from. Is
there some sort of lookup function that can do this for me?

Basically, if I enter into sheet Store 1:
1/1/09 $100
1/5/09 $200

And enter into sheet store 2:
1/1/09 $200
1/3/09 $400

I want the master log sheet to automatically fill as:
1/1/09 $100 Store 1
1/1/09 $200 Store 2
1/3/09 $400 Store 2
1/5/09 $200 Store 2

 

[ryguy7272]

Wow, sounds like a tall order. Let’s start with one thing and then take care
of the other things. Go here:
http://www.rondebruin.nl/copy2.htm

Read it carefully and copy/paste the code into a module so you can
consolidate all your data onto one SummarySheet. Get that working and then
we’ll take care of the other stuff.

Keep in mind, this probably won’t be really easy. You’re trying to make a
spreadsheet work like a database. You can certainly do it, but it’s not good
practice.

 

[ryguy7272]

Hummm, I just found this code; little easier to work with perhaps:
Public Sub Test()
Dim sh As Worksheet
Dim newSh As Worksheet
Dim i As Long
Dim NextRow As Long

NextRow = 1
Set newSh = Worksheets.Add(after:=Worksheets(Worksheets.Count))
newSh.Name = "FINAL"
For Each sh In Worksheets
If sh.Name <> "FINAL" Then
sh.UsedRange.Copy newSh.Cells(NextRow, "A")
With newSh.UsedRange
NextRow = .Row + .Rows.Count + 1
End With
End If
Next sh
End Sub

 

[Eva]

There is the formula that will combine it to you
Copy this in col
=IF(ROW()>COUNTA(Sheet1!A:A)+COUNTA(Sheet2!A:A),"",IF(ROW()<=COUNTA(Sheet1!A:A),OFFSET(Sheet1!$A$1,ROW()-1,0),OFFSET(Sheet2!$A$1,ROW()-COUNTA(Sheet1!A:A)-1,0)))

Copy this to column
=IF(ROW()>COUNTA(Sheet1!B:B)+COUNTA(Sheet2!B:B),"",IF(ROW()<=COUNTA(Sheet1!B:B),OFFSET(Sheet1!$B$1,ROW()-1,0),OFFSET(Sheet2!$B$1,ROW()-COUNTA(Sheet1!B:B)-1,0)))

Click yes if helped

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Combine multiple sheets.
Group by months, subtotals, totals, filter, graph.
All with no formulas, no code.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_26_09.xlsx

 

[kelvin duen]

have you found a solution to this yet?

 

[kelvin duen]

have you found a solution to this yet?