Renaming Multiple Sheets in order

[Smatthews]

Hi,

I'm pretty sure this can be done, im just very very rusty at using excel, I have a spreadsheet that contains 1700 sheets. On sheet 1 i have 1700 rows containing multiple colunms of data then Sheet 2 through to Sheet 1700 contain 1 row each so basically every row on the mastersheet has its own indivisual sheet with in the work book. At the moment the tabs are named by the Content in coloun A on the Mastersheet/Sheet1.

is there a way i can re-name the sheets at once in order? for example Sheet 2 would be names 001cn00 , then sheet 3 would be 001cn01, sheet 4 001cn02 and so on.

after they are named can i then save each sheet as its own xls. file with the file name being the tab/sheet name?


Thank you

[JBeaucaire]

1) Sheet2 is renamed 001cn00 .....Sheet1700 is renamed 001cn1699?
2) As each sheet is renamed it is copied to its own workbook and saved as the same sheetname?


Is the goal really step #1 or step #2? You don't have to do step #1 to accomplish step #2. We could leave your original workbook pristine while still creating these 1700 other workbooks.

[JOHN H. DAVIS]

Maybe:

Sub Smatthews()
Dim Oldbook As Workbook
Dim i As Long
Dim x As Long
Set Oldbook = Workbooks(ActiveWorkbook.Name)
x = 0
For i = 2 To ActiveWorkbook.Sheets.count
    Sheets(i).Activate
    Sheets(i).Name = "001cn0" & x
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=Oldbook.Path & "\" & ActiveSheet.Name & "", FileFormat:=xlWorkbookNormal
    Oldbook.Activate
    x = x + 1
Next i
End Sub

[Smatthews]

Hi,

I guess I need to do both? I need every sheet saved as its own file in that naming order from 001CN00 to 001CN1700.
if i could keep the original worksheet as is that would be great if i couldn't then its not a big deal.

Also at the moment i am using an xls. but when i save the sheets to individual files is it possible to save them as CSV files with the naming as 001CN00 ect? or do i have to manually do it?

Thanks

[Smatthews]

Sorry scratch that. i have the sheets named in order, I need to save them to their own file, but can i save each sheet as a CSV file with the sheet name as the file name?

At the moment they are in a normal xls. file

Thanks

[JBeaucaire]

Like so:

Option Explicit

Sub CreateCSVs()
Dim fPATH As String, fNAME As String, ws As Long

fPATH = "C:\Path\To\Save\CSV\Files\"        'remember the final \ in this string

For ws = 2 To ThisWorkbook.Sheets.Count
    fNAME = "001cn0" & ws - 2 & ".csv"
    Sheets(ws).Copy
    ActiveWorkbook.SaveAs Filename:=fPATH & fNAME, FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False
Next ws

End Sub

[Smatthews]

Thank You , worked perfectly! would i be able to use the same macro to convert the csv files to txt, if i change the CSV part in the code?