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.

.