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

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

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

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