Macro to paste x number of times

[hnyb1]

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

 

[JLatham]

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]

Thanks so much! I used the first "straight forward" method, as there
shouldn't be any more than 4 repetitions. Thanks again!

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

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

 

[hnyb1]

I'm sorry to bug again, but is there any reason why this macro won't work
when i set up a Private Sub to execute when a cell value changes? I've tried
a couple of different codes, but here's the latest. Nothing happens when I
update D11, but if I just run the sub it works perfectly?

Private Sub Worksheet_Calculate()
Static OldValD11 As Variant

If Me.Range("D11").Value = OldValD11 Then
'do nothing, it didn't change
Else
OldValD11 = Me.Range("d11").Value
'then go off and do what you want
Application.EnableEvents = False
Application.Run "PasteItWithLoop"
Application.EnableEvents = True
End If
End Sub

Thanks,
Holly

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

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

 

[hnyb1]

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.

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

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