+ Reply to Thread
Results 1 to 9 of 9

Print individual page by user input

Hybrid View

  1. #1
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Print individual page by user input

    I have this workbook with two tabs; sheet1"Employees", sheet2"schedule". On sheet2 there are 13 individual schedules with 28 day period. I'm trying to add a way the user can activate a button on sheet1 that ask for the user to input the month and only schedules with that month would print. Is anything like this possible? Or any other ideas to print an individual schedule. I've attached a stripped down workbook for your review.

    I posted here also with no response. This may not be able to be done. http://www.vbaexpress.com/forum/showthread.php?t=44390
    Attached Files Attached Files
    Last edited by RonNCmale; 11-17-2012 at 07:49 AM.

  2. #2
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Print individual page by user input

    I have found the following code but would like to enter text ie: month, instead of starting and ending number. Can this be done?


    Sub Print_All()
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim x As Variant
    Dim v As Variant
    
    i = InputBox("Please enter the Starting Record number")
    v = InputBox("Please enter the Ending Record number")
    x = i
    For i = x To v
    
    Range("n2").FormulaR1C1 = x
    Sheets("Schedule").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
    
    x = x + 1
    Next i
    End Sub

  3. #3
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Print individual page by user input

    Can anyone tell me what is the code for Macro to print selected sheet and pages based on input text.

    For example :
    in cell A1=Jan
    When I run the macro, it will print page 1

    in cell A1=Feb
    When I run the macro, it will print page 2

    A1 is manual typed text to determine which page to print

  4. #4
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Print individual page by user input

    I found the following module code that has a input box for entering two digits for a particular week to print. Is there a way to convert this to entering month ie: January, February, etc.





    Sub Print_Week_X()
        Dim rngPrint As Range, wkNum As String
        Dim rngFound As Range
        
        'clear any previous settings for the print area
        ActiveSheet.PageSetup.PrintArea = ""
        
        Application.ScreenUpdating = False
    
    tryagain:
    
        'get the week number to print
        On Error Resume Next
        
        Application.DisplayAlerts = False
        
        wkNum = ""
        wkNum = Application.InputBox("Enter the week number  as a two-digit number from 01 thru 12")
        
        On Error GoTo 0
        
        Application.DisplayAlerts = True
        
    '    If wkNum = "" Then GoTo endit:
        
        If Len(wkNum) < 2 Then
            MsgBox ("You must enter two digits for all week numbers")
            GoTo tryagain:
        End If
        
        Set rngFound = Columns(2).Find(what:=wkNum, After:=Cells(1, 2), LookIn:=xlValues _
            , lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False)
        
        If rngFound Is Nothing Then
            MsgBox "The week number was not found"
            GoTo endit:
        End If
        
        'MsgBox rngFound.Address
        
        With ActiveSheet
            .PageSetup.PrintArea = .Range("B" & rngFound.Row - 1, Range("Y" & rngFound.Row + 29)).Address
            .Print
            .PageSetup.PrintArea = ""
            .PageSetup.PrintArea = .Range(.Cells(rngFound.Row - 1, 26), .Cells(rngFound.Row + 29, 43)).Address
            .Print
        End With
        
    
    endit:
        Application.ScreenUpdating = True
    End Sub
    I also found this code to search by text that will locate where text was found, but need to combine codes to search for text, ie month and be able to print that sheet.

    Public Sub FindText()
    
    Dim ws As Worksheet
    
    Dim Found As Range
    
    Dim myText As String
    
    Dim FirstAddress As String
    
    Dim AddressStr As String
    
    myText = InputBox("Enter text to find")
    
    If myText = "" Then Exit Sub
    
    For Each ws In ThisWorkbook.Worksheets
    
        With ws
    
            Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
    
            If Not Found Is Nothing Then
    
                FirstAddress = Found.Address
    
                Do
    
                    AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
    
                    Set Found = .UsedRange.FindNext(Found)
    
                Loop While Not Found Is Nothing And Found.Address <> FirstAddress
    
            End If
    
        End With
    
    Next ws
    
    If Len(AddressStr) Then
    
        MsgBox AddressStr, vbOKOnly, myText & " found in these cells"
    
    Else:
    
        MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
    
    End If
    
    End Sub
    Last edited by RonNCmale; 11-18-2012 at 06:32 PM.

  5. #5
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Print individual page by user input

    I found the code to search the workbook by user input of text. It finds the report but I can't seem to find a way to print the page(s) it is found on. Any suggestions or help would be greatly appreciated.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Print individual page by user input

    Is it possible to write VBA code that if a word is found by using a userform search to be able print a preselected range? Example: User inputs "May" and the word is found on A 161 to have code then select AI 200 and print this selected area.
    "January" if found it would select the following range A1:AI40
    "February" if found it would select the following range A41:AI80
    "March" if found it would select the following range A81:AI120
    Etc.
    Last edited by RonNCmale; 11-27-2012 at 12:49 PM.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Print individual page by user input

    Hi Ron

    Name the ranges, for example, the Named Range for Jan=Schedule!R1C1:R40C35.

    Then add this line of code
    Sub FindAll()
       Dim strFind As String
       Dim wks As Worksheet
       Dim rngFound As Range
       Dim lngItems As Long
       strFind = InputBox(prompt:="Enter Month to find", Title:="Find what?")
       If Len(strFind) > 0 Then
          For Each wks In ActiveWorkbook.Worksheets
             If FindIt(wks, strFind, lngItems) = False Then Exit For
          Next wks
       End If
       MsgBox lngItems & " matches found"
       
       Range(strFind).PrintOut '<-----------Add this line
       
    End Sub
    Worked for me on your sample file.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  8. #8
    Registered User
    Join Date
    12-19-2009
    Location
    Goldsboro, N
    MS-Off Ver
    Excel 2003
    Posts
    21

    Re: Print individual page by user input

    Thanks alot Jaslake "the Guru", It works a treat. Thanks for responding and helping me out with this task.

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Print individual page by user input

    You're welcome...glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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