Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to copy multiple cells between worksheets
I'm using Excel 2002. We have 3 worksheets for different people and 1
master. I'd like to take the information that is filled in on each of the 3 worksheets, each row has 9 columns, and copy that to the master sheet. I know how to copy a cell between sheets but not multiple cells from more then one worksheet. Is this possible to do? Thanks -- Dominic |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to copy multiple cells between worksheets
Can you pick out a column that can be used to determine the lastrow? I used
column A. Option Explicit Sub testme02() Dim wks As Worksheet Dim mstrWks As Worksheet Dim LastRow As Long Dim DestCell As Range Set mstrWks = Worksheets.Add Set DestCell = mstrWks.Range("a1") For Each wks In ActiveWorkbook.Worksheets If wks.Name = mstrWks.Name Then 'skip it Else With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("a1:A" & LastRow).Resize(, 9).Copy _ Destination:=DestCell End With 'get ready for next paste With mstrWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With End If Next wks End Sub Dominic wrote: I'm using Excel 2002. We have 3 worksheets for different people and 1 master. I'd like to take the information that is filled in on each of the 3 worksheets, each row has 9 columns, and copy that to the master sheet. I know how to copy a cell between sheets but not multiple cells from more then one worksheet. Is this possible to do? Thanks -- Dominic -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to copy multiple cells between worksheets
Thanks Dave for the response, to be honest I have no idea what you're saying.
My question may have been too vague or I'm to confused. We have 3 people entering data, name - number etc. on 3 separtate worksheets. We'd like have that data automatically copied from each of the sheets and combined onto one master. My first thought was I could copy cells but if I do that then each worksheet would need a dedicated row on the master sheet. That would cause empty lines, ie. worksheet 1 has rows 1 to 100 on the master while worksheet 2 has rows 101 to 200 etc. I'd like us to enter our information they have it populate on the next line in the master. Thanks again -- Dominic "Dave Peterson" wrote: Can you pick out a column that can be used to determine the lastrow? I used column A. Option Explicit Sub testme02() Dim wks As Worksheet Dim mstrWks As Worksheet Dim LastRow As Long Dim DestCell As Range Set mstrWks = Worksheets.Add Set DestCell = mstrWks.Range("a1") For Each wks In ActiveWorkbook.Worksheets If wks.Name = mstrWks.Name Then 'skip it Else With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("a1:A" & LastRow).Resize(, 9).Copy _ Destination:=DestCell End With 'get ready for next paste With mstrWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With End If Next wks End Sub Dominic wrote: I'm using Excel 2002. We have 3 worksheets for different people and 1 master. I'd like to take the information that is filled in on each of the 3 worksheets, each row has 9 columns, and copy that to the master sheet. I know how to copy a cell between sheets but not multiple cells from more then one worksheet. Is this possible to do? Thanks -- Dominic -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to copy multiple cells between worksheets
I think the best (well, to me anyway) is to refresh that master list each time
you need it updated. The code that I gave you took the all the worksheets in one workbook and created a master worksheet in that same workbook with all the data on it. It uses column A to determine the last row of each worksheet. You may want to test it against some test data in a test workbook. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Dominic wrote: Thanks Dave for the response, to be honest I have no idea what you're saying. My question may have been too vague or I'm to confused. We have 3 people entering data, name - number etc. on 3 separtate worksheets. We'd like have that data automatically copied from each of the sheets and combined onto one master. My first thought was I could copy cells but if I do that then each worksheet would need a dedicated row on the master sheet. That would cause empty lines, ie. worksheet 1 has rows 1 to 100 on the master while worksheet 2 has rows 101 to 200 etc. I'd like us to enter our information they have it populate on the next line in the master. Thanks again -- Dominic "Dave Peterson" wrote: Can you pick out a column that can be used to determine the lastrow? I used column A. Option Explicit Sub testme02() Dim wks As Worksheet Dim mstrWks As Worksheet Dim LastRow As Long Dim DestCell As Range Set mstrWks = Worksheets.Add Set DestCell = mstrWks.Range("a1") For Each wks In ActiveWorkbook.Worksheets If wks.Name = mstrWks.Name Then 'skip it Else With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("a1:A" & LastRow).Resize(, 9).Copy _ Destination:=DestCell End With 'get ready for next paste With mstrWks Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With End If Next wks End Sub Dominic wrote: I'm using Excel 2002. We have 3 worksheets for different people and 1 master. I'd like to take the information that is filled in on each of the 3 worksheets, each row has 9 columns, and copy that to the master sheet. I know how to copy a cell between sheets but not multiple cells from more then one worksheet. Is this possible to do? Thanks -- Dominic -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding cells from multiple Worksheets | Excel Discussion (Misc queries) | |||
zero value when copying cells between worksheets | Excel Discussion (Misc queries) | |||
Adding separate accumulators for multiple cells | New Users to Excel | |||
changing cells in multiple worksheets | Excel Worksheet Functions | |||
copy ranges from multiple worksheets | Excel Worksheet Functions |