Macro to copy Paste from C to A

[srinidhi]

I want help from experts in setting up a macro.

The problem goes like this.

I want to copy the text in C1 & paste it from A1:A23, next copy the text from c24 & paste it in A24:A46, next copy the text from c47 & paste it in A47:A69. There are about 5000 rows in C.

 

[Luke M]

How's this?

[pre]

Sub StepCopy()
Dim StepRate As Integer

'Used this from your example
StepRate = 23
'Initial row to start at
i = 1

Application.ScreenUpdating = False
Do While i < 5000 '<--When do you want to stop looking in col C?
Cells(i, "C").Copy Range(Cells(i, "A"), Cells(i + StepRate - 1, "A"))
i = i + StepRate
Loop
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Hui]

Macro Schmacro


Why not just a good formula like:

In A1:

=OFFSET($C$1,23*INT((ROW()-1)/23),)

Then copy down

 

[Luke M]

If we go the formula way, maybe we should use the non-volatile INDEX function instead?

=INDEX(C:C,23*INT((ROW()-1)/23)+1)


*cheeky grin*


Please take this comment as a silly attempt at humor. I do fully appreciate Hui's wonderful idea of just using a formula instead. Especially considering it's past midnight there!

 

[Hui]

or the also volatile

=INDIRECT("C"&23*INT((ROW()-1)/23)+1)