How to copy multiple cells between worksheets

[Dominic]

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]

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

[Dominic]

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]

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