# Save worksheet when Set Print Area is being used



## ipbr21054 (Jan 5, 2023)

Hi,

I use the code below & works fine.

Now i wish to use the same code on another worksheet BUT on this sheet i use the Set Print Area
The are thatonly needs to be saved is A1:K23
Rows after K23 have values in etc BUT i dont want them on the saved pdf sheet

Please advise how i can continue
Thanks


```
Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets("PRINT LABELS").Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
ThisWorkbook.Worksheets("PRINT LABELS").Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
Unload GenerateForm
  Dim sPath As String, strFileName As String
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & Range("B3").Value & " " & Range("A3").Value & ".pdf"
                                                                             
    With ActiveSheet
    If Range("AB1") = "" Then
    MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
    Exit Sub
    End If
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
  MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
  
  End With
  
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\EBAY PDF\"
  strFileName = sPath & Range("AB1").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
 
End Sub
```


----------



## HaHoBe (Jan 6, 2023)

Hi.

it works for me to add the range to printout for the sheet like


```
Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
  End With
  Unload GenerateForm
                                                                             
  With ActiveSheet
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("A3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
    sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\EBAY PDF\"
    strFileName = sPath & .Range("AB1").Value & ".pdf"
  End With
  
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
 
End Sub
```

And if the sheet to printout is PRINT LABELS you may use


```
Private Sub CommandButton1_Click()
  Dim sPath As String
  Dim strFileName As String
  
  With ThisWorkbook.Worksheets("PRINT LABELS")
    .Range("B3") = Me.TextBox1.Text ' ENTERS CUSTOMERS NAME TO WORKSHEET
    .Range("A3") = Me.TextBox2.Text ' ENTERS PCB NUMBER TO WORKSHEET
    Unload GenerateForm
                                                                             
    If .Range("AB1") = "" Then
      MsgBox "NO CODE SHOWN TO GENERATE PDF", vbCritical, "NO CODE ON SHEET TO CREATE PDF"
      Exit Sub
    End If
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\" & .Range("B3").Value & " " & .Range("A3").Value & ".pdf"
    .Range("A1:K23").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "PDF HAS NOW BEEN GENERATED", vbInformation + vbOKOnly, "GENERATE PDF FILE MESSAGE"
    
    sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\EBAY PDF\"
    strFileName = sPath & .Range("AB1").Value & ".pdf"
  End With
  
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
 
End Sub
```

Ciao,
Holger


----------



## ipbr21054 (Jan 6, 2023)

Im confused as both the codes you advise are the same ?


----------



## HaHoBe (Jan 6, 2023)

Hi,

not exactly: the first has 2 With clauses (one for PRINT LABELS, one for ActiveSheet), the second just uses one while specifying PRINT LABELS as the working sheet to receive data and being printed.

Holger


----------



## ipbr21054 (Jan 6, 2023)

Sorry my mistake

will look now thanks


----------

