VBA to open, change, save and close all files in DIR

Hello all,

I have a directory of one sheet workbooks that are all the same template and need to have their sheet renamed from "Sheet1" to whatever the filename is to prepare them for merging into one larger workbook.

I know the code I need to make the change is 'activeSheet.name = activeWorkbook.name' but since I can't use Application.FileSearch anymore I need to know how to run a module that will easily open the files in the directory one at a time, make the name change, save the file and move on to the next one. I don't need to step in at any point as all the files are already saved with the correct name and they're all getting the same change.

Since nothing I've found online so far has worked I'm basically starting from scratch with a new module.

Can someone walk me through the proper way to run the loop to do this?

Thanks/

Please Login or Register  to view this content.

change "E:\OF\' into the directory/folde/map you need.
change "New name" into the desired sheetname

Please Login or Register  to view this content.

Dang it snb, you're always one step ahead of me! ; )

@Dave

Apologies.
I had an omission in my code: the asterisk.
I fear you have 1 too: C: needs a backward slash

Besides it's nice to compare both methods: I try to prevent the screenfilckering by using 'Getobject'.
I think yours needs still application.screenupdating =False

@jdkoeppen

No you didn't, I did (forgot the asterisk). See the amended code in my first post.