VBA Save only sheets based on criteria

dfcr

New Member
Joined
Apr 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone!
I'm fairly new to VBA and I'm facing a problem : I have a pre-selection of sheets to save to pdf. However, inside this selection I want to only save the ones that are filled on the cells C8 or D8.
Is this possible to do?
At the moment my code looks like this , it's saving all of the pre-selection but I'm not being able to filter the second part.
Can someone help me out?

Sub Export_As_PDF()

Filename = Application.GetSaveAsFilename( _

fileFilter:="PDF Files (*.pdf), *.pdf")

If TypeName(Filename) = "Boolean" Then

MsgBox "Processo Cancelado."

Exit Sub

Else

CheckName = VBA.FileSystem.Dir(Filename)

If CheckName = VBA.Constants.vbNullString Then

Sheets(Array("Capa", "Condições", "Tarifário_Envios ibéricos", "Tarifário_Envios ibéricos (2)", "Tarifário_Envios internacionais", "Tarifário_Envios carga", "Tarifário_Para Hoje", "Tarifário_Serviços adicionais", "Tarifário_Serviços adiciona (2)", "Tarifário_Serviços adiciona (3)", "Tarifário_Serviços adiciona (4)", "Aprovação")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename, openafterpublish:=True, ignoreprintareas:=False

ActiveWindow.Zoom = True

MsgBox "Proposta Comercial Exportada para PDF."

Else

answer = MsgBox("The FileName already exists. Do you want to overwrite?", vbQuestion + vbYesNo)

If answer = vbYes Then

Sheets(Array("Capa", "Condições", "Tarifário_Envios ibéricos", "Tarifário_Envios ibéricos (2)", "Tarifário_Envios internacionais", "Tarifário_Envios carga", "Tarifário_Para Hoje", "Tarifário_Serviços adicionais", "Tarifário_Serviços adiciona (2)", "Tarifário_Serviços adiciona (3)", "Tarifário_Serviços adiciona (4)", "Aprovação")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename, openafterpublish:=True, ignoreprintareas:=False

ActiveWindow.Zoom = True

MsgBox "Proposta Comercial Exportada para PDF."

Else

MsgBox "Proposta Cancelada."

Exit Sub

End If

End If
End If

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub Export_As_PDF()
   Dim Ws As Worksheet
   Dim i As Long
   Dim Ary() As Variant, FileNme As Variant
   Dim CheckName As String
   
   FileNme = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf")
   If TypeName(FileNme) = "Boolean" Then
      MsgBox "Processo Cancelado."
      Exit Sub
   End If
   CheckName = VBA.FileSystem.Dir(FileNme)
   If CheckName <> VBA.Constants.vbNullString Then
      If MsgBox("The FileName already exists. Do you want to overwrite?", vbQuestion + vbYesNo) = vbNo Then
         MsgBox "Proposta Cancelada."
         Exit Sub
      End If
   End If
   
   For Each Ws In Worksheets(Array("Capa", "Condições", "Tarifário_Envios ibéricos", "Tarifário_Envios ibéricos (2)", "Tarifário_Envios internacionais", "Tarifário_Envios carga", "Tarifário_Para Hoje", "Tarifário_Serviços adicionais", "Tarifário_Serviços adiciona (2)", "Tarifário_Serviços adiciona (3)", "Tarifário_Serviços adiciona (4)", "Aprovação"))
      If Ws.Range("C8").Value <> "" Or Ws.Range("D8").Value <> "" Then
         ReDim Preserve Ary(i)
         Ary(i) = Ws.Name
         i = i + 1
      End If
   Next Ws
   Worksheets(Ary).Select
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileNme, openafterpublish:=True, ignoreprintareas:=False
   ActiveWindow.Zoom = True
   MsgBox "Proposta Comercial Exportada para PDF."

End Sub
 
Solution

dfcr

New Member
Joined
Apr 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub Export_As_PDF()
   Dim Ws As Worksheet
   Dim i As Long
   Dim Ary() As Variant, FileNme As Variant
   Dim CheckName As String
  
   FileNme = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf")
   If TypeName(FileNme) = "Boolean" Then
      MsgBox "Processo Cancelado."
      Exit Sub
   End If
   CheckName = VBA.FileSystem.Dir(FileNme)
   If CheckName <> VBA.Constants.vbNullString Then
      If MsgBox("The FileName already exists. Do you want to overwrite?", vbQuestion + vbYesNo) = vbNo Then
         MsgBox "Proposta Cancelada."
         Exit Sub
      End If
   End If
  
   For Each Ws In Worksheets(Array("Capa", "Condições", "Tarifário_Envios ibéricos", "Tarifário_Envios ibéricos (2)", "Tarifário_Envios internacionais", "Tarifário_Envios carga", "Tarifário_Para Hoje", "Tarifário_Serviços adicionais", "Tarifário_Serviços adiciona (2)", "Tarifário_Serviços adiciona (3)", "Tarifário_Serviços adiciona (4)", "Aprovação"))
      If Ws.Range("C8").Value <> "" Or Ws.Range("D8").Value <> "" Then
         ReDim Preserve Ary(i)
         Ary(i) = Ws.Name
         i = i + 1
      End If
   Next Ws
   Worksheets(Ary).Select
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FileNme, openafterpublish:=True, ignoreprintareas:=False
   ActiveWindow.Zoom = True
   MsgBox "Proposta Comercial Exportada para PDF."

End Sub
Hi!!

Thank you so much! This works perfectly!
I'm now wondering something, is it possible to make it always save the "Capa", "Condições" and " Aprovação" sheets and only make it run the criteria on the other ones? But saving it all in the same pdf?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Why not just ensure that either C8 or D8 on those sheets always has a value.
 

dfcr

New Member
Joined
Apr 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Because those are like frontcovers for the rest, so it would cause less possible errors.
But I managed to do it by creating a new array and joining with the one you suggested !

VBA Code:
Obrigatorios = Array("Capa", "Condições", "Aprovação")
   Final = Split(Join(Obrigatorios, ",") & "," & Join(Ary, ","), ",")

Thank you so much for your help! Now everything is working perfectly!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,485
Messages
5,636,608
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top