moving ranges thru workbook

[Radomir]

Please
I can not move ranges located on separate sheets and
gather them into one big range. On 7 sheets I have
ranges: "monday", "tuesday" etc. Each range has the same #
of columns only number of rows is a variable. How to
collect all the data into one big range "week" located on
separate sheet of course. It seemed to be easy for me.
Thans Radomir

 

[Frank Kabel]

Hi
not quite sure but have a look at
http://www.rondebruin.nl/copy2.htm

 

[Otto Moehrbach]

This macro should do what you want. As written, this macro takes all the
data from Column A to Column J and as many rows as Column A has occupied,
from every sheet in the file (except "Weeks") and copies it to the Weeks
sheet. HTH Otto
Sub CopyData()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Week" Then GoTo Nextws
With Sheets(ws.Name)
.Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(,
10)).Copy _
Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Nextws:
Next ws
End Sub