Inserting Rows with For Loops

[johnpalf]

I'm writing code to copy rows and then insert copies of the same rows below. The numer of times it should copy and paste those rows is defined as Unit 1-7. After each for loop, I would like the code to insert a different row that sepeates each block. Please see the code below. The bold italic portion is what I need help with. The SOR row is not copying/pasting in.

'define number of catalysts used
Range("D7").Select
c = ActiveCell.Value
m = c
NameofSheet = ActiveSheet.Name
'Naming each row and expected number of units
'Each name corresponds to a row based on the cat. and the loading method

Dim Cat1 As Range
Set Cat1 = Rows(15)
Dim Cat2 As Range
Set Cat2 = Rows(16)
Dim Cat3 As Range
Set Cat3 = Rows(17)
Dim Cat4 As Range
Set Cat4 = Rows(18)
Dim Cat5 As Range
Set Cat5 = Rows(19)
Dim Cat6 As Range
Set Cat6 = Rows(20)
Dim Cat7 As Range
Set Cat7 = Rows(21)
Dim SOR As Range
Set SOR = Rows(29)

Unit1 = Application.RoundUp(Sheets(NameofSheet).Range("K4") / 10, 0) - 1
Unit2 = Application.RoundUp(Sheets(NameofSheet).Range("K5") / 10, 0) - 1
Unit3 = Application.RoundUp(Sheets(NameofSheet).Range("K6") / 10, 0) - 1
Unit4 = Application.RoundUp(Sheets(NameofSheet).Range("K7") / 10, 0) - 1
Unit5 = Application.RoundUp(Sheets(NameofSheet).Range("K8") / 10, 0) - 1
Unit6 = Application.RoundUp(Sheets(NameofSheet).Range("K9") / 10, 0) - 1
Unit7 = Application.RoundUp(Sheets(NameofSheet).Range("K10") / 10, 0) - 1

'The units are defined correctly. To prove to yourself, uncomment the following block of code.
Range("C50").Select
SOR.Select
Selection.Copy
Selection.Insert Shift:=xlDown

Range("C15").Select 'set active cell to first catalyst
For j = 1 To Unit1 'loops for each row that needs to be created
Cat1.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Next j
SOR.Select
Selection.Copy
Selection.Insert Shift:=xlDown

For j = 1 To Unit2
Cat2.Select
Selection.Copy
Selection.Insert Shift:=xlDown
Next j
SOR.Select
Selection.Copy
Selection.Insert Shift:=xlDown

 

[johnpalf]

Thanks. That second loop option looks much better. Unfortunately, SOR, what I'm trying to copy and paste no longer is on row 29 because other rows have been inserted/deleted. Is there anyway to copy something without referring to its range which could change?

 

[Norie]

I think the main problem with the code is all the select/selection stuff.

When you use that in code you can't really be sure what's actually being copied/selected/inserted etc.

Any chance you could upload a sample workbook to a file sharing site like Box.net?

Perhap with some sort of before/after kind of thing.

 

[johnpalf]

Ya. Apprecate the offr. Let me get it to a place where I think it will be most useful. By before after do you mean what it should look like after or what it does look like after? I guess I could kind of do both. And I assume dropbox should work for you?

 

[Norie]

Prefer Box.net - too many ads on DropBox.

 

[johnpalf]

https://berkeley.box.com/s/qu8j1dfy9b3tc4btmpel
K I have an account but don't usually use it. This work?

 

[Norie]

I'm sorry, but I have no idea what you are trying to do.

I can't even find the code you originally posted anywhere in the 3 workbooks you uploaded.

 

[johnpalf]

No I'm sorry about that. Someone else made the workbook. I'm supposed to edit it and make it work since there were a lot of errors. In the VBA editor it's the Module called "AddLoadingSheets." The code that I wrote is in that module after the comment, "John P added this..."