+ Reply to Thread
Results 1 to 19 of 19

VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

  1. 07-12-2016, 01:16 PM #1
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Hi All

    Have been struggling for hours to fix code to work - Have searched all posts. I need expert assistance please.
    I have a workbook with 6 sheets....Sheet 1 to Sheet 6.
    I want to save the worksheets as PDF's with cell value of all worksheets i.e. Range("I4") except Sheet 1,2,4 & 6
    Here is code:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 07-12-2016, 01:18 PM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    You don't say what the problem is but this should be inside the loop.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 07-12-2016, 01:29 PM #3
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Hi Norie

    Thanks for feedback. I get a runtime error 5. Invalid procedure or call argument
    Reply With QuoteRegister To Reply
  4. 07-12-2016, 01:37 PM #4
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Hi Norie

    My Cell("E4") has the following value: =B2&TEXT(E5,"yyyy-mm")&"-"&E3

    Is this perhaps causing problem
    Reply With QuoteRegister To Reply
  5. 07-12-2016, 01:37 PM #5
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Where do you get the error?

    PS In the code you have E4 not I4, which should it be?
    Reply With QuoteRegister To Reply
  6. 07-12-2016, 01:42 PM #6
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    At following:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 07-12-2016, 01:43 PM #7
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    It should be E4 sorry
    Reply With QuoteRegister To Reply
  8. 07-12-2016, 01:48 PM #8
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Hi Norrie

    Code changed to below and now runtime error - 2147024773 (8007007B) document not saved

    Please Login or Register  to view this content.
    Last edited by sintek; 07-12-2016 at 02:00 PM.
    Reply With QuoteRegister To Reply
  9. 07-12-2016, 01:50 PM #9
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,909

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name 

    Please Login or Register  to view this content.
    Move above part just below
    Please Login or Register  to view this content.
    PS Saw you already did that.
    Last edited by bakerman2; 07-12-2016 at 01:52 PM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
    Reply With QuoteRegister To Reply
  10. 07-12-2016, 01:58 PM #10
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,909

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name 

    Please Login or Register  to view this content.
    Is folderpath correctly created?
    To test put this 
    Please Login or Register  to view this content.
    just beneath fname line.
    Last edited by bakerman2; 07-12-2016 at 02:00 PM.
    Reply With QuoteRegister To Reply
  11. 07-12-2016, 02:01 PM #11
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Hi Bakerman2

    Twice in one day...thanks. Code has been fixed as above still no solve though
    Reply With QuoteRegister To Reply
  12. 07-12-2016, 02:07 PM #12
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Yes, folderpath comes up correctly, however does not get saved
    Reply With QuoteRegister To Reply
  13. 07-12-2016, 02:09 PM #13
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Have attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  14. 07-12-2016, 02:14 PM #14
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Are you sure E4 is correct?

    E4 is empty on the sheet 'Cost Centre'.
    Reply With QuoteRegister To Reply
  15. 07-12-2016, 02:16 PM #15
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Hi bakerman2

    Sorry, just realised message box comes up as: "C:\Users\Steven\Desktop\fdp\Client fOLDER\steven gerber\.pdf.
    WbName is ommited before .pdf
    WbName has following value:=B2&TEXT(E5,"yyyy-mm")&"-"&E3
    Reply With QuoteRegister To Reply
  16. 07-12-2016, 02:18 PM #16
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    That means E4 on the sheet you are trying to save is empty.
    Reply With QuoteRegister To Reply
  17. 07-12-2016, 02:22 PM #17
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Oh my word....what a ninkunpoop I am...That solves it bakerman2.

    Thanks so much
    Reply With QuoteRegister To Reply
  18. 07-12-2016, 02:28 PM #18
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,308

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Norie

    Pure Genius...I have learned so much over the post few months. From zero knowledge to beginner level.....Getting there lol.

    Thanks
    Reply With QuoteRegister To Reply
  19. 07-12-2016, 02:32 PM #19
    bakerman2
    bakerman2 is offline
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,909

    Re: VBA code assistance with saving all worksheets except 4 as PDF with cell value as name

    Glad you got it working.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Functional code only works correctly after saving. Saving required each time file reopened
    By James2584 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-15-2016, 06:24 PM
  2. Code that allows users, at the time of saving, to select which worksheets to save as pdfs
    By moosetales in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2016, 08:35 AM
  3. Request assistance with VBA CODE to log cell contents prior to a change event
    By CJ_ExcelNoob in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2015, 03:17 PM
  4. [SOLVED] Looping through cell ranges in multiple worksheets and prevent saving if cell value empty
    By Spritz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-22-2013, 11:24 AM
  5. [SOLVED] Macro assistance to identify dates in one range and place text in a cell in worksheets
    By lilsnoop in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-08-2013, 12:29 PM
  6. Answer to Saving worksheets as individual workbooks using code?
    By H.A. de Wilde in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2006, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1