Adapting Paste for A4 loop

[Infine]

Hello,

I have create a template which is adapted for an A4 paper area. I want to put it below each other on an Excel sheet and then print to PDF. It worked for MONTHS but all suddenly the area for A4 (settings) changed and my Range numbers were not adapting the A4 paper.

So basically:

..................
.I PASTE HERE
.
.
..................

and then from nowhere after several months the pasting is here:

..................
.
.I PASTE HERE
.
..................

and when this happens, the loop gets 1-2 steps down and the template I made is mixing on the A4 area.

Does anyone know why the area of the A4 changing? Nobody has changed anything with this Excel file nor script.

See my code below for how I paste it (I have updated it to a more simpler and math way, but I am still curious why it changed)

If pdfcount = 2 Then
Worksheets("PDF").Range("A52").Select
ElseIf pdfcount = 3 Then
Worksheets("PDF").Range("A100").Select
ElseIf pdfcount = 4 Then
Worksheets("PDF").Range("A148").Select
ElseIf pdfcount = 5 Then
Worksheets("PDF").Range("A196").Select
ElseIf pdfcount = 6 Then
Worksheets("PDF").Range("A244").Select
ElseIf pdfcount = 7 Then
Worksheets("PDF").Range("A292").Select
ElseIf pdfcount = 8 Then
Worksheets("PDF").Range("A340").Select
ElseIf pdfcount = 9 Then
Worksheets("PDF").Range("A388").Select
ElseIf pdfcount = 10 Then
'......... And so on up to 20

End If

 

[Joe4]

I think there are a number of things that could cause that to change, such as:
- a Windows or Office update
- a driver update
- the use of a new printer

By thw way, there is a cleaner way to write your code. Instead of all the ElseIf statements, you can use a Case statement.
See: MS Excel: How to use the CASE Statement (VBA)

 

[My Aswer Is This]

Yes I looked at this code earlier and thought Case would be easier.
But not sure what the end result of you script is suppose to do.

Pdf Count ?
I just use the Range("A1") to enter pdfcount

Sub Pdf()
'Modified  4/15/2021  7:55:18 AM  EDT
Dim Pdfcount As Long
Pdfcount = Range("A1").Value
With Worksheets("PDF")
    Select Case Pdfcount
        Case 2: .Cells(52, 1).Select
        Case 3: .Cells(100, 1).Select
        Case 4: .Cells(148, 1).Select
        Case 5: .Cells(196, 1).Select
        Case 6: .Cells(244, 1).Select
        Case 7: .Cells(292, 1).Select
        Case 8: .Cells(340, 1).Select
        Case 9: .Cells(388, 1).Select
    End Select
End With
End Sub

 

[Alex Blakenburg]

The code you are showing is not doing the pasting. Where is the code that is doing the copy & pasting ?
Has the source from which you are pasting changed ie if you compare a current version of the source to an older version when it seemed to be working, are there more blank rows at the top now as opposed to before.

Just as aside, you referred to the maths of the spacing and based on the pattern you could do this.
Technically you don't need the select at all but I wasn't sure about the whether the 20 upper limit had any significance.

Sub Pdf()
    Dim Pdfcount As Long
    Dim pgSize As Long
    Dim pgRowNo As Long
    
    pgSize = 48
    
    Pdfcount = Range("A1").Value
    pgRowNo = (Pdfcount - 1) * pgSize + 4
    With Worksheets("PDF")
        Select Case Pdfcount
            Case 2 To 20: .Cells(pgRowNo, 1).Select
            Case Else       ' do nothing
        End Select
    End With
    
End Sub

 

[Infine]

Hello,

So I also was thinking there could be an Update of Office or the backing template of where you get your basis from has been update but at the same time the Size of the Template is always the same no matter what. The only thing that I could see that has been changed was the A4 printing area. So instead of number A52 it was all the suddenly at A49. Etc. For no reason.

I replaced my If statements with this code:

Range(Cells(47 * (pdfcount - 1), 1), Cells(47 * (pdfcount - 1), 1)).Select
    ActiveSheet.PasteSpecial xlPasteColumnWidths ' THIS IS HOW I PASTED BEFORE TOO.
    ActiveSheet.PasteSpecial xlPasteAllMergingConditionalFormats
    ActiveSheet.Paste

pdfcount = a loop depending how many rows I have on the basis.

Dim pdfcount As Integer
pdfcount = 1

I am suppose to do this:


Rows are 3 on my basis, then:

..................
.I PASTE HERE page 1
.
.
..................
..................
.I PASTE HERE page 2
.
.
..................
..................
.I PASTE HERE page 3
.
.
..................


(This is a 3 paged PDF with different information from my basis). So basically the code above works but I am not certain if it will last as my other did work also for months then all suddenly it stopped working because the A4 Printing area was different.


Any more suggestions what it could be?

 

[Joe4]

I have seen new printers (with new print drivers) change things, as sometimes their assumed/default margins change.
I could be wrong, but I think even though you are not printing to physical paper, I think it may still use those default printer settings.

Quite frankly, I never liked trying to guess how big things need to be when printing, so typically use the "Fit to 1 page wide by 9999 pages long".

 

[Infine]

Okey this sounds logical. I might be this and not the code or Excel sheet changing A4 printing area from nowhere without updating/saving the file from the time it worked fine.


I guess this is a manual handled thing if this happens again to adapt the size of the A4.

However, I just want to mention that when saving the PDF file I do not use the printer Save as PDF. It's a code just automatically saving the Sheet as a PDF. Does this matter?

 

[Joe4]

However, I just want to mention that when saving the PDF file I do not use the printer Save as PDF. It's a code just automatically saving the Sheet as a PDF. Does this matter?

Unfortunately, I do not know the answer to that.

 

[Alex Blakenburg]

Okey this sounds logical. I might be this and not the code or Excel sheet changing A4 printing area from nowhere without updating/saving the file from the time it worked fine.


I guess this is a manual handled thing if this happens again to adapt the size of the A4.

However, I just want to mention that when saving the PDF file I do not use the printer Save as PDF. It's a code just automatically saving the Sheet as a PDF. Does this matter?

I assume that means you are using "Workbook.ExportAsFixedFormat". This does seem to use all the Excel Page Setup functionality.
For Example have you considered inserting page breaks rather than copying the data into specific locations.

Also it may be worth considering hard coding the Margins and any other page setup parameters that are going to impact your printing alignment.

 

[Infine]

I use this to save the PDF

'Create and assign variables
Dim saveLocation As String
saveLocation = "Y:\Backoffice\2. Fund Administration\Fondflytt\UTFLYTT\" & varDepot & ".STF.pdf"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=saveLocation

The code at this moment works fine now but I still do not understand why it stopped adapting the A4 printing area from nowhere.


I also wonder, if this code above can stop from overwriting. So basically if the filename already exists it should give me a msg box "Do you want to overwrite it? YES/NO" How do I do that. At the moment it just overwrites the PDF file if it has the same name