automating renaming a worksheet tab, where the worksheet tab is dynamic

[Hondahawkrider]

Help

I inherited a excel macro that needs to be changed, and I am at a loss..

The way the script works is that is automatically imports a series of .csv files as individual worksheet tabs which works great.. The issue is that there is a master worksheet that isn't imported that pulls in data from these tabs to calculate specific data...

The problems is that the files used to be static, but now as the data has increased they names are a dynamic

The files used to be named

IEP_Batch1_GETS_GenericReport thru IEP_Batch8_GETS_GenericReport

However the files are now named via the different servers that produce the data..

The files are now named

8X_IEP_BatchXX_GETS_GenericReport

where the 8X can be 80 thru 86
and BatchXX can be Batch1a and Batch1b thru Batch8a and Batch8b (batch names are split now between only a/b only not the whole alphabet)

THe only way that actually works is to do a "on error resume next" then to try and select the files If it exists and then rename it ...

like this .

Sheets("80_IEP_Batch1a_GETS_GenericReport").Select
Sheets("80_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"
Sheets("81_IEP_Batch1a_GETS_GenericReport").Select
Sheets("81_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"
Sheets("82_IEP_Batch1a_GETS_GenericReport").Select
Sheets("82_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"
Sheets("83_IEP_Batch1a_GETS_GenericReport").Select
Sheets("83_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"
Sheets("84_IEP_Batch1a_GETS_GenericReport").Select
Sheets("84_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"
Sheets("85_IEP_Batch1a_GETS_GenericReport").Select
Sheets("85_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"
Sheets("86_IEP_Batch1a_GETS_GenericReport").Select
Sheets("86_IEP_Batch1a_GETS_GenericReport").Name = "Batch1a_GETS"

The problem is that for I have to duplicate this code multiple times (16 times - a/b for 1-8) ... As I will need Batch1a and Batch1b thru Batch8a and Batch8b

Ive tried doing a loops
Dim i As Long, strTabName As String
but for the life of me I can't get it to work... Even if I could get part done a/b I could loop it a 2nd time for 1-8, but nada...

what I have works - but its kludgy.... and help to take it to less step would be appreciated...

[pjwhitfield]

I think this should do it.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Hondahawkrider]

will try tonight ... thanks

curious to what

For ab = 97 To 98

is for...

I can understand some of it, but that has me going .... hmmmmm

[pjwhitfield]

the 97 to 98 loop is the loop controlling a and b (ASCII char 97 = lowercase a and 98 is lowercase b) the "chr(ab)" in the setting of tmpSheet converts the value to the character.