software

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

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(quot;a1quot;)

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = mstrWks.Name Then
'skip it
Else
With wks
LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
.Range(quot;a1:Aquot; amp; LastRow).Resize(, 9).Copy _
Destination:=DestCell
End With
'get ready for next paste
With mstrWks
Set DestCell = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Offset(1, 0)
End With
End If
Next wks

End SubDominic wrote:
gt;
gt; I'm using Excel 2002. We have 3 worksheets for different people and 1
gt; master. I'd like to take the information that is filled in on each of the 3
gt; worksheets, each row has 9 columns, and copy that to the master sheet. I
gt; know how to copy a cell between sheets but not multiple cells from more then
gt; one worksheet. Is this possible to do?
gt;
gt; Thanks
gt; --
gt; Dominic

--

Dave Peterson

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
--
Dominicquot;Dave Petersonquot; wrote:

gt; Can you pick out a column that can be used to determine the lastrow? I used
gt; column A.
gt;
gt; Option Explicit
gt; Sub testme02()
gt; Dim wks As Worksheet
gt; Dim mstrWks As Worksheet
gt; Dim LastRow As Long
gt; Dim DestCell As Range
gt;
gt; Set mstrWks = Worksheets.Add
gt; Set DestCell = mstrWks.Range(quot;a1quot;)
gt;
gt; For Each wks In ActiveWorkbook.Worksheets
gt; If wks.Name = mstrWks.Name Then
gt; 'skip it
gt; Else
gt; With wks
gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; .Range(quot;a1:Aquot; amp; LastRow).Resize(, 9).Copy _
gt; Destination:=DestCell
gt; End With
gt; 'get ready for next paste
gt; With mstrWks
gt; Set DestCell = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Offset(1, 0)
gt; End With
gt; End If
gt; Next wks
gt;
gt; End Sub
gt;
gt;
gt; Dominic wrote:
gt; gt;
gt; gt; I'm using Excel 2002. We have 3 worksheets for different people and 1
gt; gt; master. I'd like to take the information that is filled in on each of the 3
gt; gt; worksheets, each row has 9 columns, and copy that to the master sheet. I
gt; gt; know how to copy a cell between sheets but not multiple cells from more then
gt; gt; one worksheet. Is this possible to do?
gt; gt;
gt; gt; Thanks
gt; gt; --
gt; gt; Dominic
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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:
www.mvps.org/dmcritchie/excel/getstarted.htmDominic wrote:
gt;
gt; Thanks Dave for the response, to be honest I have no idea what you're saying.
gt; My question may have been too vague or I'm to confused. We have 3 people
gt; entering data, name - number etc. on 3 separtate worksheets. We'd like have
gt; that data automatically copied from each of the sheets and combined onto one
gt; master. My first thought was I could copy cells but if I do that then each
gt; worksheet would need a dedicated row on the master sheet. That would cause
gt; empty lines, ie. worksheet 1 has rows 1 to 100 on the master while worksheet
gt; 2 has rows 101 to 200 etc. I'd like us to enter our information they have it
gt; populate on the next line in the master. Thanks again
gt; --
gt; Dominic
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Can you pick out a column that can be used to determine the lastrow? I used
gt; gt; column A.
gt; gt;
gt; gt; Option Explicit
gt; gt; Sub testme02()
gt; gt; Dim wks As Worksheet
gt; gt; Dim mstrWks As Worksheet
gt; gt; Dim LastRow As Long
gt; gt; Dim DestCell As Range
gt; gt;
gt; gt; Set mstrWks = Worksheets.Add
gt; gt; Set DestCell = mstrWks.Range(quot;a1quot;)
gt; gt;
gt; gt; For Each wks In ActiveWorkbook.Worksheets
gt; gt; If wks.Name = mstrWks.Name Then
gt; gt; 'skip it
gt; gt; Else
gt; gt; With wks
gt; gt; LastRow = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Row
gt; gt; .Range(quot;a1:Aquot; amp; LastRow).Resize(, 9).Copy _
gt; gt; Destination:=DestCell
gt; gt; End With
gt; gt; 'get ready for next paste
gt; gt; With mstrWks
gt; gt; Set DestCell = .Cells(.Rows.Count, quot;Aquot;).End(xlUp).Offset(1, 0)
gt; gt; End With
gt; gt; End If
gt; gt; Next wks
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; Dominic wrote:
gt; gt; gt;
gt; gt; gt; I'm using Excel 2002. We have 3 worksheets for different people and 1
gt; gt; gt; master. I'd like to take the information that is filled in on each of the 3
gt; gt; gt; worksheets, each row has 9 columns, and copy that to the master sheet. I
gt; gt; gt; know how to copy a cell between sheets but not multiple cells from more then
gt; gt; gt; one worksheet. Is this possible to do?
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; --
gt; gt; gt; Dominic
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson