VBA - Search workbook tabs for a match, then paste data

[Nordicrx8]

Hi all!

I'm currently using the below code (truncated) for another process, and I want to alter it to include one more search criteria.

The below code is searching for an open workbook and doing a copy/paste into a specific tab. What I'm looking to do now, is in addition to searching for the open workbook, also search for a specific TAB of the workbook and copy/paste data.

Tab Name Location (what the code needs to search for)
Workbook: SLMR Master
Tab: Main
Cell: B1

Could someone help me alter the below code to have it also search for the correct tab?

thanks so much all!

S
Sub Monthly_CP()
'
    Dim LoopCounter As Long
    Dim wb1         As Workbook
    Dim fm
On Error GoTo skip:
    With Workbooks("SLMR Master - All Regions").Sheets("Main")
        Set wb1 = Workbooks("Service Level Miss " & .Range("k2") & " MTD " & .Range("e2"))
'
        fm = Application.Match(.Range("E14"), wb1.Sheets("MTD performance").Range("A4:A40"), 0)
        If IsNumeric(fm) Then wb1.Sheets("MTD Performance").Range("B" & fm + 3 & ":C" & fm + 3).Value = .Range("F14:G14").Value

    End With
    Exit Sub


skip:
If Err.Number = 9 Then
    MsgBox "No matching SLM Market Workbook found. Please ensure the correct workbook is open and try again. If correct, please refer to the troubleshooting guide on the instructions tab."
Else
    MsgBox Err.Description
End If

End Sub

 

[Marc L]

Hi, a VBA demonstration for starters :​

Function ExistWorkbookSheet(WBOOK, WSHEET) As Boolean
                          V = Evaluate("ISREF('[" & WBOOK & "]" & WSHEET & "'!A1)")
         ExistWorkbookSheet = IIf(IsError(V), False, V)
End Function

Sub Demo1()
    If ExistWorkbookSheet("SLMR Master.xlsx", "Main") Then
        MsgBox "Ok !"
    Else
        MsgBox "Not found !"
    End If
End Sub

 

[Nordicrx8]

Hi, a VBA demonstration for starters :​

Function ExistWorkbookSheet(WBOOK, WSHEET) As Boolean
                          V = Evaluate("ISREF('[" & WBOOK & "]" & WSHEET & "'!A1)")
         ExistWorkbookSheet = IIf(IsError(V), False, V)
End Function

Sub Demo1()
    If ExistWorkbookSheet("SLMR Master.xlsx", "Main") Then
        MsgBox "Ok !"
    Else
        MsgBox "Not found !"
    End If
End Sub

Hi There!

Thanks for your response! I believe I still need to have the below code (or some variation of it) as it's not doing the search for the tab where the button is located, but in a separate workbook, as indicated by the below workbook search. I'm still new to VBA, and trying to piece all this together - any help with getting this working is appreciated!

With Workbooks("SLMR Master - All Regions").Sheets("Main")
        Set wb1 = Workbooks("Service Level Miss " & .Range("k2") & " MTD " & .Range("e2"))

 

[Nordicrx8]

Good Morning all! Bump to top - still looking for assistance on this one if possible! thank you!

 

[Gokhan Aycan]

What button are you talking about? Need to be more specific.

This function will return a worksheet object if a matching workbook/worksheet pair is found in the same instance of Excel.

Change all instances of "Find_WB_Tab" to your liking if it doesn't suit your taste.

Private Function Find_WB_Tab(ByVal strWb As String, ByVal strWs As String) As Object

    Set Find_WB_Tab = Nothing

    Dim wb As Workbook
    Dim ws As Worksheet
    
    Dim fso As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    On Error Resume Next
    
    For Each wb In Application.Workbooks
    
        If UCase(fso.GetBaseName(wb.FullName)) = Ucase(strWb) Then
            Set wb = Workbooks(strWb)
            If Err.Number <> 0 Then Set wb = Workbooks(fso.GetFileName(wb.FullName))
            Exit For
        End If
    
    Next wb
    
    Set ws = wb.Worksheets(strWs)
    
    On Error GoTo 0
    
    Set fso = Nothing
    
    Set Find_WB_Tab = ws
    
End Function

You can use the function like this:

Dim ws As Worksheet
    
Set ws = Find_WB_Tab("SLMR Master", "Main")

You can then check if the pair is found by:

If Not (ws Is Nothing) Then
    ' Your code here if found
End If

or perhaps (exit sub if not found):

If ws is Nothing then Exit Sub

Finally, this will, for example, enter "abc" in cell A1:

ws.Range("A1").Value = "abc"