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

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
144
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
 
Yes, remove "set" from the beginning please.

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Yes, remove "set" from the beginning please.

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

VBA Code:
skip:
If Err.Number = 9 Then
    MsgBox "No matching SLM Market Workbook found."
Else
    MsgBox Err.Description
End If
 
Upvote 0
Does the Service Level workbook actually have the paranthesis for Month and Market Name?
 
Upvote 0
No problem, insert this one instead:

VBA Code:
Set wsDestination = Workbooks("Service Level Miss " & wsSource.Range("K2").Value & " MTD " & wsSource.Range("E2").Value).Worksheets(wsSource.Range("B1").Value)
 
Upvote 0
No problem, insert this one instead:

VBA Code:
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!!
 
Upvote 0
If you want only values to be copied then, remove the copy line and insert these 2 lines:

VBA Code:
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.
 
Upvote 0
If you want only values to be copied then, remove the copy line and insert these 2 lines:

VBA Code:
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!!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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