Copying multiple cells out of multiple worksheets at same time.

[tom]

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

 

[Paul]

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

 

[Don Guillett]

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("a175").copy ds.cells(lr,1)
end if

next