Sub Test()
Dim ws As Worksheet
Dim sh As Worksheet
Dim i As Integer
Set ws = ThisWorkbook.Worksheets("INF")
Set sh = ThisWorkbook.Worksheets("SP")
For i = 2 To ws.Cells(Rows.Count, "X").End(xlUp).Row
PrintSeatSecret ("X" & i)
sh.PrintPreview
Next i
End Sub
Sub PrintSeatSecret(s As String)
Const blk = 40
Const grp = 4
Const off = 3
Dim wshS As Worksheet
Dim wshR As Worksheet
Dim hdrs As Variant
Dim arr As Variant
Dim i As Long
Dim lb As Long
Dim ub As Long
Dim j As Long
Dim r As Long
Dim c As Long
Application.ScreenUpdating = False
hdrs = Array("Seat", "Secret")
Set wshS = ThisWorkbook.Worksheets("INF")
Set wshR = ThisWorkbook.Worksheets("SP")
wshR.UsedRange.ClearContents
wshR.UsedRange.Borders.Value = 0
wshR.ResetAllPageBreaks
For i = 1 To 2
r = 1
c = i
arr = Split(wshS.Range(s).Offset(0, i - 1).Value, " - ")
lb = arr(0)
ub = arr(1)
For j = lb To ub
If (j - lb) Mod blk * grp = 0 Then
c = i
wshR.Cells(r, c).Value = hdrs(i - 1)
If r > 1 Then wshR.HPageBreaks.Add wshR.Cells(r, c)
r = r + 1
ElseIf (j - lb) Mod blk = 0 Then
r = r - blk - 1
c = c + off
wshR.Cells(r, c).Value = hdrs(i - 1)
r = r + 1
End If
wshR.Cells(r, c).Value = j
r = r + 1
Next j
Next i
For c = 1 To off * grp Step off
With wshR.Cells(1, c).CurrentRegion
.HorizontalAlignment = xlHAlignCenter
.Borders.LineStyle = xlContinuous
End With
Next c
Application.ScreenUpdating = True
End Sub
But I need now to deal with the | separator too .. I mean to split by | separator then the same steps will be the same
NB may be there will be more than two | (pipelines)
You do not have the required permissions to view the files attached to this post.
Sub Test()
Dim ws As Worksheet
Dim sh As Worksheet
Dim i As Integer
Set ws = ThisWorkbook.Worksheets("INF")
Set sh = ThisWorkbook.Worksheets("SP")
For i = 2 To ws.Cells(Rows.Count, "X").End(xlUp).Row
PrintSeatSecret ("X" & i)
sh.PrintPreview
Next i
End Sub
Sub PrintSeatSecret(s As String)
Const blk = 40
Const grp = 4
Const off = 3
Dim wshS As Worksheet
Dim wshR As Worksheet
Dim hdrs As Variant
Dim arr As Variant
Dim i As Long
Dim lb As Long
Dim ub As Long
Dim j As Long
Dim r As Long
Dim c As Long
Application.ScreenUpdating = False
hdrs = Array("Seat", "Secret")
Set wshS = ThisWorkbook.Worksheets("INF")
Set wshR = ThisWorkbook.Worksheets("SP")
wshR.UsedRange.ClearContents
wshR.UsedRange.Borders.Value = 0
wshR.ResetAllPageBreaks
For i = 1 To 2
r = 1
c = i
Dim e
For Each e In Split(wshS.Range(s).Offset(0, i - 1).Value, " | ")
arr = Split(e, " - ")
lb = arr(0)
If UBound(arr) = 0 Then
ub = arr(0)
Else
ub = arr(1)
End If
For j = lb To ub
If (j - lb) Mod blk * grp = 0 Then
c = i
wshR.Cells(r, c).Value = hdrs(i - 1)
If r > 1 Then wshR.HPageBreaks.Add wshR.Cells(r, c)
r = r + 1
ElseIf (j - lb) Mod blk = 0 Then
r = r - blk - 1
c = c + off
wshR.Cells(r, c).Value = hdrs(i - 1)
r = r + 1
End If
wshR.Cells(r, c).Value = j
r = r + 1
Next j
Next e
Next i
For c = 1 To off * grp Step off
With wshR.Cells(1, c).CurrentRegion
.HorizontalAlignment = xlHAlignCenter
.Borders.LineStyle = xlContinuous
End With
Next c
Application.ScreenUpdating = True
End Sub
But I got scrambled results as for the structure of the output pages
It seems the issue is difficult in that way
Can I explain the issue in another way ..?
I have Data sheet with three columns .. The column of names for example has the names (A - B - C - D) and the data is not sorted
In the worksheet named "SP" I need to have the columns "Seat" and "Secret" in groups (A-B & D-E & G-H & J-K) for each name separately so that I can print out the data related to each name ..
Here's a sample
You do not have the required permissions to view the files attached to this post.
I need the results exactly as in this topic and the groups will be according to each name ..
The final output is to print each person's seats and secrets separately (may be one page or more) and the data is grouped in four blocks ..
Here's the expected output for the person E (but I don't need a new worksheet for each person .. Just populate the data and print preview)
so this is an example of the output
You do not have the required permissions to view the files attached to this post.
To be more specific I need to deal with the sample in the post #6 (the last attachment)
If you filter column of names and select the person E >> you would see the data related to that person and the data needed to be copied is column of Seat & Secret but the copy is not in one column but in group blocks (that's the main issue ..)
I don't know if we can make use of the first code or not but the idea is embedded there in the first code
Thank you very much for your interest
Have a look at the sheet I created which is named 'E' and you will get my point (the data in four blocks for each page 40 rows ..)
Have a nice time and happy dreams
I can't sort the data as each person would work on each block separately
I think if we could filter the person, we can get the data in the order that I need then with a helper column in the target sheet the data could be grouped in 40 rows for each block and so on till the page is complete ..
I am just thinking in a loud voice but I can't get the whole idea
Can you please have a look at this file and test the button "Print" .. this works fine if the separator in the table in 'INF' has only dash ..
What I aim to is to see the final output of each person ..
I put only two person so you will find out the desired output
You do not have the required permissions to view the files attached to this post.
Yes that's exactly what I posted (the output is the same and this output is the desired)
What I need now is to change the approach of input to take the data from the columns directly without this helper table as the data is scrambled and I can't sort it
I have posted the link here too https://www.excelforum.com/excel-progra ... locks.html" onclick="window.open(this.href);return false;
And here's the file that has Data in it (I have many people but I let only two)
You do not have the required permissions to view the files attached to this post.
To make it clearer I will make the steps of the manual steps I do to get the desired output
1- Filter the Names in Data sheet by "A" for example
2- Create new sheet
3- Copy the filtered data to the new sheet
4- Delete Column B (the names) and keep only Seat and Secret columns
5- Copy the first 40 rows (row 1 to 40) to "SP" sheet and paste into A1
6- Copy the rows (41 to 80) and paste into D1
7- Copy the rows (81 to 120) and paste into G1
8- Copy the rows (121 to 160) and paste into J1
9- Copy the rows (161 to 200) and paste into A41
And so on ...
