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

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
87
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! :)


VBA Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
Hi, a VBA demonstration for starters :​
VBA Code:
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

Board Regular
Joined
Dec 10, 2015
Messages
87
Hi, a VBA demonstration for starters :​
VBA Code:
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!

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

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
87
Good Morning all! Bump to top - still looking for assistance on this one if possible! thank you!
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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:

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

You can then check if the pair is found by:

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

or perhaps (exit sub if not found):

VBA Code:
If ws is Nothing then Exit Sub

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

VBA Code:
ws.Range("A1").Value = "abc"
 

Forum statistics

Threads
1,148,253
Messages
5,745,679
Members
423,967
Latest member
malayaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top