Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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").Na me = "AthertonSFR" Range("Table710121416182022[[#Headers],[Median]]").Select ActiveSheet.ListObjects("Table710121416182022").Na me = "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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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").Na me = "AthertonSFR" Range("Table710121416182022[[#Headers],[Median]]").Select ActiveSheet.ListObjects("Table710121416182022").Na me = "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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").Na me = "AthertonSFR" Range("Table710121416182022[[#Headers],[Median]]").Select ActiveSheet.ListObjects("Table710121416182022").Na me = "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change multple table names
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Change / Format Legend Names | Charts and Charting in Excel | |||
Code for a Macro that allows a Pivot table to be repeatedly run (fora dynamic rows in a table, but static field names) | Excel Programming | |||
Add Database Names, table names & related fields from table in combo box | Excel Programming | |||
Printing PDF outputting to multple files - change to 1 file | Excel Programming | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |