Earlier this month I was trying to figure out how to add a print button to a worksheet to print selected cells as a PDF document - https://www.mrexcel.com/forum/excel...ksheet-print-selected-cells-pdf-document.html - which as you can see from that thread, I eventually figured out using this code:
This was working fine on my test spreadsheet, which I was working on at home. This morning though as I couldn't email it to myself at work as a macro enabled Excel file, I had to save the file as a .xlsx and then once I'd got it saved on the system at work, then re-assign the macros to the appropriate buttons. Printing the pages as a PDF file was working fine on my test version at home, and initially seemed to work fine at work, but then I started getting intermittent VBA errors, sometimes 400 errors, sometimes 1004 errors.
My VBA skills are very much at the basic level - when I see it I can work out what it's doing but my skill level is more or less tweaking what I find in online tutorials and forums. On my copy of the spreadsheet at home, the code's in a module, and the buttons on the printable pages all are assigned to that same code block. Initially at work, I couldn't get it to work at all, was getting 400 errors, then 1004 errors. My VBA skills being basic I looked online to try and figure out what was going wrong, tried stepping through the code, and realised that the problem was with the filename, which I'd changed when I saved it on the system at work. Having tried it again it initially worked fine on one page, but couldn't get it to work on any of the other pages. I eventually got it to work by right-clicking on the tab of each page and pasting the code in for each page so I ended up with a macro for saving as a PDF for each sheet. That worked for a while on some pages, but then I started getting the same 400 and 1004 VBA errors. After looking into the problem again online I read somewhere that with these errors it's best to set up a new module and delete copies of the code set up at worksheet level. I did this and for 2 tries it worked, assigning the print button on each page to the same module, then suddenly stopped and the 400 and 1004 errors popped up every time I tried to print a worksheet as a PDF.
This setup, with a 'button' on each printable page assigned to the single macro is still working on my copy of the spreadsheet at home - I've tried it at home this evening, but the same setup on the copy at work is refusing to play. There doesn't seem to be a problem with the code itself, as it works intermittently, but not knowing that much about VBA I'm at a loss as to why it only works intermittently on the copy of the spreadsheet at work, but is working fine on my copy at home.
Does anyone have any suggestions as to what might be wrong?
Code:
Sub Save_Timesheet_As_PDF()
With ActiveSheet.PageSetup
.CenterHeader = "Time Allocation Sheet"
.Orientation = xlLandscape
.PrintArea = "$A$1:$I$40"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="Time Allocation Sheet", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End SubThis was working fine on my test spreadsheet, which I was working on at home. This morning though as I couldn't email it to myself at work as a macro enabled Excel file, I had to save the file as a .xlsx and then once I'd got it saved on the system at work, then re-assign the macros to the appropriate buttons. Printing the pages as a PDF file was working fine on my test version at home, and initially seemed to work fine at work, but then I started getting intermittent VBA errors, sometimes 400 errors, sometimes 1004 errors.
My VBA skills are very much at the basic level - when I see it I can work out what it's doing but my skill level is more or less tweaking what I find in online tutorials and forums. On my copy of the spreadsheet at home, the code's in a module, and the buttons on the printable pages all are assigned to that same code block. Initially at work, I couldn't get it to work at all, was getting 400 errors, then 1004 errors. My VBA skills being basic I looked online to try and figure out what was going wrong, tried stepping through the code, and realised that the problem was with the filename, which I'd changed when I saved it on the system at work. Having tried it again it initially worked fine on one page, but couldn't get it to work on any of the other pages. I eventually got it to work by right-clicking on the tab of each page and pasting the code in for each page so I ended up with a macro for saving as a PDF for each sheet. That worked for a while on some pages, but then I started getting the same 400 and 1004 VBA errors. After looking into the problem again online I read somewhere that with these errors it's best to set up a new module and delete copies of the code set up at worksheet level. I did this and for 2 tries it worked, assigning the print button on each page to the same module, then suddenly stopped and the 400 and 1004 errors popped up every time I tried to print a worksheet as a PDF.
This setup, with a 'button' on each printable page assigned to the single macro is still working on my copy of the spreadsheet at home - I've tried it at home this evening, but the same setup on the copy at work is refusing to play. There doesn't seem to be a problem with the code itself, as it works intermittently, but not knowing that much about VBA I'm at a loss as to why it only works intermittently on the copy of the spreadsheet at work, but is working fine on my copy at home.
Does anyone have any suggestions as to what might be wrong?
