Printing a selection to PDF using vba

[Luis_B]

Hi Guys

I need help printing to PDF a set of data that goes from column B to J. The data start on B1 and goes down to J?. The data changes every time I run this report so the last row is always different (the report could have 4 rows or it could have 1000 rows, I never know). The code below is what I'm suing to print to PDF but it prints the entire active sheet. I only want to print the data on the sheet and leave everything blank after the data is over. Could someone please help me with this. Thank you

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="CHP.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[GWteB]

How about ...

Dim Rng As Range
With ActiveSheet
    Set Rng = .Range("B1:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:="CHP.pdf", Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With

 

[Luis_B]

How about ...

Dim Rng As Range
With ActiveSheet
    Set Rng = .Range("B1:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:="CHP.pdf", Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With

Hi GWteB, thank you so much for your help. This codes takes away all the blanks and/or active sheet after the data but now I have another problem...

I have another code that brings data from another tab (data sheet) to the sheet (sheet2) that I'm printing using the code you helped me. Before using the code you helped me with, I was getting 198 rows but when I use your code I only get 16 rows... With that said, If I filter the "data sheet" by, let's say ABB, then everything works. Do you happened to know why?

Below is the code that I'm suing to bring the data from the "data" sheet to sheet2

Sub CommandButton1_Click()

SearchTerm = "CHP"
SearchSheet = "Data"
FoundSheet = "CHP"
StartColumn = 2
StartRow = 12


Last_Row = ActiveWorkbook.Worksheets(SearchSheet).Range("A1").End(xlDown).Row 'calculates how many rows there are on the SearchSheet
Found_RowNumber = 1 'used to keep track of which row we are copying data to on the FoundSheet
StartRow = StartRow - 1 'remove 1 from the requested StartRow to get the correct offset
StartColumn = StartColumn - 1 'remove 1 from the requested StartColumn to get the correct offset
For Data_RowNumber = 2 To Last_Row 'create a ForLoop for how many rows there are on the SearchSheet
If Worksheets("Data").Cells(Data_RowNumber, 1).Value = (SearchTerm) Then 'if we found the search term in the search sheet on the row we were checking
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 1).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 1).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 2).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 2).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 3).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 3).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 4).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 4).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 5).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 5).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 6).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 6).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 7).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 7).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 8).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 8).Value
Worksheets(FoundSheet).Cells(StartRow + Found_RowNumber, StartColumn + 9).Value = Worksheets(SearchSheet).Cells(Data_RowNumber, 9).Value
Found_RowNumber = Found_RowNumber + 1
End If
Next Data_RowNumber

End Sub

 

[GWteB]

The code I provided uses column J to determine what the last populated row is. In case column J would contain less populated cells than eg column B (or one of the other columns between B and J), the area to be exported as a PDF would therefore be smaller than expected.
If you can indicate which column should be decisive for the correct length of the area, the code can be adjusted to that.

 

[Luis_B]

The code I provided uses column J to determine what the last populated row is. In case column J would contain less populated cells than eg column B (or one of the other columns between B and J), the area to be exported as a PDF would therefore be smaller than expected.
If you can indicate which column should be decisive for the correct length of the area, the code can be adjusted to that.

Ok I think I understand. The column that should be decisive for the correct length is B

 

[Luis_B]

The code I provided uses column J to determine what the last populated row is. In case column J would contain less populated cells than eg column B (or one of the other columns between B and J), the area to be exported as a PDF would therefore be smaller than expected.
If you can indicate which column should be decisive for the correct length of the area, the code can be adjusted to that.

The code I provided uses column J to determine what the last populated row is. In case column J would contain less populated cells than eg column B (or one of the other columns between B and J), the area to be exported as a PDF would therefore be smaller than expected.
If you can indicate which column should be decisive for the correct length of the area, the code can be adjusted to tha

The code I provided uses column J to determine what the last populated row is. In case column J would contain less populated cells than eg column B (or one of the other columns between B and J), the area to be exported as a PDF would therefore be smaller than expected.
If you can indicate which column should be decisive for the correct length of the area, the code can be adjusted to that.

I changed the the decisive column to B and I get the same error (I don't get all the data brought over from the "data" sheet to sheet2. I don't think there is anything wrong with your code, I think it is something to do with my other code but I'm not sure

 

[GWteB]

Then it would be ...

Dim Rng As Range
With ActiveSheet
    Set Rng = .Range("B1:J" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:="CHP.pdf", Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With

 

[GWteB]

I think it is something to do with my other code but I'm not sure

That's something I can't determine.

 

[Luis_B]

That's something I can't determine.

Right. Thank you so much for your help. I learned something new today

 

[GWteB]

Glad to help and thanks for the feedback.