Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to paste x number of times
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to paste x number of times
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to paste x number of times
Thanks so much! I used the first "straight forward" method, as there
shouldn't be any more than 4 repetitions. Thanks again! "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to paste x number of times
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 "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to paste x number of times
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and paste 2900 times macro | Excel Discussion (Misc queries) | |||
Copy & paste block of text number of times equal to value in cell | Excel Worksheet Functions | |||
Macro - copy a range and paste it in a new sheet 12 times | Excel Worksheet Functions | |||
Loop Macro a variable number of times | Excel Discussion (Misc queries) | |||
Running a Macro a number of times | Excel Discussion (Misc queries) |