Rename table from another sheet.

[Mihael]

Hello everyone,

I want to rename a table with a Macro from another sheet. When I do this, it will not change the name. The reason I want to do this, is that the table name changes when I use a different Macro.

Does anyone know how to manage this?

Thanks in advance!

 

[iconiuss]

It might be easier if you stop the other macro changing the name.
Can you post the offending macro?

 

[Mihael]

Hi Iconiuss,

Yes I think you are right, but I don't know how. I am using Excel 2010 and I will explain the situation.

I have 2 exactly the same tables on different sheets. Table Table59529 (sheet 1) must export its data to table 595 (sheet 2). To do this, I use this code:

Table59529

Every time I press the macro, it changes the table name of table 595, so I can use the macro only once.

 

[Mihael]

Sorry this is the code:

Sub Table1()'
' Table1 Macro
'


'
    Range("D1").Select
    ActiveWindow.SmallScroll Down:=81
    Range("Table59[#All]").Select
    Range("A104").Activate
    Selection.Copy
    Sheets("Resultaten tank").Select
    Range("Table595[#ALL]").Select
    ActiveSheet.Paste
   


    
End Sub

 

[iconiuss]

I'm away from my computer at the moment, so I can't do any testing.
There is a lot of redundant code in that sub, but it seems your biggest problem is your copying the whole table, hence the name change.
If you could just provide a couple of details, when I can I'll try to clean it up for you.
What are the names of the Sheet and table you get the info from, and the sheet and table you are passing it to.
I can see what I believe is most of it in the code, but clarification will help.
Also by the code I'm guessing both tables have the same headings and end up identical.

 

[Mihael]

That would be great!

The given code was created when I was recording a Macro. So this is the situation;

I have two identical tables on different sheets. On sheet "Stappenplan rapport" I want to export the data from the table to the identical table on sheet "Resultaten tank". So i pressed record Macro and selected the table on sheet "Stappenplan rapport" and copied it. Then I clicked the sheet "Resultaten tank" where I clicked paste. After this I stopped the Macro. So I can do this only once, because the name of the table changes and the Macro is useless then.

 

[Mihael]

I made an image of the situation. On the picture you see two sheets (I placed them next to each other).

[/IMG]

 

[iconiuss]

Hey Mihael,
Sorry for the delay in response, the solution seemed a lot simpler in my head before testing.
This isn't the best code as it has no error handling, but it should do what you need.
It's just a slight modification to your own code.

    Sheets("Resultaten tank").Select
    Range("Table595").ClearContents
    Sheets("Stappenplan rapport").Select
    Range("Table59").Copy
    Sheets("Resultaten tank").Select
    Range("Table595")(1).PasteSpecial

Let me know how it works for you.

 

[Mihael]

Hi Iconiuss,

Never mind the delay! The given code has some kind of error at the second row "(Range ("Table595").Clearcontents."

 

[iconiuss]

As I said there's no error checking.
Check that your table is still named Table595
And also the code requires a non empty Table to paste to.
I'll keep researching to get some neater code. I was just struggling with finding the data range of an empty table.