An Quala
Board Regular
- Joined
- Mar 21, 2022
- Messages
- 146
- Office Version
- 2021
- Platform
- Windows
Hello, This is a basic template copy paste code, but I want to change the fixed range of this code to the dynamic range, where Cells(2,"A") is fixed always, but Cells(5,"Z") will always change depending on the template provided, so it should take the last column and the last row instead of fixed 5 and Z, also row #1 is fixed and additionally I want an empty row after each copy of the template, thank you.
VBA Code:
Sub CopyData()
Dim lRow As Long
Dim Num As Integer
Num = InputBox("How many Times")
lRow = 1
Do While Num > 0
Range(Cells(2, "A"), Cells(5, "Z")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + 1, "Z")).Select
Selection.Insert Shift:=xlDown
lRow = lRow + 4
Num = Num - 1
Loop
Application.CutCopyMode = False
Range("A1").Select
End Sub