Rename the first worksheet of multiple workbooks with a folder

[Tanner_2004]

Good Day!

I was hoping someone can help-me with this. I have a number workbooks in the same path/folder (let's say: C\Excel"Workbooks\Tonys Files), and I would like to name the first sheet of each workbook within the path/folder "Sheet1" (without the ""s). Unfortunately, the first sheet (left-most) worksheet in each work are not named the same; some ARE named Sheet1, but many have other names. I guess the macro would search for the first or left-most worksheet of each workbook (within the designated path) and name it Sheet1 (even though some of the first worksheets already already have that name). Any help would be greatly appreciated.

Thank you for reading and replying.

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Rename_Sheet1()
    [color=darkblue]Dim[/color] strPathh [color=darkblue]As[/color] [color=darkblue]String[/color], strFile [color=darkblue]As[/color] [color=darkblue]String[/color], counter [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strPath = "C:\Excel Workbooks\Tonys Files\"
    strFile = Dir(strPath & "*.xl*")
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]Do[/color] [color=darkblue]While[/color] strFile <> ""
        [color=darkblue]With[/color] Workbooks.Open(strPath & strFile)
            .Sheets(1).Name = "Sheet1"
            .Close SaveChanges:=[color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        counter = counter + 1
        strFile = Dir
    [color=darkblue]Loop[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox counter & " files altered. ", vbInformation, "Process Complete"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Tanner_2004]

Thank you..can't wait! I will do so shortly. Thank again!

 

[Tanner_2004]

Thank you AlphaFrog, it worked perfectly.
I would now like to loop through the same workbooks, Select the range for each Sheet1 which always starts in cell A1, and convert each range to a table, then great a basic pivot table on a new sheet.

Believe it or not, I have found many macros with directions for performing each of the aformentioned steps , but none that puts that all in one macro. Last night I tried to edit your macro to do what i described above, but none were to be found.

 

[zamshabeer]

Hi there

This code works perfectly for .xlsx files.

But the code does not rename the sheets when the files are .csv files.

Is there a way to use this code for .csv files?

Thanks
Best regards
Alakazam

 

[AlphaFrog]

Hi there

This code works perfectly for .xlsx files.

But the code does not rename the sheets when the files are .csv files.

Is there a way to use this code for .csv files?

CSV files don't have sheets. They are text files that can be imported into Excel onto one sheet.

Do you want to open CSV files, rename the sheet it's imported on, and save the one sheet as an Excel file? If yes, that's a significantly different macro.

 

[zamshabeer]

Hi AlphaFrog

Thanks for your quick response.

I broke my task into 2 macros and was able to solve the issue.

First one to batch convert all 400 csv files into excel files
Second to rename the first sheet as sheet 1 of all the converted excel files.

Thanks
Best regards
Alakazam

 

[AlphaFrog]

Well done. Thanks for the feedback.