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.

 

[Raceman]

I can't seem to correct the error but have the following questions

For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(x1Up))
'is this correct, "for each C (C is the date column)"?
'Should "B2" be "B3" if my data starts in row 3?
'is Rows.Count, 2 correct?

 

[JLGWhiz]

I can't seem to correct the error but have the following questions

For Each c In Msh.Range("B2", Msh.Cells(Rows.Count, 2).End(x1Up))
'is this correct, "for each C (C is the date column)"?
'Should "B2" be "B3" if my data starts in row 3?
'is Rows.Count, 2 correct?

You can change the B2 to B3 to be consistent with your data range, although it wouldn't be the cause of an error as is. It just includes cell B2 data as a search item in column E. Yes the Rows.Count,2 is correct. It takes the cursor to the bottome of column B then the End(xlUp) takes the cursor back up that column to the first cell with data, which makes it the last cell of the range starting at B2, or B3 after you change it..

I think if you made the I suggested in post #20 that your error should have gone away.

 

[Raceman]

I didn't see your #20 post before my last reply, but made the change and still no help, I still get the same error. Just for reference, this is the code that I used:

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("data") 'Edit sheet name
sh.Columns("F").Insert
For Each c In Msh.Range("B3", 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
wb2.Close True
Set wb2 = Nothing
Next
wb1.Close False
Set wb1 = Nothing
Set Msh = Nothing
Set sh = Nothing
Set fLoc = Nothing
End Sub

 

[brian.wethington]

Hmmm

Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
Dim Msh As Worksheet, sh As Worksheet
Dim c As Range, fLoc As Range
Dim sPath As String, Swb() As Variant
Dim i As Integer

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 UBound(Swb)
    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
    Next
    wb2.Close True
    Set wb2 = Nothing
Next
wb1.Close False
Set wb1 = Nothing
Set Msh = Nothing
Set sh = Nothing
Set fLoc = Nothing
End Sub

 

[JLGWhiz]

I ran this code in a test set up using your file names and directory names. It ran without error, so I don't know what else I can do. Maybe somebody else can see something that I don't.

Sub CommandButton1_Click()
Dim wb1 As Workbook, wb2 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")
    For i = LBound(Swb) To UBound(Swb) 'will not need change if more files added
        Set wb2 = Workbooks.Open(sPath & Swb(i))
        Set sh = wb2.Sheets(1) 'Edit sheet name if not first sheet in workbook
        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
        Next
    wb2.Close True
    Set wb2 = Nothing
    Next
wb1.Close False
Set wb1 = Nothing
Set Msh = Nothing
Set sh = Nothing
Set fLoc = Nothing
End Sub

Regards, JLG

 

[Raceman]

Your code stopped on the same line, it won't go past :

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

 

[Raceman]

Thanks, I guess I will start from scratch, set up test folders and try to re-run it. I appreciate the time you are putting into this. I'll let you know what happens.

 

[Raceman]

the last reply of mine was directed to Brian. I ran your code and had the same issue. Thanks for looking at this too.

 

[Raceman]

Ok I set up new folders, and created dummy files and noticed the code worked only when all of my files had the extension .xlsx. When I tried to run the code with .xls it didn't work and the code stopped at the usual spot.

A couple more things please if you don't mind, and thanks much for your help, when the dates are moved over to the Mrepo workbooks the inserted column isn't formatted for date.

The last issue has to do with the sheet name of the Mrepo workbooks. When I begin this whole process each sheet in the Mrepo workbooks has a unique name. I need some code that would take all of the Mrepo workbooks and change their sheet name to "data" (they each have only one sheet that contains data). I can do it manually, but if I had another button, one that could be pushed first in the process that performs this task, then it would be much easier. I could push 2 buttons and the process would be complete.

 

[Raceman]

I'm just realizing that in my testing scenarios I can use .xlsx files, but in real life I need to work with .xls files. So is there any logical reason why I'm seeing problems with the code when I use .xls?