Paste Range X times based on user input

Sundae

New Member
Joined
Jul 25, 2011
Messages
44
Hi All,

I hope you can assist. I am trying to paste a range the number of times input by a user. I want the pasted range to be above the range titled Grand Totals (resize).

I have the following code:

Option Explicit<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p> </o:p>
Sub insert_Rows()<o:p></o:p>
Dim lngRows As Long, lngNextRow As Integer<o:p></o:p>
<o:p> </o:p>
On Error GoTo Finish<o:p></o:p>
Application.DisplayAlerts = False<o:p></o:p>
Application.Calculation = xlCalculationManual<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
With Worksheets("Payroll")<o:p></o:p>
.Unprotect<o:p></o:p>
lngRows = CLng(InputBox("How many rows do you wish to insert?"))<o:p></o:p>
lngNextRow = .Cells(Rows.Count, "A").End(xlUp).Row<o:p></o:p>
.Range("Top_5").Copy<o:p></o:p>
.Range("GrandTotals").Resize(Rows.Count, 1).Insert Shift:=xlDown<o:p></o:p>
<o:p></o:p>
<o:p> </o:p>
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True<o:p></o:p>
<o:p></o:p>
Finish:<o:p></o:p>
If Err.Number <> 0 Then MsgBox Prompt:="Please ensure you only enter numeric values!"<o:p></o:p>
<o:p> </o:p>
End With<o:p></o:p>
<o:p> </o:p>
Application.CutCopyMode = False<o:p></o:p>
Application.ScreenUpdating = True<o:p></o:p>
Application.Calculation = xlCalculationAutomatic<o:p></o:p>
Application.DisplayAlerts = True<o:p></o:p>
End Sub<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p></o:p>
<o:p>I either get the Error Message or no matter what number I input, the range only pastes one time.</o:p>
<o:p></o:p>
<o:p>I hope someone can assist.</o:p>
<o:p></o:p>
<o:p>Thank you!!</o:p>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top