Excel & high sheet numbers 1

[segmentationfault]

I have a macro that generates a multiple sheet report by first deleting the existing sheets and recreating them from cumulative data. Though the sheets are deleted, their numbers are not reused when new sheets are added. As a result, I have a template, cumulative data, and (currently) Sheet87 through Sheet98. The sheets are renamed as they are created, but the object name persists.

Does anyone know if this will cause problems? Is there an upper limit on the Sheet naming conventions? Is there a way to conveniently erase all data, formatting, column & row widths, etc., on a sheet without deleting it?

Thanks

 

[rmikesmith]

Don't have the answer to your primary question but the following short procedure will clear contents, formatting and reset row & column sizes to default values for the active worksheet:

Sub ClearWorksheet()

  With ThisWorkbook.ActiveSheet.Cells
    .Clear
    .UseStandardWidth = True
    .UseStandardHeight = True
  End With
  
End Sub

HTH
M. Smith

 

[kylua]

To rename your sheets, and I don't think there is an upper limit to the naming:

On Error GoTo Test2_error
For n = 1 To Sheets.Count
Worksheets.Name = ("Mysheet " + CStr)
Next n
Exit Sub
Test2_error:
Worksheets.Name = ("Mysheets " + CStr)

Potential infinite loop if there is already a mysheet 5 and a mysheets 5 but I'll leave you to get round the naming convention. (Rename all just to cstr and then redo to "sheet " + cstr)

But you're actually better off using the provided clear code, quicker and neater. Just thought you'd like to know how to rename the sheets. You could rename to the relevant dates or whatever.