Assign names to tables

Please help. I need a macro to assign names to tables in one Worksheet (Sheet1) in one workbook.

In the Sheet1 I have several tables, each with a title (ex; "BPL Metrics (AM-REG)_SBRM_Reg" in Bold), and under each table the data, which end with a blank row at the end of each table before the next table’s title. Tables shows going down, all starting Titles in column "A" (samples below), and each table can have from 1 up 30 columns and rows from 1 up to 25 rows, that may change every week.
I need a macro to:
First identify the title of each table in Sheet1 (Titles will not change, and will be listed in “Sheet2” (see below), e.x; go to cell A1 where “BPL Metrics 1” is,
Second, identify the last active cell of that table (to highlight the information of the table under each title, rows and columns), e.x; go to cell D4 where “999.9” is (this table range will be A1..D4).
Third, assign to this table range as name, its corresponding title, e.x; Name of table range: “BPL Metrics 1”

Then if the table title change to the next table, then repeat the process for the new table, and so on (next table range will be A6..E9, and its name will be “BPL Metrics 2”

There are about 209 tables, each with a different title, and all of them in only one sheet. Please remember, every week, the number of rows and columns may change in any table, but it will always have a blank row under each table.
“Sheet1”
A B C D E
1 BPL Metrics 1
2 Channel BranchPriority BBRegion1 PM_2ConRev
3 SBRM B1 East -B1 469.1
4 SBRM B1 West -B1 999.9
5
6 BPL Metrics 2
7 Channel BranchPriority BBRegion1 PM_2ConRev PM_1ConRev
8 WHLS WS East -WS 145.9 143.2
9 WHLS WS West -WS 114.1 113.7
10
11 BPL Metrics 3
12 Channel BBRegion PM_2ConRev
13 WHLS East 574.51
14 WHLS East 603.68
15 WHLS East 276.76
16

“Sheet2”
BPL Metrics 1
BPL Metrics 2
BPL Metrics 3
Thanks a lot

Please Login or Register  to view this content.

Thanks for your help, but I am getting an error 1004 in:
ActiveWorkbook.Names.Add Name:=Replace(.Cells(startRow, "A").Value2, " ", "_"), _
RefersTo:="=" & .Cells(startRow, "A").Resize(i - startRow, 5).Address

Do I need to change something here. Also it looks that this code is replacing the name of the table by changing " " to "_". Is there is any way to avoid this (Some tables have the "_" in the title.

Thanks again

EXCEL_TEST1.xls

I need a macro to assign names to tables in one Worksheet (CMD_1) in one workbook and THEN copy these ranges to other specific ranges in other worksheets (Sheet2 and Sheet3).

I attached a file to make it easier.

In the worksheet “CMD_1”, I have several tables, each with a title (ex; "Create WS_Mkt_mgr Roster" in Bold), and under each table the data, which end with a blank row at the end of each table before the next table’s title.

Tables shows going down, all starting Titles in column "A", and each table can have from 1 up 30 columns and rows from 1 up to 25 rows, that may change every week.


The macro would do the following:

First identify the title of each table in worksheet “CMD_1” (Titles will not change, and will be listed in worksheet “Map_Ref”), e.x; go to cell A1 where “Create WS_Mkt_mgr Roster” is,

Second, identify the last active cell of that table (to highlight the information of the table under each title, rows and columns), e.x; go to cell F28 where “SBRM” is (this table range will be A1..F28).

Third, assign to this table range as name, its corresponding title, e.x; Name of table range: “Branch Counts_Chase_SBRM_Reg”

Then if the table title change to the next table, then repeat the process for the new table, and so on (next table range will be A30..E33, and its name will be “Branch Counts_Chase_SBRM_Reg”

There are about 209 tables, each with a different title, and all of them in only one sheet. Please remember, every week, the number of rows and columns may change in any table, but it will always have a blank row under each table.

THEN NEXT,

Look in worksheet “Map_Ref”, and take the Range Name of column “A” and copy that range in the corresponding Tab and range as specified in Columns “C” and “D” (i,e. take range name “BB Staff Counts_Tenured” from cell “A2” and copy that range in worksheet “Sheet2” (as specified in cell C2), range “A2” (as specified in cell D2). And so on.

Thanks a lot for you help.

Yonel

Still unresolved - please help

Help please

Thanks John for your help.

I looking for a macro to look in a list in worksheet “Map_Ref”, and take the Range Name of column “A” and copy that range in the corresponding Tab and range as specified in Columns “C” and “D” (i,e. take range name “BB Staff Counts_Tenured” from cell “A2” and copy that range in worksheet “Sheet2” (as specified in cell C2), range “A2” (as specified in cell D2). And so on. All Ranges to be copied are in tab "CMD_1".


List of worksheet "Map_Ref":
Column A Column B Column C Column D
Table/Range Title/Name: Range in Copy to Tab: To Range:
Tab "CMD_1"
BB_Staff_Counts_Tenured A126..Q156 Sheet2 A2
Branch_Counts_BBmarkets A35..C47 Sheet2 A59
Branch_Counts_Chase_SBRM_Reg A30..E33 Sheet2 A86
BB_SCs_Dash_EXPORTexcel_All A122..F124 Sheet3 A2
Create_WSmgr_Roster A158..F165 Sheet3 A6
Branch_Counts_WHLS A77..A120 Sheet3 A15
Branch_Counts_Chase_WHLS_AM A49..E75 Sheet3 A60
Create_WS_Mkt_mgr_Roster A1..F28 Sheet3 A98

So far somebody helped with the following code, and works fine, BUT only copies the first 5 columns, and some tables can have more that 60 columns or more (see in the attached file "EXCEL_TEST3.xls), the range "BB_Staff_Counts_Tenured" in windows 4 (original) and window 2 Copied range):

Sub CopyRanges()
Dim i As Long, lastrow As Long
With Sheets("Map_Ref")
lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lastrow
Sheets("CMD_1").Range(.Cells(i, 1).Value).Copy
Sheets(.Cells(i, 3).Value).Range(.Cells(i, 4).Value).PasteSpecial xlPasteValues
Next i
End With
Range("A1").Select
Application.CutCopyMode = False
End Sub

Please let me know what I am doing wrong. THANKS