Macro to Adjust Print Area

[luis_marques]

Hi Folks,

I use the following macro, which is actionable using a button in the sheet 1, to print a hidden sheet 2. The report is printed in PDF extension and the file must be opened.

The problem is: when PDF file is generated the print area between the pages of the report in the sheet 2 is variable in different computers.

I need to generate each page printed with 57 rows in the PDF file. Is possible to add such routine in this macro below?

Thanks in advance.

Sub GerarPDF2()
Dim SVInput As String
Dim Nome As String


Worksheets("Sheet2").Visible = True


    Sheets("Sheet2").Select
Nome = InputBox("Digite o nome para o relatório", "Gerar Relatório PDF")

On Error Resume Next
SVInput = ThisWorkbook.Path & Application.PathSeparator & Nome & ".pdf"



With Worksheets("Sheet2")
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=SVInput, _
OpenAfterPublish:=True
      
End With
  
Sheets("Sheet2").Visible = False

End Sub

 

[Hui]

I think you want

With Worksheets("Sheet2").Range("A1:M57") 'Adjust range to suit
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=SVInput, _
OpenAfterPublish:=True
      
End With

 

[khelgadi]

Dear Hui/ others ,
Since I've a question, related to similar problem, I'm continuing here....
I've got fixed ( Fixed no of rows and columns) sheets numbered from 1- 10. depending on the selection of product, I want to print the specific pages like No. 2,3 and 7 for product 1 etc.
Can I do it without macro? if yes how?
With Macro how?
Thanks...

 

[Hui]

Khelgadi

Related yes, there both about printing, as are 2000 other posts
Please start a new post

Hui...

 

[luis_marques]

Dear Hui,

This formula set a range for the first page, but second page ( 58 row until 114 row) and so on the pdf generated doesnt appear anything.

Is possible a adjustment?

Regards,

 

[luis_marques]

Hi, anyone could help me?

 

[Hui]

Can you please post your file ?

 

[luis_marques]

Dear Hui,

Translating the sheets names:
-Sheet1 = Diagnóstico
-Sheet2= Relatório

Thanks in advance,

Luis

 

[Hui]

I think I can do this without changing the macro!

In the worksheet relatorio go to the position in Column A where you want a page break
Goto the Excel, Page Layout Tab
Breaks
Insert Page Break
Goto the next location
repeat the above

 

[luis_marques]

We tried this in the original file. But when i opened in my computer it moved the page break to another row
-Computer 1 - Original file: page 1 - break in the 57 row
-Computer 2 (mine) - Original file: page 1 - break in the 55 row

That is why i thought a macro could adjust it automatically. What do you suggest?

Best regards,

Luis

 

[Hui]

Goto the Page Layout tab
In the Scale to Fit pane, change Page Height from Automatic to 7
Now manually adjust the Page breaks by dragging the Horizontal Blue lines between pages to the correct locations

 

[luis_marques]

Thanks to your solution. It solved!!!
Regards!!!

 

[Hui]