Copy sets of ranges
 

I need to copy different ranges of data to different
Worksheets. The data will always be in ranges C1:I?, the
next range will be J1:I?, so on. The # of ranges to copy
varies. Cell B2 shows how many ranges must be copied.
Here's what i have so far.

Dim lngLastRow As String
Dim shtActive As Worksheet
Dim myStartColumn, myEndColumn As String
lngLastRow = myDataSheet.UsedRange.Rows.Count
myStartColumn = 3
myEndColumn = myStartColumn + 7

For i = 1 To Range("b2").Value
Range("R[1]C[" & myStartColumn & "]:R[" & lngLastRow & "]C
[" & myEndColumn & "]").Select
Selection.Copy
Sheets("Item" & i & "").Select
Range("C1").Select
ActiveSheet.Paste

I'm gettin a :Run time error '1004' : Method 'Range' of
object'_Global'failed" error.

Please help.

Copy sets of ranges
 

Try this setup instead:

Sub Testo()
'Leo Heuser, 16 Oct. 2003
Dim lngLastRow As Long
Dim shtActive As Worksheet
Dim myStartColumn As Long
Dim myEndColumn As Long
Dim i As Long

lngLastRow = myDataSheet.UsedRange.Rows.Count
myStartColumn = 3
myEndColumn = myStartColumn + 7

With myDataSheet
For i = 1 To .Range("B2").Value
.Range(.Cells(1, myStartColumn), _
.Cells(lngLastRow, myEndColumn)). _
Copy Destination:=Sheets("Item" & i).Range("C1")
Next i
End With
End Sub


--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

"MDC" skrev i en meddelelse
...
I need to copy different ranges of data to different
Worksheets. The data will always be in ranges C1:I?, the
next range will be J1:I?, so on. The # of ranges to copy
varies. Cell B2 shows how many ranges must be copied.
Here's what i have so far.

Dim lngLastRow As String
Dim shtActive As Worksheet
Dim myStartColumn, myEndColumn As String
lngLastRow = myDataSheet.UsedRange.Rows.Count
myStartColumn = 3
myEndColumn = myStartColumn + 7

For i = 1 To Range("b2").Value
Range("R[1]C[" & myStartColumn & "]:R[" & lngLastRow & "]C
[" & myEndColumn & "]").Select
Selection.Copy
Sheets("Item" & i & "").Select
Range("C1").Select
ActiveSheet.Paste

I'm gettin a :Run time error '1004' : Method 'Range' of
object'_Global'failed" error.

Please help.

Copy sets of ranges
 

Assuming that your data is located in Sheet1 and ranges to
copy always start with the first range C1:?? and no ranges
are skipped, this code will copy all the ranges up to Range
("B2").Value.

Note: This code will overwrite previous values on Item ?
sheets.

BTW, This may not be the best way but it works. I'm not a
pro.

Hope this helps...


Sub test()
Dim rng As Range

'Enter ALL your columns here
Set rng = Worksheets("Sheet1").Range("A:A,J:J,M:M") _
..SpecialCells(xlCellTypeConstants)

k = 1
i = Worksheets("sheet1").Range("b2").Value
For j = 1 To i
rng.Areas(k).Copy _
Destination:=Worksheets("Item " & k).Range("c3")
k = k + 1
Next j



End Sub
-----Original Message-----
I need to copy different ranges of data to different
Worksheets. The data will always be in ranges C1:I?, the
next range will be J1:I?, so on. The # of ranges to copy
varies. Cell B2 shows how many ranges must be copied.
Here's what i have so far.

Dim lngLastRow As String
Dim shtActive As Worksheet
Dim myStartColumn, myEndColumn As String
lngLastRow = myDataSheet.UsedRange.Rows.Count
myStartColumn = 3
myEndColumn = myStartColumn + 7

For i = 1 To Range("b2").Value
Range("R[1]C[" & myStartColumn & "]:R[" & lngLastRow & "]C
[" & myEndColumn & "]").Select
Selection.Copy
Sheets("Item" & i & "").Select
Range("C1").Select
ActiveSheet.Paste

I'm gettin a :Run time error '1004' : Method 'Range' of
object'_Global'failed" error.

Please help.

.