Using range with cells (copying columns) - Error message

[dsalves]

Hi,

I want to create add sheets copying some columns from a "master" sheet using the range command.

I am having an issue with a Error 1004 "Application-defined or Object-defined error" when selecting a range using a cell format. I would like to use cells so I coud have the columns in a loop. Here is what I am trying to use:

Set qty = Sheets("B562 Production Release").Range(Cells(66, 3), Cells(341, 3))

I initially created the code below. But now I can't change the columns for "qty" range (LINE 07) for each new sheet added. Can anyone have a quick look and check what I am missing?

Sub CreateBOMS()
Dim mycell As Range, myrange As Range, i As Integer
Set myrange = Sheets("B562 Production Release").Range("c49")
Set myrange = Range(myrange, myrange.End(xlToRight))
For Each mycell In myrange
Sheets.Add after:=Sheets(Sheets.Count)
Set qty = Sheets("B562 Production Release").Range("c66:c341")
Set description = Sheets("B562 Production Release").Range("bs66:bs341")
Set parts = Sheets("B562 Production Release").Range("bt66:bt341")
For i = 1 To 341
Sheets(Sheets.Count).Cells(i + 3, 1).Value = parts(i)
Sheets(Sheets.Count).Cells(i + 3, 2).Value = description(i)
Sheets(Sheets.Count).Cells(i + 3, 3).Value = qty(i)
Next i
Sheets(Sheets.Count).Columns("A:C").AutoFit
Sheets(Sheets.Count).Name = mycell.Value
Next mycell
End Sub



Thank you!

 

[VoG]

Try

Set qty = Sheets("B562 Production Release").Range(Sheets("B562 Production Release").Cells(66, 3), Sheets("B562 Production Release").Cells(341, 3))

 

[dsalves]

Wow, It is working fine now.

Thanks for the prompt reply!