How to Input a Row variable pertaining to all macros

How to input a row variable pertaining to all macros

Requirement:

In the following code, there is one variable that needs to be input each time the user use it and that's the last Row number. In the example code it's 10 for all macros. The user wants to be able to input "xx" prior to running.

Code:

Sub Pastytreat()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")

copySheet.Range("A2:D10").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Sub LoopMacroPastytreat()
Dim x As Integer
For x = 1 To 3
Call Pastytreat
Next x
Call CandPFirst
Call CandPSecond
Call CandPThird
End Sub


Sub CandPFirst()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("E2:J10").Copy Worksheets("Sheet2").Range("E" & n)

End Sub

Sub CandPSecond()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("K2:P10").Copy Worksheets("Sheet2").Range("E" & n)
End Sub

Sub CandPThird()
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("Q2:V10").Copy Worksheets("Sheet2").Range("E" & n)
End Sub

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1201402-input-a-row-variable-pertaining-to-all-macros

Solution:

Code:

Option Explicit
Sub LoopMacroPastytreat()
    Dim x As Integer, vRow As Variant
    vRow = InputBox("Prompt:")
    If Not IsNumeric(vRow) Or Not Val(vRow) = Int(vRow) Then Exit Sub

For x = 1 To 3
Call Pastytreat(vRow)
Next x
Call CandPFirst(vRow)
Call CandPSecond(vRow)
Call CandPThird(vRow)
End Sub


Sub Pastytreat(vRow)
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Sheet1")
Set pasteSheet = Worksheets("Sheet2")

copySheet.Range("A2:D" & vRow).Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Sub CandPFirst(vRow)
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("E2:J" & vRow).Copy Worksheets("Sheet2").Range("E" & n)

End Sub

Sub CandPSecond(vRow)
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("K2:P" & vRow).Copy Worksheets("Sheet2").Range("E" & n)
End Sub

Sub CandPThird(vRow)
Dim n As Long
n = Worksheets("Sheet2").Cells(Rows.Count, "E").End(xlUp).Row + 1
Worksheets("Sheet1").Range("Q2:V" & vRow).Copy Worksheets("Sheet2").Range("E" & n)
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by pike.