Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a list of info in A1 cell in multiple worksheets | Excel Discussion (Misc queries) | |||
creating a list from a few worksheets not including blanks | Excel Worksheet Functions | |||
Creating number of worksheets based on list | Excel Discussion (Misc queries) | |||
Creating a List From Worksheets in a WorkBook | Excel Worksheet Functions | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming |