I have over 100 similar worksheets with two tables on each sheet, currently
with excel inscrutable names. I would like to automatically change the names
of the tables to reflect two things:
1. the name of the worksheet
2. conatentate sfr on the first table in the sheet and ct to the second
table on the sheet.

I've spent the last four hours searching the internet and playing around
with this to no avail.

Recording a macro gives me this:
Range("Table.AthertonSFR[[#Headers],[Date]]").Select
ActiveSheet.ListObjects("Table811131517192123").Name = "AthertonSFR"
Range("Table710121416182022[[#Headers],[Median]]").Select
ActiveSheet.ListObjects("Table710121416182022").Name = "AthertonCT"

Which, as the names of all the tables are different, is useless, and I can't
figure out how to just select "Table1" then "Table2".

The second problem is the new name. The formula to generate the new name is
=(MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256))&"SFR"

When I test that portion, it returns "compile error: syntax error"

Any help would be greatly appreciated.

TIA

Having a real problem trying to interpret exactly what you want to do. Can
you answer the following for me.

Provide the following for one worksheet before any changes.

Worksheet name:
Table 1 name:
Table 1 header names:
Table 1 range:

Table 2 name:
Table 2 header names:
Table 2 range:

What do you want the above to be after the changes?


If having difficulty providing the above table details before changes then
run the following code on the active sheet.

Sub TableDetails()
Dim ws As Worksheet
Dim LstObj As ListObject
Set ws = ActiveSheet
For Each LstObj In ws.ListObjects
MsgBox "Table name: " & _
LstObj.Name & vbCrLf & _
"Table range: " & LstObj.Range.Address
Next
End Sub

--
Regards,

OssieMac


"rdcsfd" wrote:

> I have over 100 similar worksheets with two tables on each sheet, currently
> with excel inscrutable names. I would like to automatically change the names
> of the tables to reflect two things:
> 1. the name of the worksheet
> 2. conatentate sfr on the first table in the sheet and ct to the second
> table on the sheet.
>
> I've spent the last four hours searching the internet and playing around
> with this to no avail.
>
> Recording a macro gives me this:
> Range("Table.AthertonSFR[[#Headers],[Date]]").Select
> ActiveSheet.ListObjects("Table811131517192123").Name = "AthertonSFR"
> Range("Table710121416182022[[#Headers],[Median]]").Select
> ActiveSheet.ListObjects("Table710121416182022").Name = "AthertonCT"
>
> Which, as the names of all the tables are different, is useless, and I can't
> figure out how to just select "Table1" then "Table2".
>
> The second problem is the new name. The formula to generate the new name is
> =(MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256))&"SFR"
>
> When I test that portion, it returns "compile error: syntax error"
>
> Any help would be greatly appreciated.
>
> TIA
>
>

Worksheet Name: Belmont
Table 1 Name: Table811131517192125
Table 1 Header names:
Date
Median
Average
Sales
Pending
Inventory
DOI
SP/LP
DOM
MedSy
AveSy
SalesSy
PendSy
InvenSy
TableRange: $A$5:$U$87

Worksheet Name: Belmont
Table 2 Name: Table710121416182024
Table 2 Header names:
Median
Average
Sales
Pending
Inventory
DOI
SP/LP
DOM
MedSy
AveSy
SalesSy
PendSy
InvenSy
TableRange: $W$5:$AP$87

The current table names are useless for creating formulas, charts, etc... I
want the table names to be user friendly: BelmontSFR for the first table and
BelmontCT for the second table.

"OssieMac" wrote:

> Having a real problem trying to interpret exactly what you want to do. Can
> you answer the following for me.
>
> Provide the following for one worksheet before any changes.
>
> Worksheet name:
> Table 1 name:
> Table 1 header names:
> Table 1 range:
>
> Table 2 name:
> Table 2 header names:
> Table 2 range:
>
> What do you want the above to be after the changes?
>
>
> If having difficulty providing the above table details before changes then
> run the following code on the active sheet.
>
> Sub TableDetails()
> Dim ws As Worksheet
> Dim LstObj As ListObject
> Set ws = ActiveSheet
> For Each LstObj In ws.ListObjects
> MsgBox "Table name: " & _
> LstObj.Name & vbCrLf & _
> "Table range: " & LstObj.Range.Address
> Next
> End Sub
>
> --
> Regards,
>
> OssieMac
>
>
> "rdcsfd" wrote:
>
> > I have over 100 similar worksheets with two tables on each sheet, currently
> > with excel inscrutable names. I would like to automatically change the names
> > of the tables to reflect two things:
> > 1. the name of the worksheet
> > 2. conatentate sfr on the first table in the sheet and ct to the second
> > table on the sheet.
> >
> > I've spent the last four hours searching the internet and playing around
> > with this to no avail.
> >
> > Recording a macro gives me this:
> > Range("Table.AthertonSFR[[#Headers],[Date]]").Select
> > ActiveSheet.ListObjects("Table811131517192123").Name = "AthertonSFR"
> > Range("Table710121416182022[[#Headers],[Median]]").Select
> > ActiveSheet.ListObjects("Table710121416182022").Name = "AthertonCT"
> >
> > Which, as the names of all the tables are different, is useless, and I can't
> > figure out how to just select "Table1" then "Table2".
> >
> > The second problem is the new name. The formula to generate the new name is
> > =(MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,256))&"SFR"
> >
> > When I test that portion, it returns "compile error: syntax error"
> >
> > Any help would be greatly appreciated.
> >
> > TIA
> >
> >

OK I am assuming that the Table Header "Date" is the identifier for the table
that will be worksheet name & SFR and the other table will be worksheet name
& CT. Hope this is correct but if not then let me know.

NOTE: Ensure that you backup your workbook before testing the code.

The first sub will change the table names and the second is testing code
only and will iterate through the worksheets and tables on each sheet so that
you can check if they are correct. (You probably know this but just in case.
Ctrl/Break will stop the second code if you don't want to go through them
all.)



Sub ChangeTableName()
Dim ws As Worksheet
Dim LstObj As ListObject
Dim rngFind As Range

For Each ws In Worksheets
For Each LstObj In ws.ListObjects
With LstObj.HeaderRowRange
'Find the word Date in header row
Set rngFind = .Find(What:="Date", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFind Is Nothing Then
'Date found: Table Sheet name & SFR
LstObj.Name = ws.Name & "SFR"
Else
'Date NOT found: Table Sheet name & CT
LstObj.Name = ws.Name & "CT"
End If
End With
Next LstObj
Next ws

End Sub


'Testing code only

Sub TableDetails()
Dim ws As Worksheet
Dim LstObj As ListObject
For Each ws In Worksheets
For Each LstObj In ws.ListObjects
'Application.Goto LstObj.Range
MsgBox "Worksheet: " & ws.Name & vbCrLf & _
"Table name: " & _
LstObj.Name & vbCrLf & _
"Table range: " & LstObj.Range.Address & vbCrLf & _
"Headers: " & LstObj.HeaderRowRange.Address
Next
Next ws
End Sub

--
Regards,

OssieMac

Should have included this for you before. Not a bad site for using VBA with
tables.

mhtml:http://office.microsoft.com/download...AM102882561033

--
Regards,

OssieMac

Thanks for the help. Sorry about not replying sooner, but the 5th thru the
15th of each month is heads down work, and January is a double dose.

Using the field name "Date" is not what I had in mind because then there
will be multiple tables named "DateSFR' or DateCT in the workbook. I need to
use the worksheet name, which I have extracted and put into each worksheet at
b2.

I should be able to figure it out. Thank you for the assistance.

"OssieMac" wrote:

>
> Should have included this for you before. Not a bad site for using VBA with
> tables.
>
> mhtml:http://office.microsoft.com/download...AM102882561033
>
> --
> Regards,
>
> OssieMac
>
>

Should have seen you were using ws.name. Get error 1004 at line: LstObj.Name
= ws.Name & "CT"

"OssieMac" wrote:

> OK I am assuming that the Table Header "Date" is the identifier for the table
> that will be worksheet name & SFR and the other table will be worksheet name
> & CT. Hope this is correct but if not then let me know.
>
> NOTE: Ensure that you backup your workbook before testing the code.
>
> The first sub will change the table names and the second is testing code
> only and will iterate through the worksheets and tables on each sheet so that
> you can check if they are correct. (You probably know this but just in case.
> Ctrl/Break will stop the second code if you don't want to go through them
> all.)
>
>
>
> Sub ChangeTableName()
> Dim ws As Worksheet
> Dim LstObj As ListObject
> Dim rngFind As Range
>
> For Each ws In Worksheets
> For Each LstObj In ws.ListObjects
> With LstObj.HeaderRowRange
> 'Find the word Date in header row
> Set rngFind = .Find(What:="Date", _
> LookIn:=xlFormulas, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False, _
> SearchFormat:=False)
>
> If Not rngFind Is Nothing Then
> 'Date found: Table Sheet name & SFR
> LstObj.Name = ws.Name & "SFR"
> Else
> 'Date NOT found: Table Sheet name & CT
> LstObj.Name = ws.Name & "CT"
> End If
> End With
> Next LstObj
> Next ws
>
> End Sub
>
>
> 'Testing code only
>
> Sub TableDetails()
> Dim ws As Worksheet
> Dim LstObj As ListObject
> For Each ws In Worksheets
> For Each LstObj In ws.ListObjects
> 'Application.Goto LstObj.Range
> MsgBox "Worksheet: " & ws.Name & vbCrLf & _
> "Table name: " & _
> LstObj.Name & vbCrLf & _
> "Table range: " & LstObj.Range.Address & vbCrLf & _
> "Headers: " & LstObj.HeaderRowRange.Address
> Next
> Next ws
> End Sub
>
> --
> Regards,
>
> OssieMac
>
>