macro to open every file in a folder and change Data in specific cells?

[steve400243]

Hello I have several hundred files in a folder on My Desktop with a general name 'New Folder'. I am looking for a way to open each file and input the following into the noted cells in each file. Thanks for looking and any help provided. I also provided an example of one of the files with the correct formulas in the mentioned cells.

in cell H5 for each file -

=IF(H4="","-",H4+6)

in cell H6 for each file -

=IF(H4="","-",H4+11)

in cell H7 for each file -

=IF(H4="","-",H4+15)

YM UPWARD 2080.xlsx
	A	B	C	D	E	F	G	H
1	Packet Received:
2	ETA Date:		01/17/22			Container Released:
3	Master B/L:		YMLUW236642080			Received Container:
4	Vessel		YM UPWARD			Devanned On:
5	Container#:		YMLU9536528			Storage - 7th Day:		-
6	Intensive Exam Date:					12th day:		-
7	VACIS Exam Date:					GO Date:		-
8	Customs Exam Date:
9
CFS SHEET

Cell Formulas
Range	Formula
H5	H5	=IF(H4="","-",H4+6)
H6	H6	=IF(H4="","-",H4+11)
H7	H7	=IF(H4="","-",H4+15)

 

[AC PORTA VIA]

try this

Sub OPEN_ALL_FILES_IN_FOLDER_RUN_MACRO()

Dim Path As String
Path = "C:\Users\xxxxx\Desktop\New Folder\"
Dim fileName As String

' ANY EXCEL FILE
fileName = Dir(Path & "*.xls*")

Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While fileName <> ""
Workbooks.Open Path & fileName

' *****************************************************add code to run on each workbook in the folder********************************************
    Range("H5").FormulaR1C1 = "=IF(R[-1]C="""",""-"",R[-1]C+6)"
    Range("H6").FormulaR1C1 = "=IF(R[-2]C="""",""-"",R[-2]C+11)"
    Range("H7").FormulaR1C1 = "=IF(R[-3]C="""",""-"",R[-3]C+15)"
' ****************************************************end code to run on each workbook in the folder*********************************************
Workbooks(fileName).Close True
fileName = Dir()
Loop
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[steve400243]

try this

Sub OPEN_ALL_FILES_IN_FOLDER_RUN_MACRO()

Dim Path As String
Path = "C:\Users\xxxxx\Desktop\New Folder\"
Dim fileName As String

' ANY EXCEL FILE
fileName = Dir(Path & "*.xls*")

Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While fileName <> ""
Workbooks.Open Path & fileName

' *****************************************************add code to run on each workbook in the folder********************************************
    Range("H5").FormulaR1C1 = "=IF(R[-1]C="""",""-"",R[-1]C+6)"
    Range("H6").FormulaR1C1 = "=IF(R[-2]C="""",""-"",R[-2]C+11)"
    Range("H7").FormulaR1C1 = "=IF(R[-3]C="""",""-"",R[-3]C+15)"
' ****************************************************end code to run on each workbook in the folder*********************************************
Workbooks(fileName).Close True
fileName = Dir()
Loop
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Thanks for your time in looking at this. I did not ask where I would put this code to run? Would I create just a blank excel file and place in the folder?

 

[AC PORTA VIA]

you can have excel file with macro anywhere you want just make sure you have correct path to your folder on below line
Path = "C:\Users\xxxxx\Desktop\New Folder\"

 

[steve400243]

you can have excel file with macro anywhere you want just make sure you have correct path to your folder on below line
Path = "C:\Users\xxxxx\Desktop\New Folder\"

Got it thanks, Just needed to slow down a bit. Works as needed after changing the file path. Many Thanks!

 

[AC PORTA VIA]

glad it works for you