Update cell value for all files in a folder

[MathiasDW87]

I'm trying to find a VBA code to update one specific cell (B14) in the first sheet for all files in a folder. The macro should update this cell B14 with the value in cell H1 in my macro file.
The folder path, where all files are located that should be updated, is in cell B4.
So shortly, cell H1 from my macro file should be copied to cell B4 for all files in a folder.

[MathiasDW87]

Added the macro file

[Paul_Hossler]

This is not tested and assumed only XLSX files and the destimation cell is B14 (you last sentence in #1 says B4)




Option Explicit


'I 'm trying to find a VBA code to update one specific cell (B14) in the first sheet for all files in a folder.
'The macro should update this cell B14 with the value in cell H1 in my macro file.
'The folder path, where all files are located that should be updated, is in cell B4.
'So shortly, cell H1 from my macro file should be copied to cell B4 for all files in a folder


Sub H1_to_B14()
Dim sFile As String
Dim strPath As String
Dim H1 As Variant
Dim wb2 As Workbook ' I like to be VERY specific when having multiple workbooks open




strPath = Blad1.Cells(4, 2).Value
If Right(strPath, 1) <> Application.PathSeparator Then strPath = strPath & Application.PathSeparator

H1 = Blad1.Range("H1").Value

sFile = Dir(strPath & "*.xlsx")

Do While sFile <> ""
If sFile = ThisWorkbook.Name Then GoTo GetNextFile


Workbooks.Open Filename:=strPath & sFile
Set wb2 = ActiveWorkbook

wb2.Worksheets(1).Range("B14").Value = H1

wb2.Save
wb2.Close

ThisWorkbook.Activate

sFile = Dir

GetNextFile:
Loop




End Sub

[MathiasDW87]

Many thanks Paul! This is working.