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]

Yes, remove "set" from the beginning please.

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

 

[Nordicrx8]

Yes, remove "set" from the beginning please.

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

That cleared the compile error, but now it's popping the bellow error message. It's finding and pasting the top data, but it pops the message on the new code. thoughts?

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

 

[Gokhan Aycan]

Does the Service Level workbook actually have the paranthesis for Month and Market Name?

 

[Nordicrx8]

Does the Service Level workbook actually have the paranthesis for Month and Market Name?

no, I added them in for clarity, but it looks like it added confusion! haha

 

[Gokhan Aycan]

No problem, insert this one instead:

Set wsDestination = Workbooks("Service Level Miss " & wsSource.Range("K2").Value & " MTD " & wsSource.Range("E2").Value).Worksheets(wsSource.Range("B1").Value)

 

[Nordicrx8]

No problem, insert this one instead:

Set wsDestination = Workbooks("Service Level Miss " & wsSource.Range("K2").Value & " MTD " & wsSource.Range("E2").Value).Worksheets(wsSource.Range("B1").Value)

Woooo! It found the tab and pasted the data! Though it looks like it's copying over the formulas, not pasting as values. It's also only copying over row K34:AD24 from the master sheet, and pasting the row twice in the Market sheet in rows A55:T55 and A56:T66. We're so close!!

 

[Gokhan Aycan]

If you want only values to be copied then, remove the copy line and insert these 2 lines:

wsSource.Range("K13:AD38").Copy
wsDestination.Range("A35:T60").PasteSpecial Paste:=xlPasteValues

I can't say anything about what is being copied, since you have provided the ranges.

 

[Nordicrx8]

If you want only values to be copied then, remove the copy line and insert these 2 lines:

wsSource.Range("K13:AD38").Copy
wsDestination.Range("A35:T60").PasteSpecial Paste:=xlPasteValues

I can't say anything about what is being copied, since you have provided the ranges.

that fixed both issues! thank you so much for all your help!!