Still no solution after 1 Year - Please Help

[ggtlewis]

So the short version is: I have a tool that makes signs for items. The paper it prints on is divided into 8 signs. (8.5x11 Sheet) I have tried every different option to get it to print properly and I cannot.
Each page should have 8 signs. I keep getting overlap or width issues. ("A1:BM99") should be on one page. ("A100:BM199") should be on the next. and etc.

I have this to use for testing:
Sub gl()
Dim m8e80dd3f25b4f076decbb7cfa16adadd As Double
Worksheets("Print Settings").Select
'w4a97505eeb6e22ac949f1eebf8767f51 = Worksheets("Settings").Range("B9").Value
'c6137e8c784e43b9ff3855ad15c4e58ab = Worksheets("Settings").Range("B10").Value
'm8e80dd3f25b4f076decbb7cfa16adadd = Worksheets("Settings").Range("B11").Value
'w04eea45e14e9e49346a3a3cb18923b67 = Worksheets("Settings").Range("B12").Value


w4a97505eeb6e22ac949f1eebf8767f51 = 0.88
c6137e8c784e43b9ff3855ad15c4e58ab = 2.5
m8e80dd3f25b4f076decbb7cfa16adadd = 7.7
w04eea45e14e9e49346a3a3cb18923b67 = 0.5


Worksheets("Print Settings").Rows("1:999").RowHeight = m8e80dd3f25b4f076decbb7cfa16adadd
Worksheets("Print Settings").Columns("A:AE").ColumnWidth = w4a97505eeb6e22ac949f1eebf8767f51
Worksheets("Print Settings").Columns("AF").ColumnWidth = c6137e8c784e43b9ff3855ad15c4e58ab
Worksheets("Print Settings").Columns("AG").ColumnWidth = w04eea45e14e9e49346a3a3cb18923b67
Worksheets("Print Settings").Columns("AH:BL").ColumnWidth = w4a97505eeb6e22ac949f1eebf8767f51
Worksheets("Print Settings").Columns("BM").ColumnWidth = c6137e8c784e43b9ff3855ad15c4e58ab

With Worksheets("Print Settings").PageSetup
.LeftMargin = Application.InchesToPoints(0.01)
.RightMargin = Application.InchesToPoints(0.01)
.TopMargin = Application.InchesToPoints(0.01)
.BottomMargin = Application.InchesToPoints(0.01)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
End With
Set ws = Worksheets("Print Settings")
ws.PageSetup.printArea = "$A$1:$BM$99"


Set Rng = ws.Range("A1:AF24")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("AH1:bm24")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("A26:AF49")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("AH26:bm49")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("A51:AF74")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("AH51:bm74")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("A76:AF99")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick

Set Rng = ws.Range("AH76:bm99")
Rng.BorderAround LineStyle:=xlContinuous, Weight:=xlThick


End Sub





Someone please help as I'm losing my mind.

 

[jolivanes]

To me that sounds more like the long version!!!!!!!
Do you want to insert 8 pictures onto a single sheet?
If so, 2 wide by 4 long?
How are you determining which pictures? By selecting all of the pictures in one go?

 

[ggtlewis]

LOL. it is a much more robust tool. for simplicity sake: it makes price tags that print on special paper. (8.5x11) But has red and white lines in specific places.

but yes. 2 wide by 4 down.

 

[jolivanes]

So what are the individual pictures?
We, at least I do, need more of an explanation.
It looks like you have 24 different pictures of all different sizes bundled into two pictures, 12 pictures per "print item".
1) "Joes Sports" is one picture, 0 Left, 0 Top, 3 Rows in Height and 12 Columns in Width
2) "No Returns. All Sales Final" is one picture, 0 Left. Top is the Top of the 4th Row, 2 Rows in Height and 12 Columns in Width
Keep on going for the rest of the pictures. You will only need this for one combination as the rest will a repeat with offsets.
You also have a space showing. No mention of any spacing in the subsequent combinations.
Anyway, to add a particular picture to a sheet, one need
Picture Full name ("C:\Pictures\Joes Sport.jpg"), the Left amount where that picture needs to be placed from the sheet left edge, the amount from the top of the sheet where the placement needs to be, the width as well as the height of that picture.
Have a peek here.

Shapes.AddPicture method (Excel)

Office VBA reference topic

learn.microsoft.com

I would suggest to have all the relevant picture names in a row from left to right if they are all in the same folder so you can loop through them..

Re: "has red and white lines in specific places" That is difficult to see on a BW picture as your attachment.
No mention of where these lines ought to be.

Once when all the information is in place, a different sheet maybe, it should work very well for you.

 

[ggtlewis]

Hi,

I'm dealing with cells and merged cells rather than pictures. So Joes Sports is just text in a merged cell. There is a space between each "print item" (Column AG)

There are supposed to be 8 signs(Print ITems) per page. one page should be 99 rows tall.

Cannot get the printing to work.

 

[jolivanes]

Show us your print setup please. I assume that that is with a macro
Is this not a picture?A17:F22

With ws.PageSetup
    .PrintArea = ws.Range("A1:BM99").Address
    .Zoom = False
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

Do you have spaces vertically? Certain Rows

 

[Alex Blakenburg]

I think Jolivanes knows much more about this than I do but I was wondering if using a separate Print Sheet with your labels as Linked Pictures would make it easier to work with.
Perhaps each label separately so you can move them around.
• Highlight a Label range
• Copy
• on a new sheet Click the Paste Drop down and select Linked Picture (bottom right icon)

 

[jolivanes]

Hi Alex
Adding pictures would be preferred as far as I can see and I certainly would eliminate the merged areas.
Like mentioned in Post #4, if it is all set up properly it will be a "fluitje van een cent", in other words, pretty simple.
I misunderstood the request I think. It seems that the OP is mostly concerned about the printing problem.
If that is the case:
I assume there will be multiple pages involved. Change references if and where required.
It is set to saving as pdf files but you can change it once the result is checked and found to be what you want.
It saves the pdf files in the same folder where this excel file has been saved.

Sub Maybe_Like_So()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr Step 99
    With ActiveSheet.PageSetup
        .PrintArea = ActiveSheet.Range("A" & i & ":BM" & i + 98).Address
        .Zoom = False
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "From Row " & i & " To Row " & i + 98 & ".pdf"
'<----- Change the above line to "ActiveSheet.Printout" when happy that all works as required
Next i
End Sub

 

[jolivanes]

Did you get the printing to work?

 

[ggtlewis]

Hi Alex
Adding pictures would be preferred as far as I can see and I certainly would eliminate the merged areas.
Like mentioned in Post #4, if it is all set up properly it will be a "fluitje van een cent", in other words, pretty simple.
I misunderstood the request I think. It seems that the OP is mostly concerned about the printing problem.
If that is the case:
I assume there will be multiple pages involved. Change references if and where required.
It is set to saving as pdf files but you can change it once the result is checked and found to be what you want.
It saves the pdf files in the same folder where this excel file has been saved.

Sub Maybe_Like_So()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lr Step 99
    With ActiveSheet.PageSetup
        .PrintArea = ActiveSheet.Range("A" & i & ":BM" & i + 98).Address
        .Zoom = False
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & "From Row " & i & " To Row " & i + 98 & ".pdf"
'<----- Change the above line to "ActiveSheet.Printout" when happy that all works as required
Next i
End Sub

AMAZING. I feel like an idiot for overlooking the simplicity of this. Thank you!!!