Find sheet in any open workbook?

[Bonacci8]

I want to prompt the user to enter a sheet name in an input box and then have the macro search for that sheet name in any open workbook. Once it finds the sheet name I want it to run some minor code to format a pivot table on that sheet. As I know just enough vba to hurt myself....I can't seem to figure out how to search all the open workbooks. The reason for this approach is I want the user of one spreadsheet to be ale to hit a button that runs the macro,formats the pivot and the paste values it into her workbook. The person she gets the data seems to always change the formatting and the file name but the sheet name remains constant. I was thinking by avoiding having to input teh workbook name that it would allow the same code to be used on whichever sheet name was entered and avoid having to enter a workbook and sheet name. Any thoughts or is this just a bad idea?

 

[CharlesH]

Hi,

This may get you started.

Sub test()
Application.DisplayAlerts = False
Dim wb As Workbook
Dim MySheet As Worksheet
Dim Response As String
  Response = Application.InputBox("Enter Sheet Name")
'Loop through each open workbook
For Each wb In Workbooks
    If Not (wb.Name = ThisWorkbook.Name) Then
        wb.Activate
        For Each MySheet In Worksheets '' loop thru each worksheet
            If MySheet.Name = "Response" Then
               ''' Your code'''
            End If
        Next
    End If
Next wb
End Sub

 

[Bonacci8]

Thanks Charles, Going to give it a try today. Appreciate the help!!!

Pete

 

[Bonacci8]

Code worked great!, one modification had to remove the quotes on the string name in the.... If MySheet.Name = "Response" Then .... line.

One other question is if the user enters nothing in the input box how would I test that to exit the sub?

Thanks Again!!!

 

[CharlesH]

Hi,

See if this helps.

Sub test()
Application.DisplayAlerts = False
Dim wb As Workbook
Dim MySheet As Worksheet
Dim Response As Variant
  Response = Application.InputBox("Enter Sheet Name")
'Loop through each open workbook
If Response = "" Then
    Exit Sub
ElseIf Response = False Then '' user click Cancel
    Exit Sub
End If
For Each wb In Workbooks
    If Not (wb.Name = ThisWorkbook.Name) Then
        wb.Activate
        For Each MySheet In Worksheets '' loop thru each worksheet
            If MySheet.Name = Response Then
               ''' Your code'''
            End If
        Next
    End If
Next wb
End Sub
[/codde]

 

[Bonacci8]

Works like a charm, thanks!!!

 

[CharlesH]

thanks for the update.