Hello everybody. I need some help with the code below.
What I need is this: I have a folder (mDir). In this folder I have several excel files that end with different strings (VB20, NB20 etc). I have a master Workbook (in another folder, not connected to the folder of the excel files) and I want to compare the 4 above mentioned strings (VB20,NB20) with the name of the sheets in my Workbook. If there is a match, then I want Excel to copy the sheet VB20 in the file that ends with "VB20.xlsx". And then copy Sheet NB20 to the file that ends with "NB20.xlsx" and so on. There are many files in the folder, but the end of the file name is always unique. I have the below code, but the code doesn't find any match between the Sheet and the File Name. Any ideas?
What I need is this: I have a folder (mDir). In this folder I have several excel files that end with different strings (VB20, NB20 etc). I have a master Workbook (in another folder, not connected to the folder of the excel files) and I want to compare the 4 above mentioned strings (VB20,NB20) with the name of the sheets in my Workbook. If there is a match, then I want Excel to copy the sheet VB20 in the file that ends with "VB20.xlsx". And then copy Sheet NB20 to the file that ends with "NB20.xlsx" and so on. There are many files in the folder, but the end of the file name is always unique. I have the below code, but the code doesn't find any match between the Sheet and the File Name. Any ideas?
VBA Code:
Sub sheetCompare()
Dim i As Integer
Dim mDirs As String
Dim path As String
Dim MasterFile As Variant, SrcFile As Variant
Dim file As Variant
Dim wb As Workbook
Dim datevar As Variant
Set wb = ThisWorkbook
MasterFile = ActiveWorkbook.Name
mDirs = "C:\Users\me\Desktop\Test\Split\"
file = Dir(mDirs)
While (file <> "")
path = mDirs + file
Workbooks.Open (path)
SrcFile = ActiveWorkbook.Name
datevar = Right(file, 9)
datevar2 = Left(datevar, 4)
For i = 1 To Workbooks(MasterFile).Sheets.Count
If datevar2 = Workbooks(MasterFile).Sheets(i).Name Then
wb.Activate
Sheets(i).Copy Before:=Workbooks(file).Sheets(1)
Workbooks(file).Close SaveChanges:=True
End If
Next i
Workbooks(file).Close (False)
file = Dir
Wend
End Sub