excel to pdf ignores print area and calls printer

by

so I changed track – excel vba does not seem to be happy producing pdf files with the printer set as it is…
So, i changed to export an excel file per student using copy & paste special values and formats. Here is the code i did (lots stolen from other answers on here! thanks…) Any comments about improving the code are welcome – I think there is a lot of scope for that!!

    Option Explicit

Sub Exportmacro()
    Dim rCell As Range, rRng As Range 'define loop names
    Dim NewCaseFile As Workbook 'give a name to new work book for duplicate sheet
    Dim wks As Worksheet 'name of the copy of feedback
    Dim sPath As String
    sPath = MacScript("(path to desktop folder as string)")
'turn off screen
With Application
'        .ScreenUpdating = False  ‘only removed while testing
'        .EnableEvents = False
'        .Calculation = xlCalculationManual  ‘disabled for the moment
End With

    'Student numbers in cells A7:A160 WARNING SET TO 3 STUDENTS ONLY FOR TEST
    Set rRng = Worksheets("studentlist").Range("A7:A9")

    With Worksheets("Feedback") '<--| reference "Feedback" worksheet

        For Each rCell In rRng '<--| loop through "students" range
            .Range("A1").Value = rCell.Value '<--| write current student number to cell A1 on Feedback sheet

           'do copy ready for paste spec vals to destroy links & calculations
               ActiveSheet.Range("A2:W77").Copy

            'now open new workbook then pastespecial values and formats
             Set NewCaseFile = Workbooks.Add
             NewCaseFile.Sheets(1).Range("A1").PasteSpecial xlPasteValues
             NewCaseFile.Sheets(1).Range("A1").PasteSpecial xlPasteFormats

            'now save as xls with student number as filename Filename:=sPath & rCell.Value & ".xlsx"
             ActiveWorkbook.SaveAs Filename:=rCell.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

            'now close duplicate file
             ActiveWorkbook.Close False

        Next rCell   '<-- next student number
    End With         '<-- once all done
'turn screen back on
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

More Related Contents:

  1. Save multiple sheets to .pdf
  2. Excel VBA to Export Selected Sheets to PDF
  3. Extract Data from PDF and Add to Worksheet
  4. Save excel file multiple times with file names in another workbook.
  5. Sequence of numbers based on start and end value of 2 cells [closed]
  6. Read/Parse text file line by line in VBA
  7. How do I find the last column with data?
  8. Excel VBA: Answer gets “stuck”
  9. How to insert a picture into Excel at a specified cell position with VBA
  10. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  11. Excel VBA autofilter all but three
  12. How can I create a calendar input in VBA Excel?
  13. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  14. Using Google Chrome in Selenium VBA (Installation Steps)
  15. Excel VBA code to copy a specific string to clipboard
  16. Merge values of column B based on common values on column A
  17. How to import a table from web page (with “div class”) to excel?
  18. Range limit conundrum
  19. How do you run a .exe with parameters using vba’s shell()?
  20. Excel VBA – Range.Copy transpose paste
  21. How to determine the last Row used in VBA including blank spaces in between [duplicate]
  22. How to give a time delay of less than one second in excel vba?
  23. Changing the date format to yyyy-mm-dd
  24. Delete all duplicate rows Excel vba
  25. every possible combination of the contents of 2 columns in excel
  26. excel vba freeze pane without select
  27. mac excel vba loop : from list & then export as pdf
  28. How to delete rows in Excel based on criteria using VBA?
  29. Converting a string representation of a constant into a constant?
  30. How to protect Excel workbook using VBA?

Leave a Comment