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

[Raceman]

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.

 

[JLGWhiz]

And what did the error message say?

 

[Raceman]

Run-time error '1004' Application -defined or object -defined error

 

[JLGWhiz]

I don't see any problem with the code you posted and I cannot duplicate the error. Look for incorrectly spelled words and/or variable names. Also look for foreign data such as extra apostrophes, periods, commas, etc. I have had occasion to delete the line an retype it back in and the problem would go away. But, like I said, the code looks good and I can't trouble shoot it from here to find any other cause. You could also check to be sure there is data in the sheet in that workbook.
Regards, JLG

 

[Raceman]

I looked for errors like you suggested and didn't find any. I tried to retype the code on the line where the code gets stuck. I got as far as : "For Each c In Msh.Range (" and then the macro helper pops up on the screen. It seems to want more info for the Range. The macro helper (pop up)says, "Range(Cell1, [Cell2])As Range". In your code you have : "For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(x1Up))" . Could it be looking for a Range such as "B:B" rather than "B2"?

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( 'here is the point where the code helper pops up and tells me "Range(Cell1,[Cell2])..."
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

 

[JLGWhiz]

What you are looking at is the intellisense display that is just a reminder of what options you have. But it is a good sign when it pops up, because that means that what you have so far is good. The code that I had sets a range from cell B2 to the last cell with datat in that column. If you want to test it for validity, then put a message box in like this:

MsgBox Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(xlUp)).Address

just before the For...Next statement and it should display the in a $B$2:$B$100 format/

 

[Raceman]

I wasn't able to do this , compile errors. Not sure exactly what you meant by just before the For. I tried that and had no success. If you are positive that this line is correct,

 For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(x1Up))

then I guess I have to try to go step by step (again) and see if I missed anything. Also, my "execute" workbook is located in the C:\Master\ folder, not the output folder. Is that correct?

 

[JLGWhiz]

Well, the code can be technically correct, but if your data or worksheets vary from what the code was based on, then it could produce that error. That is what the 'Application' and 'Object Defined' terms are referring to. There is a logic gap somewhere and I cannot see it from here. That is why it is good practice to post a screen shot or a link to your sheets if possible. You just might have stumbeld across the answer to your error message with the location of the execute file.

 

[Raceman]

In your code is wb3 correct? and what is it referring to?

 

[JLGWhiz]

I stuck it in originally for the 'execute' workbook, but it wasn't needed. It can be deleted from the declarations.

 

[JLGWhiz]

I believe I found the culprit. It was a coding error on my part. I have the workbook being closed before the loop is finished. You need to substitute this modification of the inner For..Next loop. It just basically moves the closing of the workbook outside the inner loop.

    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
        Next
       [COLOR=#b22222] wb2.Close True
[/COLOR]        Set wb2 = Nothing
    Next