O.K. now I'm just being a pain. I figured it out... change it from

Private Sub Worksheet_Calculate()

to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Learn something new everyday.

"JLatham" wrote:

There are several ways to code this up. I'll give two ways. First is fairly
clear in what it's doing and probably "good enough" for relatively small
values in A2.

The second may not be so clear, but can be very effective/fast, especially
if the values in A2 are going to be very large.

To get them into your workbook: open it up, press [Alt]+[F11] to enter the
VB Editor. In there, choose Insert -- Module. Copy and paste the code
below into the module presented to you. Close the VB Editor and test them to
see which you like the best.

Sub PasteItWithLoop()
Dim pCount As Integer
Dim LC As Integer

pCount = ActiveSheet.Range("A2")
If pCount < 1 Then
Exit Sub
End If
'the clear way to do it
Application.ScreenUpdating = False
For LC = 1 To pCount
ActiveSheet.Range("B" & Rows.Count) _
.End(xlUp).Offset(1, 0) = ActiveSheet.Range("B2")
Next
End Sub

Sub PasteUsingRange()
Dim pCount As Integer
Dim pRange As Range
Dim pRangeAddress As String

pCount = ActiveSheet.Range("A2")
If pCount < 1 Then
Exit Sub
End If

Application.ScreenUpdating = False
'another way, effective for large counts in A2
pRangeAddress = _
ActiveSheet.Range("B" & Rows.Count) _
.End(xlUp).Offset(1, 0).Address
pRangeAddress = pRangeAddress & ":" & _
Range(pRangeAddress).Offset(pCount, 0).Address
Set pRange = ActiveSheet.Range(pRangeAddress)
pRange.Value = ActiveSheet.Range("B2").Value
Set pRange = Nothing
End Sub

"hnyb1" wrote:

Hi! Can you help me with a macro to copy and paste a cell x number of times
dependent on a number that is entered into a cell.

Ex: If A2 = 2 then cell B2 is copied and pasted into the next 2 empty cells
in column C. But then if A2 = 5, B2 is copied and pasted into the next 5
empty cells in column C.

As always, any help is much appreciated! Oh yeah, I'm working in Excel 2003.

Thanks,
Holly