VBA: Find Open Workbook, Find Tab Name, and Paste Data

[Nordicrx8]

Hi All!

I made a previous post on this topic, but I don’t think I was clear enough on my request so I decided to make a new post.

I have two workbooks:

MASTER FILE
Name: SLMR Master – All Regions
Description: This workbook takes data from a dozen sources and transforms it for output to another workbook based on which Market is selected.
Market Name Location: E2
Current Month Location: K2
Current Date Location: E6
Name of Tab to search location: B1

Market Workbooks
Name: Service Level Miss (MONTH) MTD (Market Name)
Description: This is an example of a Market workbook. Both “September” and “New England” are variables. This workbook has several main tabs, along with a tab for each day, labeled in order. (EX: Sep 01, Sep 02, Sep 03, etc)

Once it finds the matching Workbook, I needs to find the correct tab, which mirror the date. The location for this tab name is on the master file, "main" tab, in cell B1.

I want it to copy data from this location:

Workbook Name: SLMR Master
Tab Name: Main
Data Copy Location: K13:AD38
Tab Name Location: B1

To Location:

Workbook Name: Service Level Miss (MONTH) MTD (Market Name)
Tab Name: VARIABLE (Tab name is on the master file, "main" tab, in cell B1)

Data Paste Location: A35:T60

Here is the code I am currently using (provided by another user) which is able to find the correct open workbook, but I need to alter it so also finds the correct tab in the matching workbook. I’m currently using this code to copy paste another data set – it’s not set up for above requirement.

I've tried a few alterations to the below code and I'm not able to get it working. Any help would be greatly appreciated - I know this is a big ask! It's the final part of my project, and I'll be officially done with it!

Sub CP()
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("e7"), wb1.Sheets("MTD Template").Range("B2:B34"), 0)
        If IsNumeric(fm) Then
            wb1.Sheets("MTD Template").Range("C" & fm + 1 & ":AA" & fm + 1).Value = .Range("F7:AD7").Value
        End If
                fm = Application.Match(.Range("e6"), wb1.Sheets("MTD Template").Range("B2:B34"), 0)
        If IsNumeric(fm) Then
            wb1.Sheets("MTD Template").Range("C" & fm + 1 & ":AA" & fm + 1).Value = .Range("F6:AD6").Value
        End If
                fm = Application.Match(.Range("e10"), wb1.Sheets("MTD Template").Range("B36:B69"), 0)
         If IsNumeric(fm) Then
            wb1.Sheets("MTD Template").Range("C" & fm + 35 & ":AA" & fm + 35).Value = .Range("F10:AD10").Value
        End If
                fm = Application.Match(.Range("e11"), wb1.Sheets("MTD Template").Range("B36:B69"), 0)
        If IsNumeric(fm) Then
            wb1.Sheets("MTD Template").Range("C" & fm + 35 & ":AA" & fm + 35).Value = .Range("F11:AD11").Value
        End If
End With

Exit Sub


skip:
If Err.Number = 9 Then
    MsgBox "No matching SLM Market Workbook found"
Else
    MsgBox Err.Description
End If

End Sub

 

[Gokhan Aycan]

Is the source file named "SLMR Master – All Regions" or "SLMR Master"? Are these 2 different workbooks?

 

[Nordicrx8]

Is the source file named "SLMR Master – All Regions" or "SLMR Master"? Are these 2 different workbooks?

Whoops - let me edit my post, good catch, and sorry about that!

 

[Gokhan Aycan]

Another question: What to reference for "MONTH" and "Market Name"?

 

[Nordicrx8]

Alright - looks like you can't edit a post after ten minutes have elapsed.

the name of the file is "SLMR Master - All Regions" they are not two separate workbooks - they are one in the same.

 

[Nordicrx8]

Another question: What to reference for "MONTH" and "Market Name"?

Its referencing the Master file for Month And Market Name, in K2 and E2, respectively.

MASTER FILE
Name: SLMR Master – All Regions
Description: This workbook takes data from a dozen sources and transforms it for output to another workbook based on which Market is selected.
Market Name Location: E2
Current Month Location: K2

 

[Gokhan Aycan]

Also, these 2 workbooks are already open when you run the macro, so actually we don't need to find them but only provide a reference to them, which by the way is already in your posted code

 

[Nordicrx8]

Also, these 2 workbooks are already open when you run the macro, so actually we don't need to find them but only provide a reference to them, which by the way is already in your posted code

Yup! The two workbooks *Should* already be open, so it just needs to find the open workbook, find the correct tab, and paste the data

 

[Gokhan Aycan]

Insert this between "End With" and "Exit Sub" in the code you posted.

Dim wsSource as Worksheet
Dim wbDestination as Worksheet

Set wsSource = Workbooks("SLMR Master - All Regions").Worksheets("Main")
Set wsDestination = Workbooks("Service Level Miss (" & wsSource.Range("K2").Value & ") MTD (" & wsSource.Range("E2").Value & ")").Worksheets(wsSource.Range("B1").Value)

Set wsSource.Range("K13:AD38").Copy wsDestination.Range("A35:T60")

Set wsSource = Nothing
Set wsDestination = Nothing

 

[Nordicrx8]

Insert this between "End With" and "Exit Sub" in the code you posted.

Dim wsSource as Worksheet
Dim wbDestination as Worksheet

Set wsSource = Workbooks("SLMR Master - All Regions").Worksheets("Main")
Set wsDestination = Workbooks("Service Level Miss (" & wsSource.Range("K2").Value & ") MTD (" & wsSource.Range("E2").Value & ")").Worksheets(wsSource.Range("B1").Value)

Set wsSource.Range("K13:AD38").Copy wsDestination.Range("A35:T60")

Set wsSource = Nothing
Set wsDestination = Nothing

It's popping a compile/Syntax error for the below line:

Set wsSource.Range("K13:AD38").Copy wsDestination.Range("A35:T60")