Input a Row variable pertaining to all macros

rinconpaul · Mar 23rd 2018

In the following code, there is one variable that needs to be input each time I use it and that's the last Row number. In the example code it's 10 for all macros. I want 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

Display More

**pike** · Mar 23rd 2018

Hello,
Do you mean like,,,

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
End Sub


Sub Pastytreat(vRow As Long)
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 As Long)
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 As Long)
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 As Long)
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

Display More

rinconpaul · Mar 24th 2018

Thanks pike for your help. Getting an error and no input box? I've pasted your code into a working example so you can play with it. Sheet2(2) is how my code runs (Module 1) I've put your code in module 2. See how you go, just a tweak or two hopefully? [ATTACH]n1201412[/ATTACH]

**pike** · Mar 24th 2018

Ive tested this one , the first post was just written out

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

Display More

rinconpaul · Mar 24th 2018

Perfection Pike, your new name pike! Well done and many thanks. :cheers:

**pike** · Mar 24th 2018

You're welcome

Input a Row variable pertaining to all macros

Participate now!

Share