macro to adjust files in directory....

  • Thread starter Thread starter Heidi
  • Start date Start date
H

Heidi

hi,

i have 500 files all with only one sheet (all named
dirferently) in a directory. i want to create a macro
that opens one file at a time changes that sheet name
to "PRI", adds another sheet and names it "BAK", then
saves it with the same name as a workbook.

any help would be greatly appreciated.

thanks

heidi
 
Heidi

Try this

Sub ChangeFiles()

Dim sPath As String
Dim Fname As String
Dim wb As Workbook

sPath = "C:\Dick\Tester\"

Fname = Dir(sPath & "*.xls")

Do While Len(Fname) > 0
Set wb = Workbooks.Open(Fname)
wb.Sheets(1).Name = "PRI"
wb.Worksheets.Add , wb.Sheets(1)
wb.Sheets(2).Name = "BAK"
wb.Close True
Fname = Dir
Loop

End Sub
 
Back
Top