Count Cells with Values in a Range Then Use it to create rows

[hamistasty]

I want vba code to give the Sum of column A in Sheet("Schedule") from row 9 onwards until there is no value in a cell.

Then I want the code to Copy row 13 of Sheet("Index") and insert it below the same row the amount of times that the previous code counted in the other sheet.

Appreciate any help..

 

[hamistasty]

Something like this..? Obviously it's wrong, but I can't work out how to copy and paste the row for the count in Schedule.

Sub CreateIndex()
Dim I As Long
 
 For I = 9 To Sheets("Schedule").Range("A" & Rows.Count).End(xlUp).Row
 
 Sheets("Index").Range("C13").Copy
Next I
 
End Sub

 

[hamistasty]

Please, any help?

 

[Yahya]

May Be something like this

Sub Test()
Dim LR As Long, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Schedule")
Set ws2 = Sheets("Index")
LR = ws1.Range("a" & Rows.Count).End(xlUp).Row
ws1.Range("b2").Value = WorksheetFunction.Sum(ws1.Range("a9:a" & LR))
ws1.Range("b3").Value = ws2.Range("A13").Value
End Sub

 

[hamistasty]

Not sure if it works, but thanks. It needs to count starting at A9 in the line:

LR = ws1.Range("a" & Rows.Count).End(xlUp).Row

How can I start the count at row 9??

 

[hamistasty]

Never mind. I'm not sure how this is supposed to work. It isn't creating rows from C13 onwards in Index (the amount of times being the Count of values in each row from A9 down until a null value is reached.)

Any other ideas?

 

[hamistasty]

bumpity bump