Copy date from one workbook into other workbooks located in another folder, there is a common reference no. between them

Raceman

Board Regular
Joined
Mar 11, 2010
Messages
64
I need to pull the date from one workbook called “log” (the date is in column B) and display it in another workbook (one of which is) called “MrepoA” .
I will have approx 8 workbooks in a folder called: MrepoA, MrepoB, MrepoC , etc. that need to receive date information from the “log” workbook (which is located in another folder).
We can relate the 2 different types of workbooks (log and Mrepo) with a number that is common between them. This number is called “log number”. The log number in the “log” file in column B. And the log number in all of the “Mrepo” files is in column E

After the macro is run I should find that the date from the “log” has now been copied into all of the Mrepo files in a newly inserted column just before column F, based on the reference of “log number” which is common between files.

I don't mind introducing a 3rd workbook in the folder where the "Mrepo" files are located and putting an "execute" button in this "Execute" workbook to start the macro. I will be introducing new Mrepo files in this folder each month so having a 3rd workbook which can stay in the folder might be helpful.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I believe that if you read your post over very carefully, you will understand why no one has responded to it. It is not clear whether MrepoA thru H are folders or workbooks. If they are not workbooks, then what are the workbook names and what is the path? The code is simple enough.
Code:
Workbooks(1).Sheets(1).Range("B2").copy Workbooks(2).Sheets(1).Range("E2")
With variables to handle the different workbooks, sheets and ranges, of course. Try to provide as much detail about the file attributes as possible, not necessarily the content, but where they are, how they are named, including sheets.
 
Last edited:
Upvote 0
Sorry, I'll make it clearer: MrepoA thru H are Workbooks in a folder. The workbook "log" is located in C:\Master and the workbooks: MrepoA thru H are located in: C:\Master\outputfile
 
Upvote 0
And I forgot, the workbook "execute" which has a button that will run the macro should be located in C:\Master\outputfile .
 
Upvote 0
need to pull the date from one workbook called “log” (the date is in column B)
The log number in the “log” file in column B


these statements appear to conflict with each other. Are both the log number and the date in column B? Please clarify.
 
Upvote 0
I apologize for forgetting to include this info.

In the "log" workbook:
log number is in column B and date is in column C

In the MrepoA-H workbooks: log number is in column E
 
Upvote 0
You will need to edit the title line if your button is not an Acrtive-X button and/or is not CommandButton1.
If sheet index positions vary in the Mrepo workbooks, the code fails. Same if you use the sheet name and it varies.
This code can be run from the "execute' workbook. If you use an Active-X button, then this code should be copied into the sheet code module that has the button. If the button is a forms control button, then the title line of the procedure should be changed to remove the _Click, copy the procedure to the standard code module and then right click the button to attach the macro to it.
Code:
Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, Msh As Worksheet, sh As Worksheet
Dim sPath As String, Swb As Variant, i As Long, c As Range, fLoc As Range
Set wb1 = Workbooks.Open("C:\Master\log.xlsx")
Set Msh = wb1.Sheets(1) 'Edit Sheet name
sPath = "C:\Master|outputfile\"
Swb = Array("MrepoA.xlsx", "MrepoB.xlsx", "MrepoC.xlsx", "MrepD.xlsx", "MrepoE.xlsx", "MrepoF.xlsx", _
    "MrepoG.xlsx", "MrepoH.xlsx")
    For i = 0 To 7
        Set wb2 = Workbooks.Open(sPath & Swb(i))
        Set sh = wb2.Sheets(1) 'Edit sheet name
        sh.Columns("F").Insert
        For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(xlUp))
            Set fLoc = sh.Range("E:E").Find(c.Value, , xlValues)
                If Not fLoc Is Nothing Then
                    fLoc.Offset(0, 1) = c.Offset(0, 1)
                End If
  wb2.Close True
        Next
 Set wb2 = Nothing
    Next
wb1.Close False
Set wb1 = Nothing
Set Msh = Nothing
Set sh = Nothing
Set fLoc = Nothing
End Sub
 
Upvote 0
Thanks for the quick responses. I tried the code and having some problems. The error message is "Run time error '438' and the Set MSH = wb1.Logbook line is highlighted when I clicked debug. Also I wondered if wb3 in the code is correct?

HTML:
Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, Msh As Worksheet, sh As Worksheet
Dim sPath As String, Swb As Variant, i As Long, c As Range, fLoc As Range
Set wb1 = Workbooks.Open("C:\Master\log.xls")
Set Msh = wb1.Logbook 'Edit Sheet name
sPath = "C:\Master\outputfile\"
Swb = Array("MrepoA.xls", "MrepoB.xls", "MrepoC.xls")
For i = 0 To 7
Set wb2 = Workbooks.Open(sPath & Swb(i))
Set sh = wb2.Sheets(1) 'Edit sheet name
sh.Columns("F").Insert
For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(xlUp))
Set fLoc = sh.Range("E:E").Find(c.Value, , xlValues)
If Not fLoc Is Nothing Then
fLoc.Offset(0, 1) = c.Offset(0, 1)
End If
wb2.Close True
Next
Set wb2 = Nothing
Next
wb1.Close False
Set wb1 = Nothing
Set Msh = Nothing
Set sh = Nothing
Set fLoc = Nothing
End Sub
 
Last edited:
Upvote 0
By this:
Code:
Set Msh = wb1.Logbook
I assume you meand this:
Code:
Set Msh = wb1.Sheets("Logbook")

You will also need to change this:
For i = 0 To 7
To This
For i = 0 To 2
Since you only include Three workbooks in the array.
 
Last edited:
Upvote 0
Ok thanks , I made the changes you suggested and adjusted sheet name on this line: Set sh = wb2.Sheets("Sheet1") and now seeing Runtime error '1004' and clicking debug highlights this line: For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(xlUp))

HTML:
Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook, Msh As Worksheet, sh As Worksheet
Dim sPath As String, Swb As Variant, i As Long, c As Range, fLoc As Range
Set wb1 = Workbooks.Open("C:\Master\log.xls")
Set Msh = wb1.Sheets("Logbook") 'Edit Sheet name
sPath = "C:\Master\outputfile\"
Swb = Array("MrepoA.xls", "MrepoB.xls", "MrepoC.xls")
For i = 0 To 2
Set wb2 = Workbooks.Open(sPath & Swb(i))
Set sh = wb2.Sheets("Sheet1") 'Edit sheet name
sh.Columns("F").Insert
For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(xlUp))
Set fLoc = sh.Range("E:E").Find(c.Value, , xlValues)
If Not fLoc Is Nothing Then
fLoc.Offset(0, 1) = c.Offset(0, 1)
End If
wb2.Close True
Next
Set wb2 = Nothing
Next
wb1.Close False
Set wb1 = Nothing
Set Msh = Nothing
Set sh = Nothing
Set fLoc = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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