Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Dominic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dominic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding cells from multiple Worksheets Joe Excel Discussion (Misc queries) 1 October 6th 05 06:25 PM
zero value when copying cells between worksheets joe Excel Discussion (Misc queries) 1 August 9th 05 08:51 PM
Adding separate accumulators for multiple cells jrambo63 New Users to Excel 1 May 26th 05 06:56 PM
changing cells in multiple worksheets boyd Excel Worksheet Functions 2 May 23rd 05 10:10 PM
copy ranges from multiple worksheets simora Excel Worksheet Functions 2 May 18th 05 01:42 AM


All times are GMT +1. The time now is 12:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"