[Lame] Renaming worksheets in VBA

[Yarroll]

Hi,

How do I rename sheets in VBA so they bear the name of each respective
workbook?
I have a lot of workbooks each with 1 sheet named Sheet1. I need to import
them to one workbook, but then of course they'd be named Sheet1, Sheet1(2),
... etc.

I tried to run a macro which opens each xls in a folder, renames Sheet1,
saves and closes it.
The rename line was:
Sheets("Sheet1").Name = ActiveWorkbook.Name

- but that doesnt work (Runtime error 1004, and the error is the name is
sometimes longer than 31 characters). Any way to go around this limitation?

Thanks. Best regards
Yarroll

 

[Jim Cone]

Yarroll,

Limit the sheet name to 31 characters...

Sheets("Sheet1").Name = Left$(ActiveWorkbook.Name, 31)

Regards,
Jim Cone
San Francisco, CA