Hi guys, I'm trying to figure out a way for my code, could you please help me? I'm trying to get some reference data from several workbooks( all in the same folder) and check if they are duplicate or not. I want to open the folder, get the reference ( which is always in cell A1), check if it is already in the range of previously checked references ( that is , in another separate workbook, where I will record all references) and if it isn't, add it there and start doing some computations with the data from the referenced sheet (irrelevant at this stage). This is because I may add files over time in the folder and I want to be able to run the code on all of them, without running it twice on the same one.
Here's the code
Sub Collect_sheets()
Dim Path As String
Dim bflag As Boolean
Dim sh As Worksheet
Dim wBook As Workbook
Dim wBookx As Workbook
Dim iRef As String
Dim xRange As Long
Path = "C:\Users\Desktop\ACASX\"
Filename = Dir(Path & "*.xlsx")
Set wBook = Workbooks("Book1.xlsm")
Do While Filename <> "" 'as long as there are files in the folder'
For i = 2 To 100
Workbooks.Open (Path & Filename), ReadOnly:=True 'open it'
iRef = Cells(1, 1) 'get the reference
x = wBook.Worksheets("Centralizer").Range("A" & Rows.Count).End(xlUp).Row 'check how long the existing range is
xRange = Application.WorksheetFunction.CountIf(ActiveSheet.Range("A2:A" & x), iRef) 'count if there are duplicates in the range
If xRange <=1 Then 'if that's a unique record, add it to the list
wBook.Worksheets("Centralizer").Cells(i, 1).Value = iRef
"Other computation shall happen here"
End If
Workbooks(Filename).Close
Filename = Dir()
Next i
Loop
End Sub
Many thanks! I attached some sample workbooks with the references in A1
Bookmarks