+ Reply to Thread
Results 1 to 8 of 8

Use Inputbox to find then select an offset range and print for ALL sheets

  1. #1
    Registered User
    Join Date
    11-19-2004
    Posts
    16

    Use Inputbox to find then select an offset range and print for ALL sheets

    My workbook has many sheets, each having essentially the same structure but the data of interest varies in location within the sheet by date ranges that start differently in each sheet. I'm trying to create a macro that will ask the user for a string that is then used to find the sheet location to create an offset defined range to print. This macro needs to print that range from each sheet within the workbook (except the first, summary sheet). Below is a simplified version of the code I'm trying unsuccessfully. Seems that everything I try just performs the same thing on one sheet for the number of times that there are sheets. Can't figure out what I am doing wrong? Any help is appreciated beyond words!

    [CODE]
    Sub Find_Then_Print_Offset_All_Sheets()
    Dim ws As Worksheet
    Dim FindString As String
    Dim Rng As Range

    FindString = InputBox("Enter a Search value")

    For Each ws In Worksheets

    If Trim(FindString) <> "" Then
    With ws.Range("A1:z100")
    Set Rng = .Find(What:=FindString, _
    After:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    If Not Rng Is Nothing Then
    Application.Goto Rng, True

    ActiveCell.Offset(0, -4).Range("A1:E16").Select
    ActiveCell.Activate
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveCell.Select

    Else
    MsgBox "Nothing found on this sheet"
    End If
    End With
    End If

    [\CODE]

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    There are quite a few issues you have going on which I tried to address.

    I have rewritten your code, inactivated the printout (i didn't want to print while testing), and incorporated a test debug line so you can see what is going on.

    Please Login or Register  to view this content.
    Last edited by stnkynts; 02-23-2015 at 11:31 AM. Reason: Edit: To add Select Case

  3. #3
    Registered User
    Join Date
    11-19-2004
    Posts
    16

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    stnkynts -
    thanks for the help - really appreciated. Maybe my explanation was not entirely clear, but understand that after the string is found, the cell containing the string is selected (it becomes the top right corner of what is printed) and used as a reference point to offset select a range that is then printed (the selection only is printed). Thus, my code had the selection that you said you had no idea what I was trying to do. Within that code was the relative range that I was trying to print which you said I did not specify. Does that explanation make better sense? Again, I appreciate your help!

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    Does that explanation make better sense?
    Yes and No. Let's say that on a sheet, any sheet, your find range is cell A10. What cell range do you want to print? What if the find range is Y90? (Note: Both the cells fit within your search range)

  5. #5
    Registered User
    Join Date
    11-19-2004
    Posts
    16

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    The string will always be found at the top right corner of the print range (so the A10 example would not work - string would never be there - see note below about range). So, instead if the string were found in I1 then the print range would be A1:I66. For your other example of Y90 it would be Q90:Y155. The search range in the example code was just something I threw in my simple code example... the real search range would b I1:K3000 (sorry for the confusion - my bad). thanks

  6. #6
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-19-2004
    Posts
    16

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    That's got it. Thank you such much for your patience. This will really help out a lot of folks at our office! Kudos.

  8. #8
    Registered User
    Join Date
    11-19-2004
    Posts
    16

    Re: Use Inputbox to find then select an offset range and print for ALL sheets

    Thanks for all the help.
    [SOLVED]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 02-04-2014, 01:57 PM
  2. [SOLVED] Select range via Inputbox
    By nakul2617 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-21-2013, 07:46 AM
  3. Trouble Using InputBox Method to Select Range from Other Sheets
    By pao_e_vinho in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2006, 06:48 AM
  4. Replies: 1
    Last Post: 05-15-2006, 04:10 AM
  5. [SOLVED] How to Select a relative range with Using "Find" and Offset()
    By Dennis in forum Excel General
    Replies: 7
    Last Post: 07-27-2005, 11:05 AM

Tags for this Thread

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