Convert through Macro an Excel Table to a Range

**GRajagopalan** · 03-14-2016, 03:10 AM

I have a small issue here.
1) I am sending an excel sheet to my users . This sheet has a macro. In this sheet my users will paste varying rows of data values GL Accountwise / Buisness Areawise / Currencywise.

2) Then they will run the macro ( I recorded the macro ) which converts the data to an excel official table ,converts the data into a summarized format by creating a pivot table in the sheet itself through the macro . If the data is not converted to a table the macro does not work.

This is all working fine. But the issue is that if users re-run the macro a second time without converting the table to a range ,the macro gives an error “A table cannot overlap another table”. I need to reset the base data from a excel table to a range from the macro itself .

I tried to record a macro which resets the table to range from the menu, but the macro seems to record nothing.

Could you help?

Thanks.

**Keebellah** · 03-14-2016, 05:40 AM

Hi, I suugest to do the following.
It's simple and will show you what to do.

Start recording macro #1:
select the table and convert to range
Stop the recording

Start recording macro #2:
Select the range required
Convert this to Table
Stop the recording

All you need to do is enter a check if the table exists and then run the macro #1 to set to range

The second one can only be run if the Table dose not exists.

I hope this helps to give you an idea.

BTW if peroble add data to an existing table it will automatically adapt and resieze.

**GRajagopalan** · 03-14-2016, 07:52 AM

OK. Thank you so much Keebellah.

I will try and get back to you.

Thanks Once again.

**GRajagopalan** · 03-14-2016, 08:02 AM

Hi Keebellah

As I had mentioned in my first post itself, When you record a macro : Select the table and either from the menu or by right clicking you convert the table to a range and stop the macro, the macro seems to record nothing. The macro does not record the steps. Sorry to trouble once again.

**Keebellah** · 03-14-2016, 10:22 AM

You mentioned that but what I mentioned was that you need to add a test to see if the table is already present to avoid the errors.

Create a small table in a new blank workbook

Range B4 - F4 will be the header
B5 - F 6 is the date, two rows

Please Login or Register  to view this content.

If you place the above two macros in a blank workbook and run them:

rng2Table will create a table if does not already exists for the selected range

tbl2rng will convert the table to range if the table is present

The table name will be 'TestTable"

This is what I meant

Convert through Macro an Excel Table to a Range

LinkBack

Thread Tools

Rate This Thread

Display

Convert through Macro an Excel Table to a Range

Re: Convert through Macro an Excel Table to a Range

Re: Convert through Macro an Excel Table to a Range

Re: Convert through Macro an Excel Table to a Range

Re: Convert through Macro an Excel Table to a Range

Thread Information

Users Browsing this Thread

Similar Threads

Convert PDF Table to Excel Table or Convert Text String to Column(s)

Convert Word Table Macro to excel macro

Macro to convert date range to individual line items (i.e. rows) in excel

VBA how to convert a range to a table in excel 2010

[SOLVED] Macro to Convert Selected Range to Table

How to convert a table to a range without removing the query. (Excel 2010)

Macro to convert mailing address list into excel table

Bookmarks

Bookmarks

Posting Permissions