Automatic list creating - start at the beginning of new page (macro)

lastknownuser

New Member
Joined
Feb 1, 2022
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Started very recently with VBA so I'm not an expert but I am familiar with programming in other languages.
I want to create a macro to write a number of names and it would create list (by copying template on the side), and the problem is after row 50, 100,... How can I define that on new page it starts at 51 as first row? I attached the pictures. So the problem is splitting of copied range as can be seen in picture, I don't want that

Any help would be welcome, especially if there are more examples of this kind of template creating for print, thank you
Current short code:
VBA Code:
Sub test()

Range("J1:R5").Select
Selection.Copy
Dim ink As Integer
Dim i As Integer
i = 1
ink = InputBox("Enter number of names")
ink2 = ink * 6
For i = 1 To ink2 Step 6
Worksheets("sheet1").Cells(i, 1).Select
ActiveSheet.Paste

Next i

End Sub

test123.png
 
Tell me in words what you're trying to do. Your copying cells and then saying activesheet.paste

Where on the active sheet are you wanting to paste it.
We should be using sheet names like what is the name of the sheet where you're copying from and what is the name of the sheet you're wanting to paste to.
I figured it out, the problem was "50 < i < 100"...as I said I started with VBA few days ago
I wanted to copy range "J1:R5" to cells (i, 1) where i is defined by inputbox, and the problem was how to avoid splitting range "J1:R5" between two pages
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I figured it out, the problem was "50 < i < 100"...as I said I started with VBA few days ago
I wanted to copy range "J1:R5" to cells (i, 1) where i is defined by inputbox, and the problem was how to avoid splitting range "J1:R5" between two pages
Please show me the new code your using. I'm always curious after I start trying to help.
 
Upvote 0
Please show me the new code your using. I'm always curious after I start trying to help.
This one works like I wanted

VBA Code:
Public i As Integer

Sub test()

Range("J1:R5").Select
Selection.Copy
Dim ink As Integer
i = 1
ink = InputBox("Enter number of names")
ink2 = ink * 6

Do While i < ink2

If i < 45 Then
Worksheets("sheet1").Cells(i, 1).Select
ActiveSheet.Paste
ElseIf 47 < i And i < 51 Then
i = 51
Worksheets("sheet1").Cells(i, 1).Select
ActiveSheet.Paste
ElseIf 51 < i Then
Worksheets("sheet1").Cells(i, 1).Select
ActiveSheet.Paste
End If

i = i + 6

Loop

End Sub
 
Upvote 0

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top