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]
Bookmarks