Making a Table global to every worksheet


I have written a little macro that adds data to a table appending each time a new row. I have for that created a table to which a macro refers to add a row and add the data.
I would like however that same process to exist in other worksheets which are identical but for the data. Ie Table1 in sheet1 might have more data that in sheet 2, but the macro would refer to Table1 in the Active sheet, and append a row in that Table.
I am not sure whether tables can be like named range global for all sheets?
If not, I will then need to change the macro to refer to a named range?
Here is teh code
Sub Recup()
Dim sht As Worksheet
Dim tbl As ListObject
Dim table_object_row As ListRow

Set sht = Sheets(4)
Set tbl = sht.ListObjects("Table10")
last_row_with_data = sht.Range("Y65536").End(xlUp).Row
If last_row_with_data = sht.Range("Y13") Then
Set table_object_row = tbl.ListRows
Else: Set table_object_row = tbl.ListRows.Add
End If

sht.Range("Y" & last_row_with_data).Value = Range("AJ10")
sht.Range("Z" & last_row_with_data).Value = Range("AK10")
sht.Range("AA" & last_row_with_data).Value = Range("AL10")
sht.Range("AB" & last_row_with_data).Value = Range("AM10")
sht.Range("AC" & last_row_with_data).Value = Range("AN10")
sht.Range("AD" & last_row_with_data).Value = Range("AO10")
sht.Range("AE" & last_row_with_data).Value = Range("AP10")
sht.Range("AF" & last_row_with_data).Value = Range("AQ10")
sht.Range("AG" & last_row_with_data).Value = Range("AR10")

End Sub

Open in new window


Daniele QuestiauxResearch associateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Tables ARE global (workbook-based) named ranges

See Formulas / Defined Names / Name Manager


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniele QuestiauxResearch associateAuthor Commented:
Sorry, I think I was not very clear, not clear at all.

inserted the tables using insert>table and of course got nowhere as I could not change the scope of the table in the name manager afterwards as the scope window was greyed out which I thought meant the scope was defined once and for all.
 I somehow never thought that building the table building via the manager would un-grey that window and allow me that change of scope!
The solution was super evident, and as you expected it now works beautifully!
I had found a roundabout way to deal with the issue, but it is niggling to feel that there must be an elegant way, and miss it altogether.


Daniele QuestiauxResearch associateAuthor Commented:
Clear and concise answer which specifies that to be able to have a table with a global scope, do not go via insert a table, but build it directly via name manager which allows you then to change the default scope of "worksheet" to "global".
Simple but so handy!
Daniele QuestiauxResearch associateAuthor Commented:
I am afraid I thought it did work, but no, it reverts to the table in the first worksheet each time so that the same macro cannot be used for each sheet without everytime having a new table. I figured I should each time create a table via VBA in each sheet, then somehow via VBA, find the table name for that sheet and still via VBA refer to it to append the data. This seems sadly quite convoluted compared to named range that somehow can refer to ranges in any worksheet.
The obvious is to use a dynamic named range rather than a table for what I am trying to do but am unsure how to change the code to cater for that dynamic name range.
Still sadly not fixed!

I think you should open a new question precising you want not one global table but one table for each sheet.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.