Prev: Convert COUNTIFS function from 2007 to something in 2003 version
Next: Sumproduct
From: tom on 2 Apr 2010 15:47
In Excel 2003 I was wondering if it is possible to copy multiple cells A1:O75
in multiple worksheets (sheet1, sheet2, sheet3, etc) and pasting them
Consecutively into one work sheet (all records)?

Thanks for the help.
Tom
From: Paul on 2 Apr 2010 15:55

Try:




Code:
--------------------



Sub movedata()
Sheets("Sheet1").Range("A1:O75").Copy Sheets("Summary").Range("A1")
Sheets("Sheet2").Range("A1:O75").Copy Sheets("Summary").Range("A76")
Sheets("Sheet3").Range("A1:O75").Copy Sheets("Summary").Range("A151")
Application.CutCopyMode = False
End Sub

--------------------




Change your sheet names as necessary, of course. If you have a lot of
worksheets, you can loop through the sheets instead:




Code:
--------------------



Sub movedata2()
Dim ws as Worksheet, lrow as Long
lr = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row
For each ws in ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then
ws.Range("A1:O75").Copy Sheets("Summary").Range("A" & lr + 1)
lr = lr + 75
End If
Next ws
Application.CutCopyMode = False
End Sub

--------------------


--
Paul

- Paul
------------------------------------------------------------------------
Paul's Profile: 1697
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=192595

http://www.thecodecage.com/forumz

From: Don Guillett on 2 Apr 2010 16:03
One at a time with a one click looping macro

for each ws in sheets.count

ds=Sheets("destinationsheetnamehere")
if ws.name<>ds then
lr=ds.cells(rows.count,1).end(xlup).row+1
ws.range("a1:o75").copy ds.cells(lr,1)
end if

next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"tom" <tom(a)discussions.microsoft.com> wrote in message
news:81DA3BF0-918A-4194-B555-02A33765AD4D(a)microsoft.com...
> In Excel 2003 I was wondering if it is possible to copy multiple cells
> A1:O75
> in multiple worksheets (sheet1, sheet2, sheet3, etc) and pasting them
> Consecutively into one work sheet (all records)?
>
> Thanks for the help.
> Tom

 | 
Pages: 1
Prev: Convert COUNTIFS function from 2007 to something in 2003 version
Next: Sumproduct