Help with macro to copy and paste based on formula result

[Phelony]

Hi

I'm pretty new to VBA although I've got (or so I thought) the majority of Excel mastered. I'm trying to build an intelligent form that basically does all the thinking for the user.

As part of this, I've built in sections that take names and then put them into a list, the list then gets counted and the result appears in a seperate cell.

From this cell with the result in is, say (C2), I need to build a macro that will then loop and copy a specific section of a sheet that number of times.

What I have so far is:

[VBA]Do
Dim ibtRowCount
ActiveCell.Range("A1:N5").Select
Selection.Copy
ActiveCell.Offset(6, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
Loop

End Sub[/VBA]

This however copies the selection an infinate number of times!

I've tried working on the "Dim introwcount as integer" etc that's available from Microsoft Office's own forums, but don't seem to be able to get it to work properly.

Could someone possibly suggest how I can tell the code to: find out what is in C2, then loop the copy and paste code that number of times.

I've been on this for hours now and I'm triving myself insane .

Any help or guidance would be very much appreciated.

Many thanks

Phelony

[Bob Phillips]

[vba]

Dim i As Long
For i = 1 To Range("C2").Value
With Range("A1")

.Resize(5, 14).Copy .Offset(i * 5 + 1, 0)
End With
Next i
[/vba]

[Phelony]

Thanks for this Xld!

Still a small hurdle though, my code now looks like this:

[VBA]Do
Dim ibtRowCount
Dim i As Long
For i = 1 To Range("R2").Value
With Range("A1")
ActiveCell.Range("A1:N5").Select
Selection.Copy
ActiveCell.Offset(6, 0).Range("A1").Select
ActiveSheet.Paste
.Resize(5, 14).Copy .Offset(i * 5 + 1, 0)
End With
Next i
Loop

End Sub[/VBA]

I've moved the variable from C2 to R2 as the cut and paste was copying this section as well which caused it to increase the number of times it was supposed to copy.

Now it copies the content the correct number of times, however, it's now copying the an empty section of the same size infinately...

Where am I screwing up?

Thanks so much for your and sorry for taking up your time with what I'm sure is a simple issue.

Phelony x

[Bob Phillips]

Well, if you don't use the code I provide, you cannot blame me for it not working. You still have the Do Loop.

[Phelony]

Ahh, I thought that was an amendment to be added, not the whole code!

Works amazingly! Thanks so much!!

[mdmackillop]

Hi Phelony,
Welcome to VBAX
When you post code, select it and click the green VBA button to format it as shown.
Regards
MD