Recording dynamic range using ctrl+ arrow keys not working

Hi ashkay12,

There are many ways to do what you want. See the attached workbook with a simple example that uses Workbook_SheetSelectionChange() in the 'ThisWorkbook' module.

We have to have more specific information on what you are trying to do in order to help you further.

Please Login or Register  to view this content.

Lewis

Hi,

Thanks for the reply Lewis & Holger.

To be more detailed, I get a report file in excel format exported by the erp.
This file has data in tabular format one below the other, i.e. there are number of tables(not in excel table format) one below the other.
I need to have filter on all these tabular data, so I have to convert them into table by selecting range and 'insert table'
The range is dynamic, the number of rows change every time in every tabular data.
I need to have a macro which could select the range and convert it into excel table and then move on and then select the next range and then convert it to table too.
Yes I found that the problem in selection by using ctrl+shift+ arrow keys is due to merged cells.

I was thinking to have a macro which would select the data range with cell borders and convert it into table and then find the next tabular data with cell borders and select them
and convert them into table. Since I am sure that the data cells will have borders always.
To create a table I have to have only one data range selected at a time since excel can insert table only for one data range at a time.

Thanks a lot again.

If you can provide a sample workbook with two (small) sample tables we may be able to help you. Chances are there are unique identifiers (such as specific Header items) that can help automate this task.

Lewis

Hi Lewis,

I have attached the file here. I used the fill color to locate the headers.

In case the earlier file does not opens.

Thanks!!

akshay

Hi,

Your workbook was extremely helpful.

See the attached workbook, based on your file. It contains two additional Sheets:
a. Sheet 'Sheet1' which contains a list of Tables to be processed.
b. Sheet 'Destination' where the data is copied to. At the current time only the LAST TABLE is copied to the 'Destination Area' starting in cell 'A1'.

Some intermediate results are displayed in the debugger 'Immediate Window' (CTRL G) in the debugger. An Excerpt of those results for the first two tables follows:

Please Login or Register  to view this content.

The next step is to decide the destination to copy each table to and what you want copied:
a. Everything for each table including the 'Table Type Sentinel'.
b. Data Table only including Header.
c. Data only (no header)
d. Copy values only or copy values and formats.
e. Destination Workbook (can be the source workbook) for each table.
f. Destination Sheet for each table.
g. Destination Start Cell for each table (including appending to existing tables).

Lewis

Hi Lewis,

Thanks for the help.
Sorry for the late reply. I was getting a sample from my counterparts.
I am looking into the code but I would like to share the required output as well.
Thanks for the great help.