VBA Macro take cell value from multiple files and populate the values in a different workbook.

[agohir]

Hi Everyone,

I have multiple files with the sheet name "Admin Costs" with varying row numbers in each file.

I want to take the value from Cell A2 and paste that value in a different workbook. But the tricky part is that I want to past that value equal to the number of rows I have in Column A from the corresponding file's sheet "Admin Costs".

I'm assuming the way a code would work is a VBA macro will open each file and read the value in cell A2 (save it as string?) and Also read the number of rows in each file sheet "Admin Costs" and copy paste A2 value times the number of rows in sheet "Admin Costs" in a different workbook named "Template tool" that I have open.

Can anyone help on how to create the code or give a quick sample code to start with.

Help/feedback highly appreciated.

 

[JLGWhiz]

This assumes the wkbk "Template tool" is in the same directory as your other workbooks and that it will host the code for the procedure.

fPath = ThisWorkbook.Path
Set sh1 = ThisWorkbook.Sheets("Admin Costs")
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            On Error Resume Next
                Set sh2 = wb.Sheets("Admin Costs")
                If Err.Number = 0 Then
                    sh1.Cells(Rows.Count, 1).End(xlUp)(2).Resize(sh2.UsedRange.Rows.Count - 1, 1) = sh2.Range("A2").Value
                End If
            On Error GoTo 0
            wb.Close False
            Set wb = Nothing
            Set sh2 = Nothing
        End If
        fName = Dir
    Loop
Beep
MsgBox "Done"
End Sub