CODEWITHSUNDEEP
vbaexcel
Hoopah
Hoopah

Reputation: 5

I would like to change a worksheets name to the filename during a loop as I open its workbook VBA

I am currently using this code to open all .xls files in a folder

Sub OpenFiles()

    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = InputBox("Please enter the folder for files")
    MyFile = Dir(MyFolder & "\*.xls")
    Do While MyFile <> ""
        Workbooks.Open Filename:=MyFolder & "\" & MyFile
        ****Sheet1.Name = "MyFile"****
        MyFile = Dir
    Loop
End Sub

I am trying to change the worksheet name to the file name as it loops though

Every worksheet I am opening will be called "Parts List"

I am trying to use the asterisk portion to do this but it does not work.

Upvotes: 0

Views: 34

Answers (2)

user6432984
user6432984

Reputation:

This will rename the Worksheets("Parts List") in the newly opened workbook to MyFile.

Sub OpenFiles()
    Dim wb As Workbook
    Dim MyFolder As String
    Dim MyFile As String
    MyFolder = InputBox("Please enter the folder for files")
    MyFile = Dir(MyFolder & "\*.xls")
    Do While MyFile <> ""
        Set wb = Workbooks.Open(Filename:=MyFolder & "\" & MyFile)

        wb.Worksheets("Parts List").Name = MyFile

        MyFile = Dir
    Loop
End Sub

Upvotes: 1

Kyle
Kyle

Reputation: 2545

Try changing the line to:

Sheets("Sheet1").Name = "MyFile"

Upvotes: 0

Related Questions