How about ...
VBA Code:
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