Results 1 to 7 of 7

Multiple Sheet Export to PDF, one sheet needs specific range based lastrow of data

Threaded View

xlyfe Multiple Sheet Export to PDF,... 04-25-2018, 02:08 PM
xlyfe Re: Multiple Sheet Export to... 04-25-2018, 05:00 PM
jolivanes Re: Multiple Sheet Export to... 04-25-2018, 05:01 PM
xlyfe Re: Multiple Sheet Export to... 04-25-2018, 05:11 PM
xlyfe Re: Multiple Sheet Export to... 04-25-2018, 05:16 PM
xlyfe Re: Multiple Sheet Export to... 04-25-2018, 06:03 PM
xlyfe Re: Multiple Sheet Export to... 04-25-2018, 07:39 PM
  1. #1
    Forum Contributor
    Join Date
    03-21-2013
    Location
    USA
    MS-Off Ver
    365
    Posts
    163

    Multiple Sheet Export to PDF, one sheet needs specific range based lastrow of data

    Here is the formula that I currently have.

    Sub PDFOut()
        
        ThisWorkbook.Sheets(Array("Cover Sheet", "Summary", "Back Page")).Select
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "P:\Test.pdf", quality:=xlQualityStandard, includedocproperties:=True, _
                ignoreprintareas:=False, openafterpublish:=True
                
        ThisWorkbook.Sheets("Summary").Select
        
    End Sub
    I need this to do 2 things differently tho.

    #1) While both "Cover Sheet" and "Back Page" can print from their preset print areas, I need the "Summary" sheet to only print the selection from B2:P(lastrow).
    I was thinking this could be implemented somehow by the following...
    Dim lastRow As Long
    Dim printRng As Long
    
    'finds last used row # in my column B, starting from 2nd row, for reference in printRng
    
    With ActiveSheet
        lastRow = .Range("B2" & .Rows.Count).End(xlUp).Row
    End With
    For i = lastRow To 1 Step -1
        If Cells(i, "B2").Text <> "" Then
            Exit For
        End If
    Next i
    
    lastRow = i
    
    'creates my print range variable
    
    printRng = "B2" & ":" & "P" & lastRow
    But How do I get something like this to fit into my current sheet array selection?

    #2) I'd also like to supply a inputbox for the user to be able to name this PDF something unique, and have it automatically saved within the same folder as the Active.Workbook.

    Which one of you geniuses are up for this challenge?

    All joking aside, I greatly appreciate any help or guidance that you guys can possibly provide me. Not only will I give you a 1,000 good karma points, I will also not forget to provide the "Reputation" that you deserve.

    Mike in Cali
    Last edited by xlyfe; 04-25-2018 at 02:17 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Autofill Data based off current sheet lastrow not first sheet lastrow
    By bcarroll76 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-06-2018, 06:26 PM
  2. Copy specific range from multiple sheet into single sheet
    By qiyusi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2018, 12:24 AM
  3. [SOLVED] Summary of specific data from an export on another sheet
    By oneyejack77 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-22-2017, 11:54 AM
  4. [SOLVED] Export data from sheet to sheet based on single criteria
    By WHATSXL in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2017, 01:31 PM
  5. Replies: 2
    Last Post: 05-24-2016, 11:44 AM
  6. [SOLVED] Trying to move specific range to a specific sheet based on data in certain column
    By cain2011 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-01-2013, 08:09 AM
  7. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1