Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Printing range of sheets in excel or in PDF

  1. 08-17-2015, 10:52 AM #11
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    15

    Printing of range of sheets in excel or in PDF

    Hi, the short report is attached.
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
  2. 08-20-2015, 07:40 AM #12
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Code:
    For i = 3 To 38
    strRngName = .Cells(i, 2).Text
    If Not ThisWorkbook.Name = ActiveWorkbook.Name Then ThisWorkbook.Activate
    strShtname = Range(strRngName).Parent.Name
    strFileName = wbkActive.Path & "\" & strShtname & Format(Date, "dd-mm-yy")
    'clear any existing print areas and reset to named ranges areas
    With wbkActive.Worksheets(strShtname)
        .PageSetup.PrintArea = ""
        .PageSetup.PrintArea = .Range(strRngName).Address
      
        '// Paste the data to the workbook for PDF
        .Range(strRngName).Copy rngDest
        RowsCount = .Range(strRngName).Rows.Count
        Set rngDest = rngDest.Offset(RowsCount)
        '// Paste the data to a new workbook
        Set wbkNew = Workbooks.Add
        .Range(strRngName).Copy wbkNew.Worksheets(1).Range("a1")
        '// Save the print area as a new file
        wbkNew.SaveAs strFileName, 51
        wbkNew.Close
        Set wbkNew = Nothing
    End With
Next i
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
  3. 08-20-2015, 10:54 AM #13
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    15
    Hi, thank you it worked, creates the pdf file but looses print areas and original columns' width and formatting, does not convert the ranges into a separate page. It also create an excel file but with same issue of print areas.
    Last edited by excel_learner; 08-20-2015 at 11:03 AM.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
  4. 08-21-2015, 11:02 AM #14
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    code the copy paste part like this

    Code:
    .Range(strRngName).Copy
rngDest.PasteSpecial xlPasteValues
rngDest.PasteSpecial xlPasteFormats
rngDest.PasteSpecial xlPasteColumnWidths
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
  5. 08-26-2015, 04:51 PM #15
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    15
    Thanks, however, in which place should i insert these codes.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
  6. 08-27-2015, 07:29 AM #16
    Admin
    Admin is offline
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Sorry.

    replace the following lines and paste the above code in procedure 'Print_Rangespdf'

    Code:
     '// Paste the data to the workbook for PDF
                .Range(strRngName).Copy rngDest
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
    Reply With Quote Reply With Quote
  7. 08-27-2015, 11:13 AM #17
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    15
    Hi, Admin, Thanks for the advice, however, see the attached file if i have correctly used the codes, as it has same formatting issue.

    Please advise and thank you for your cooperation and patience.

    Regards
    Attached Files Attached Files
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
  8. 09-03-2015, 12:25 PM #18
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    15
    Dears

    Any update on this.

    Thank you

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316057#p316057
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=316705#p316705
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=176255#p176255
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-27-2024 at 02:14 PM.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
  9. 10-11-2015, 01:36 PM #19
    excel_learner
    excel_learner is offline
    Member
    Join Date
    Apr 2011
    Posts
    69
    Rep Power
    15
    Dears

    Any updates on this...please.
    ----
    excel_learner
    (Using XL 2007 & 2010)

    -------Learning never stops!
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Copy Selected Range From One Sheet To Multiple Workbooks Sheets Using VBA
    By olives in forum Excel Help
    Replies: 3
    Last Post: 05-14-2013, 03:25 PM
  2. Converting Excel Sheets automatically into Powerpoint Slides
    By Howardc in forum Powerpoint Help
    Replies: 2
    Last Post: 04-14-2013, 08:23 PM
  3. Print excel sheet to a PDF file
    By xander1981 in forum Excel Help
    Replies: 2
    Last Post: 12-14-2012, 02:16 PM
  4. Sending E-mails w/PDF Attachments via Command Buttons in Excel
    By zyousafi in forum Excel Help
    Replies: 7
    Last Post: 08-08-2012, 10:24 AM
  5. Printing Sheets Based On Criteria VBA
    By excel_learner in forum Excel Help
    Replies: 1
    Last Post: 05-04-2011, 08:00 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