Oct 23 2020 07:52 AM
I'm printing some price tags from an excel spreadsheet. On the spreadsheet each row has store code, product and the sell price. In column A is a code for a store, in column B is the product and in Column C is the price. My question is this, when the store code changes from 001 to 002 for example, I would like excel to insert blank rows so I can separate each stores products. That's pretty straight forward, but the problem I have is that I'm printing 6 tickets per sheet, and the number of products per store differ so I want excel to look at how many rows there are per store (ie the code in column A), then work out how many blank inserted rows are needed to bring that up to a multiple of 6. That way, no sheet will have two stores tickets on the same sheet. For example, if there are 58 rows of products for store code 001, then Excel will know to insert 2 blank rows to bring that upto 60 rows in total. It will mean that the products for store 002 will start on the next printed sheet.
Oct 23 2020 08:44 AM
Here is an example as an approach, where you can select how many lines you would like to insert each time.
Passwort: Floatyman
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Oct 26 2020 01:19 AM
@NikolinoDE Thanks. How did you do this? Was it using Macros and if so how do I do the same? Please explain
regards,
Oct 26 2020 01:31 AM
Here is the code...is in German vba commands.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim zeilenanzahl As Long
Dim x As Integer
Dim i As Integer
x = 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Target.Column = 1 And Target.Row > 2 Then
Worksheets("2021").Unprotect Password:="Floatyman"
On Error GoTo Notausgang
zeilenanzahl = InputBox("How many rows?", "Insert rows")
With ActiveCell
For i = 1 To zeilenanzahl
If ActiveCell = "" Then
.EntireRow.Copy
.EntireRow.Offset(x, 0).Insert shift:=xlDown
.EntireRow.Offset(x, 0).PasteSpecial
ActiveCell = 1
.EntireRow.Offset(x, 0).SpecialCells(xlCellTypeConstants).ClearContents
Else
.EntireRow.Copy
.EntireRow.Offset(x, 0).Insert shift:=xlDown
.EntireRow.Offset(x, 0).PasteSpecial
.EntireRow.Offset(x, 0).SpecialCells(xlCellTypeConstants).ClearContents
ActiveCell.Offset(-x, 0).Select
End If
x = x + 1
Next i
.Offset(1, 0).Select
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Sheets("2021").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, _
AllowInsertingColumns:=True, AllowSorting:=True, AllowFiltering:=True, _
Password:="Floatyman"
Notausgang:
Cancel = True
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Sheets("2021").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, _
AllowInsertingColumns:=True, AllowSorting:=True, AllowFiltering:=True, _
Password:="Floatyman"
End If
End Sub
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Nikolino
I know I don't know anything (Socrates)
Oct 26 2020 03:32 AM
Oct 26 2020 04:01 AM
Oct 26 2020 04:08 AM
Oct 26 2020 05:29 AM
@NikolinoDE Thank you for your help. I paste the code in and Excel asks me to name and create a Macro, which I do. Then Run, but see no difference so I must be doing something wrong.
Attached is a file. In column A, when the code changes I would like 10 blank rows (or ideally a number rounded to up a multiple of 6) to be inserted to separate these codes into groups on the sheet.
regards
Oct 26 2020 06:38 AM
Ready to use! :)
Simply left double-click on column A (from A2) with the mouse and a user form will appear. There you enter how many lines you want and they will appear.
Tested and it works for me without problems.
I wish you continued success with Excel (the coolest invention since chocola... uh ... Microsoft! :)))
And keep asking here - I just taught myself Excel with the help of this forum :).
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here