Hi!
I have a master tracker file includes usernames(in column A) with corresponding dates at the top(starts form C) in every month as listed.There are around 20 odd office files generated daily, and users working on each file creates ID and that is recorded in column BI.So I need to track the amount of work done by each user just by taking the count of usernames in BI on that day and update the tracker by matching the corresponding date.
I tried to loop through the files and couldnt figure further logic to record the count.
Your guidance/suggestion would be helpful.
Dim strDirContainingFiles As String, strFile As String, strFilePath As String
Dim wbkDst As Workbook, wbkSrc As Workbook
Dim wksDst As Worksheet, wksSrc As Worksheet
Dim c1, c2, c3, c4 As Long 'counter for each user
Dim lngIdx As Long, lngSrcLastRow As Long, _
lngSrcLastCol As Long, lngDstLastRow As Long, _
lngDstLastCol As Long, lngDstFirstFileRow As Long
Dim rngSrc As Range, rngDst As Range, rngFile As Range
Dim colFileNames As Collection
Set colFileNames = New Collection
b = InputBox("Enter the folder name")
a = "F\Shopping\Specialists\Master Copy\Daily list\Split files\Archive\"
'Set references up-front
strDirContainingFiles = a & b
Set wbkDst = ThisWorkbook '<~ Dst is short for destination
Set wksDst = ActiveSheet
'Store all of the file names in a collection
strFile = Dir(strDirContainingFiles & "\*.xls*")
If Len(strFile) <= 0 Then
MsgBox "Files not found"
Exit Sub
End If
Do While Len(strFile) > 0
colFileNames.Add Item:=strFile
strFile = Dir
Loop
''CHECKPOINT: make sure colFileNames has the file names
' Dim varDebug As Variant
' For Each varDebug In colFileNames
' Debug.Print varDebug
' Next varDebug
'start looping through the "source" files
For lngIdx = 1 To colFileNames.Count
'Assign the file path
strFilePath = strDirContainingFiles & "\" & colFileNames(lngIdx)
'Open the workbook and store a reference to the data sheet
Application.DisplayAlerts = False
Set wbkSrc = Workbooks.Open(strFilePath, ReadOnly:=True, corruptload:=xlRepairFile)
Application.DisplayAlerts = True
Set wksSrc = wbkSrc.Worksheets("SHOPPING LISTING") ' change based on your Sheet name
wksSrc.Columns.EntireColumn.Hidden = False