How to export a sheet from a workbook with multiple sheets

Hi,
    
we will be going trough step by step into all the details of how to make this app with minimal understanding on how to use a computer.

     1) We open Excel -> File->Options->Customize Ribbon->We check Developer

     2) We go back to the main screen of the excel file and we can now find the Developer tab added on the upper part of the screen, right next to File, Insert, etc.
     3)  Select Developer->Click on"Visual Basic" -> Right click on Modules-> Insert-> Module

     4) An example for a set of data to test the function which needs to be copied into Sheet2 to work for the script that we created in its initial form. You can change the source sheet without any problem:

  

id	Name	Age
1	Dan	23
2	Ben	43
3	Jack	53
4	Chuck	27
5	Sam	34

     5) Copy this code and insert it in the new module window from the developer tab:

Sub Export_Sheet_And_Save_as_PDF()

Dim strFileLocation 'we declare the variable for the path where we'll be saving the sheet.

Dim strFileName 'we declare the variable for the name of the file

strFileName = Sheets("Sheet1").Range("A2") 'we set the variable to target the information from cell A2 in Sheet1. Why? You want to change dynamically and easily the name of the file, let's say you have more files that need to be created and you must change the name of the file corresponding to other variables.Well, by writing the name of the file in cell A2 in Sheet 1 you can achieve this.

strFileLocation = Sheets("Sheet1").Range("A3") 'Same setting  for the location. What if you have multiple files that need to be stored into different locations corresponding to their name. You can achieve this altough by setting the value of the variable like this.

Dim wb As Workbook 'we declare the variable wb as an excel workbook

Set wb = Workbooks.Add 'We set the variable to add a new workbook  (in this workbook we will insert our exported sheet from the main workbook file)

ThisWorkbook.Sheets("Sheet2").Copy Before:=wb.Sheets(1) 'We copy Sheet2 from our main workbook. As I said earlier you can set it as you please by inserting the data table where you want. The last part of the function says to Paste it before wb(which is the new workbook we created with the add function).

wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileLocation & " " & strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 'I need to also export the resulted file in a pdf format. If you don t need this function you can simply delete it.

wb.SaveAs strFileLocation & " " & strFileName & ".xlsx" 'After we created the new workbook, pasted the sheet from the main workbook we need to save it as a new individual file.

answer = MsgBox("Do you want to print now?", vbYesNo, "Save paper!") 'I know you might find this functions useless, but you also might need them in one place. This function prints the new resulted file as a single file by asking you if you want it or not. ( of course the printer is the default one from your operating system settings).

    If answer = vbYes Then

    wb.PrintOut Copies:=1 'how many copies you want to print

    End If

wb.Close

End Sub