Trimming worksheet name from an imported data source

[lonnierudd via OfficeKB.com]

I'm bringing in data from an external source that changes each month, and to
make the macro run correctly it needs to have the same name each month on the
tab (I think. I'm still a novice at VBA). It's name is always the same length
except for the month's name at the beginning. I'm having a little trouble
getting the Trim function to work in VBA though. Can somebody help me with
this? I'm sure I'm missing something easy. If it helps here's the name for
the July/August worksheet: JA_20082008_CHRYSLER_300_SERIES

Next month the JA will be replaced with AS for August/September. I really
would appreciate your help.

 

[Bob Phillips]

=RIGHT(A1,LEN(A1)-FIND("_",A1)+1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

I'm not totally clear on what you are asking. Do you want to know how to
change the first 2 characters of a string of text? If your string is in a
variable named, say, TabName so that currently,

TabName = "JA_20082008_CHRYSLER_300_SERIES"

and you want to change this to start with AS, then there are two ways you
can do this...

1) TabName = "AS" & Mid(TabName, 3)

2) Mid$(TabName, 2) = "AS"

You could then assign the TabName back to the tab. If you are asking
something else, them I think you will have to provide us with more details.

Rick

 

[lonnierudd via OfficeKB.com]

The database I'm pulling the worksheet from comes from a vendor, and it
automatically assigns the worksheet name. What I'm looking for is to be able
to have a macro pull the new numbers each month after I've loaded the new
tables. There are 80+ different vehicles I pull this information on, so
unless we can get the vendor to give us the data with a consistent name, not
likely, then I need to be able to put it into a consistent form so that the
worksheets with all the calculations on them will update correctly when the
tables themselves are updated. Does that help? So if it'll trim 12 characters
off the left side of the worksheet name, then I'd be good. I'd just repeat
that on all 80+ worksheets that I need to trim. Trimming from the right would
mean different code for each page since there's lots of different types of
vehicles.

I'm not totally clear on what you are asking. Do you want to know how to
change the first 2 characters of a string of text? If your string is in a
variable named, say, TabName so that currently,

TabName = "JA_20082008_CHRYSLER_300_SERIES"

and you want to change this to start with AS, then there are two ways you
can do this...

1) TabName = "AS" & Mid(TabName, 3)

2) Mid$(TabName, 2) = "AS"

You could then assign the TabName back to the tab. If you are asking
something else, them I think you will have to provide us with more details.

Rick

I'm bringing in data from an external source that changes each month, and
to

[quoted text clipped - 9 lines]

Next month the JA will be replaced with AS for August/September. I really
would appreciate your help.

 

[Rick Rothstein \(MVP - VB\)]

If you want to "trim off" the first 12 characters from your worksheet name,
you can use the Mid function to do that. Normally, you would specify a 3rd
argument to this function telling it how many characters to retrieve from
the starting point (specified in the 2nd argument) onward; however, if you
omit the 3rd argument, the Mid function will retrieve from the starting
point to the end of the text. So, if...

WorksheetName = "JA_20082008_CHRYSLER_300_SERIES"

then...

TrimmedWorksheetName = Mid(WorksheetName, 13)

Does that help?

Rick

The database I'm pulling the worksheet from comes from a vendor, and it
automatically assigns the worksheet name. What I'm looking for is to be
able
to have a macro pull the new numbers each month after I've loaded the new
tables. There are 80+ different vehicles I pull this information on, so
unless we can get the vendor to give us the data with a consistent name,
not
likely, then I need to be able to put it into a consistent form so that
the
worksheets with all the calculations on them will update correctly when
the
tables themselves are updated. Does that help? So if it'll trim 12
characters
off the left side of the worksheet name, then I'd be good. I'd just repeat
that on all 80+ worksheets that I need to trim. Trimming from the right
would
mean different code for each page since there's lots of different types of
vehicles.

I'm not totally clear on what you are asking. Do you want to know how to
change the first 2 characters of a string of text? If your string is in a
variable named, say, TabName so that currently,

TabName = "JA_20082008_CHRYSLER_300_SERIES"

and you want to change this to start with AS, then there are two ways you
can do this...

1) TabName = "AS" & Mid(TabName, 3)

2) Mid$(TabName, 2) = "AS"

You could then assign the TabName back to the tab. If you are asking
something else, them I think you will have to provide us with more
details.

Rick

I'm bringing in data from an external source that changes each month,
and
to

[quoted text clipped - 9 lines]

Next month the JA will be replaced with AS for August/September. I
really
would appreciate your help.

 

[lonnierudd via OfficeKB.com]

I think that'll do it. Thank you!

If you want to "trim off" the first 12 characters from your worksheet name,
you can use the Mid function to do that. Normally, you would specify a 3rd
argument to this function telling it how many characters to retrieve from
the starting point (specified in the 2nd argument) onward; however, if you
omit the 3rd argument, the Mid function will retrieve from the starting
point to the end of the text. So, if...

WorksheetName = "JA_20082008_CHRYSLER_300_SERIES"

then...

TrimmedWorksheetName = Mid(WorksheetName, 13)

Does that help?

Rick

The database I'm pulling the worksheet from comes from a vendor, and it
automatically assigns the worksheet name. What I'm looking for is to be

[quoted text clipped - 41 lines]