Creating a list from 2 or 3 different worksheets

[bruce]

I have several worksheets that I have contact info in. I like to keep the
worksheets seperate (my own pet peeve- I know it would probaly be easier to
combine them all into one sheet....)
I would like to create a self updating "Mastersheet" that pulls all of the
info from the other worksheet into this one. How would I "step" (step is
probably not the best word, but....) column1 , on each sheet, then copy that
info into the mastersheet, column1.
I was thinking for a nested IF, comparing the row() to the COUNTA of each
sheet, but is there a cleaner, or simpler way?

Thanks

Bruce

 

[Tom Ogilvy]

Dim i as Long
Dim sh as Worksheet
Dim rng as Range
for i = 1 to 3
set sh = worksheets("Sheet" & i)
set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,1).End(xldown))
rng.copy destination:=Worksheets("Master") _
.Cells(rows.count,1).End(xlup)(2)
Next

 

[bruce]

Tom,

Thanks for the reply!
I am having a little issue with the code, as it is only filling from SHEET1,
it is not going to sheet2, or 3. ...

I currently have this set up as a macro. Is there a way where it will up
date the master when new data is added to either of the other sheets?, or to
be ran when ever MASTER is selected?
Also, how would I handle it if I have differnt sheet names?

TIA,

Bruce

 

[Tom Ogilvy]

Sub AABB()
Dim i As Long
Dim sh As Worksheet
Dim rng As Range
For i = 1 To 3
Set sh = Worksheets("Sheet" & i)
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown))
rng.Copy Destination:=Worksheets("Master") _
.Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub

Was tested and does exactly what you describe.

If you have odd sheet names:

Replace the AA, BB, etc with your sheet names.

Sub AABB()
Dim i As Long
Dim sh As Worksheet
Dim rng As Range
vArr = Array("AA","BB","CC")
For i = lbound(varr) to ubound(varr)
Set sh = Worksheets(vArr(i))
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(1, 1).End(xlDown))
rng.Copy Destination:=Worksheets("Master") _
.Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub