combining data from multiple worksheets into one


Y

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
 
Ad

Advertisements

R

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.
 
R

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
 
E

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top