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