![]() We want to leave our user with a single active Worksheet (rather than leaving all three of them selected), since leaving all three selected might lead to the user making mistakes. The most important consideration here is Filename:=, which we set to the strFilename variable we created in Step 2 – Exploration (which uses info from our first Worksheet).Īnd finally, Step 4 – Cleanup. Line 25 uses the Worksheet.ExportAsFixedFormat method, since Selection.ExportAsFixedFormat appears to be buggy in Excel 2013! (Check this Stack Overflow post for more context on the Excel 2013 bug: ) Line 24 uses Select, which should usually be avoided, but comes in handy here as the next line relies on it. Step 3 – Execution happens on lines 25-31. We want to use cells D6, E6 and F6 to create our actual PDF file name (D6 E6-F6.pdf), so we collect the values from each cell and concatenate everything together using &. Next up is Step 2 – Exploration, which happens on lines 14-21. Finally, strFilepath holds the parent folder we would like to write our PDF file to. We then create a Variant Array, which holds all the names of the Worksheets we are targeting. We assign wksSheet1 to our “Sheet1” Worksheet (there are less explicit ways to do this, but I prefer explicit over implicit and you should too). Per usual, we start with Step 1 – Setup, which is handled by lines 9-11. Nice! Let’s walk through our script using the 4-step VBA process as tour guide: Here’s a link to the code above so you can review it side-by-side with the walk through below. So without further ado, let’s get to the code! For a little extra fun, we are also going to dynamically set the file name, using cells D6, E6 and F6. This file contains three Worksheets, and we want all of them to be combined into a single PDF. ![]() Our example workbook has 3 sheets, and we want to save all of them together as a single PDF If fName "Master1.xls" And fName "Master2.xls" Then 'or. 'wbNew.Sheets().Name = wbNew.Sheets().Cells(1, 1)Īpplication.Calculation = xlCalculationAutomatic Sheet.Copy After:=wbNew.Sheets()ĪctiveSheet.Name = ActiveSheet.Range("F6") Workbooks.Open FileName:=FolderPath & FileName, ReadOnly:=True 'wbNew.SaveAs FileName:="C:\Users\swaroopa.bp\Desktop\C\Consolidation", FileFormat:=xlWorkbookNormalĪpplication.Calculation = xlCalculationManual Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=Falseĭim wbOpen As Workbook, wbNew As Workbook, fName As String, strPath As Stringĭim FolderPath As String, fldr As FileDialog, sItem As String, FolderName As Stringĭim FileName As String, Sheet As Worksheet Range("A1:E20").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & "\" &. 'File_Name = FolderName & "\" & File_Name & ".xlsm" ' Create a path by combining the file and folder names: ' If the user didn't select anything, you can't save, so tell them so: InitialFileName = Application.DefaultFilePath Set fldr = Application.FileDialog(msoFileDialogFolderPicker) LastRow = Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row operation repeats with row 3 and so on to the last non-empty row in column B of my source tableĭim i As Long, LastRow As Long, FolderPath As Stringĭim fldr As FileDialog, sItem As String, FolderName As String.code prints template to pdf and takes the name from the same row in source table (my table has headers, so it would be E2) and saves the pdf file under that name.code takes first value from source table (zaposleni B2) and paste in my template sheet. ![]() I'm choosing existing or creating a new folder. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |