Rename table from another sheet.

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
169
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!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

iconiuss

Board Regular
Joined
Feb 8, 2016
Messages
51
It might be easier if you stop the other macro changing the name.
Can you post the offending macro?
 

Mihael

Board Regular
Joined
Mar 18, 2016
Messages
169
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:

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

Board Regular
Joined
Mar 18, 2016
Messages
169
Sorry this is the code:

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

Board Regular
Joined
Feb 8, 2016
Messages
51

ADVERTISEMENT

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

Board Regular
Joined
Mar 18, 2016
Messages
169
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

Board Regular
Joined
Mar 18, 2016
Messages
169

ADVERTISEMENT

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

2wgdh5j.png
[/IMG]
 

iconiuss

Board Regular
Joined
Feb 8, 2016
Messages
51
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.

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

Board Regular
Joined
Mar 18, 2016
Messages
169
Hi Iconiuss,

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

iconiuss

Board Regular
Joined
Feb 8, 2016
Messages
51
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,865
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top