Hi
I've spent a few days working on trying to establish something that should be quite simple, but anything I've found online doesn't seem to work, so I'm really hoping someone can help.
The simple requirement is this: I have an Excel file that allows pricing to be conducted via various different selections. It allows a Salesperson to produce a Quote for multiple contract types. The selections made appear in a hidden worksheet which is effectively the proposal that can be sent to the customer.
Once the pricing selections are made, a macro button allows for the proposal sheet to appear in PDF.
The issue I have, is that depending on the contract types chosen, the proposal needs to include certain content (terms and conditions and product guides).
To manage this I've created them all in the proposal with some formulas that put a zero on the row if the contract type has not been priced (and therefore the row containing the product guide is not required)
The first step of the Macro is then to filter out "0" on the column so it only contains relevant rows.. The problem I experience is that the PDF generates with blank spaces wherever the rows would have been.
I realise I've probably done this in a very clunky way, but I either need to make the macro only export visible rows to the PDF, or do something smarter. This is the Macro:
Sub Send_PDF_2()
'
'
Application.StatusBar = "Please wait while Quote is created"
Application.ScreenUpdating = True
ActiveWorkbook.Unprotect (Range("Password").Value)
Sheets("Proposal Sheet 2").Visible = True
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Proposal Sheet 2").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveSheet.Range("$B$1:$AY$2082").AutoFilter Field:=1, Criteria1:="="
Dim vShts As Variant
Dim strFileName As String
vShts = Sheets(1).Range("A1")
If Not IsNumeric(vShts) Then
Exit Sub
Else
strFileName = Application.GetSaveAsFilename( _
InitialFileName:="\\server\share\folder\", _
FileFilter:="PDF Files (*.pdf),*.pdf", _
Title:="Save As PDF")
If strFileName <> "False" Then
Select Case vShts
Case 1
Sheets("Proposal Sheet 2").Select
End Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=strFileName, _
OpenAfterPublish:=False
End If
End If
Sheets("Proposal Sheet 2").Visible = False
ActiveWorkbook.Protect Range("PASSWORD").Value, True, False
Sheets("Ebidd").Select
End Sub
Could someone just help me with a way of ensuring the filtered out rows, do not appear as blanks on the PDF?
Thanks in advance
Andy
I've spent a few days working on trying to establish something that should be quite simple, but anything I've found online doesn't seem to work, so I'm really hoping someone can help.
The simple requirement is this: I have an Excel file that allows pricing to be conducted via various different selections. It allows a Salesperson to produce a Quote for multiple contract types. The selections made appear in a hidden worksheet which is effectively the proposal that can be sent to the customer.
Once the pricing selections are made, a macro button allows for the proposal sheet to appear in PDF.
The issue I have, is that depending on the contract types chosen, the proposal needs to include certain content (terms and conditions and product guides).
To manage this I've created them all in the proposal with some formulas that put a zero on the row if the contract type has not been priced (and therefore the row containing the product guide is not required)
The first step of the Macro is then to filter out "0" on the column so it only contains relevant rows.. The problem I experience is that the PDF generates with blank spaces wherever the rows would have been.
I realise I've probably done this in a very clunky way, but I either need to make the macro only export visible rows to the PDF, or do something smarter. This is the Macro:
Sub Send_PDF_2()
'
'
Application.StatusBar = "Please wait while Quote is created"
Application.ScreenUpdating = True
ActiveWorkbook.Unprotect (Range("Password").Value)
Sheets("Proposal Sheet 2").Visible = True
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Proposal Sheet 2").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
ActiveSheet.Range("$B$1:$AY$2082").AutoFilter Field:=1, Criteria1:="="
Dim vShts As Variant
Dim strFileName As String
vShts = Sheets(1).Range("A1")
If Not IsNumeric(vShts) Then
Exit Sub
Else
strFileName = Application.GetSaveAsFilename( _
InitialFileName:="\\server\share\folder\", _
FileFilter:="PDF Files (*.pdf),*.pdf", _
Title:="Save As PDF")
If strFileName <> "False" Then
Select Case vShts
Case 1
Sheets("Proposal Sheet 2").Select
End Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=strFileName, _
OpenAfterPublish:=False
End If
End If
Sheets("Proposal Sheet 2").Visible = False
ActiveWorkbook.Protect Range("PASSWORD").Value, True, False
Sheets("Ebidd").Select
End Sub
Could someone just help me with a way of ensuring the filtered out rows, do not appear as blanks on the PDF?
Thanks in advance
Andy