Posts by coyner

coyner · Apr 27th 2022

Thanks a million, works perfectly!

coyner · Apr 26th 2022

Hi,

I have the below code that, when pointed at a particular folder, will capture the following data in my check tab : file name, number of rows, number of columns. The final part i need help with is to find a header, say its "value", and sum the column, posting the total adjacent to each file name starting in cell d8. Code below. Any ideas how to do this easily?

Code

Sub CollectData()

    Dim fso As Object, xlFile As Object
    Dim sFolder$
    Dim r&, j&, k&
    
    '*
    Sheets("Check").Activate
    Range("F8:I50").ClearContents
    Range("A8:D50").Copy Range("F8")
    Range("A8:D50").ClearContents
    
    '*
     
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .InitialFileName = ThisWorkbook.path
        If .Show Then sFolder = .SelectedItems(1) Else Exit Sub
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each xlFile In fso.GetFolder(sFolder).Files
        With Workbooks.Open(xlFile.path, Password:="password")
            With .Sheets(1)
                j = .Cells(.Rows.Count, 1).End(xlUp).Row
                k = .Cells(1, Sheet1.Columns.Count).End(xlToLeft).Column
                             
                               
            End With
            .Close False
            
        End With
               
        r = r + 1
        Cells(r + 7, 1).Value = xlFile.Name
        Cells(r + 7, 2).Value = j
        Cells(r + 7, 3).Value = k
                
    ActiveWorkbook.Save
       
    Next
         

End Sub

Display More

Posts by coyner

VBA to sum column with particular header and capture in a master file

VBA to sum column with particular header and capture in a master file