Creating a list from 2 or 3 different worksheets
 

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

Creating a list from 2 or 3 different worksheets
 

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

--
Regards,
Tom Ogilvy


"bruce" wrote in message
...
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

Creating a list from 2 or 3 different worksheets
 

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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"bruce" wrote in message
...
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

Creating a list from 2 or 3 different worksheets
 

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

--
Regards,
Tom Ogilvy


"bruce" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy


"bruce" wrote in message
...
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