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

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
143
Office Version
  1. 365
Platform
  1. Windows
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!

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Is the source file named "SLMR Master – All Regions" or "SLMR Master"? Are these 2 different workbooks?
 
Upvote 0
Another question: What to reference for "MONTH" and "Market Name"?
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
Insert this between "End With" and "Exit Sub" in the code you posted.

VBA Code:
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
 
Upvote 0
Solution
Insert this between "End With" and "Exit Sub" in the code you posted.

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

VBA Code:
Set wsSource.Range("K13:AD38").Copy wsDestination.Range("A35:T60")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top