How to input a row variable pertaining to all macros
Category: [Excel] Demo Available
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
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy range from variable named workbook to current workbook |
| How to import data in a specific sheet from another workbook (sheet number must be variable) |
| How to insert VLOOKUP into cell with variable array |
| How to pull data from a worksheet where variables are met |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.