Names.Add Name - RefersToR1C1
 

Hello,
I've tried to Create a Procedure that Creates & Names a Range from scratch
each time the Sub is called so that I can copy & insert it elsewhere in a
Workbook later in the Procedure.

What I've got is a Do Loop that copies and pastes each (criteriated) row
from Sheet1 to Sheet2. Then I've tried to find the size of that range
(sheet2) and name it "table1".
Table1 is then reformatted by copying & pasting the formats from another row
elsewhere in the spreadsheet so that it (table1) can be copied & inserted
into another templated sheet (lets call it sheet3).
I was pretty proud of myself until I realised that the RefersToR1C1 (see
below) overwrites the previous three lines of code essentially defuncting the
entire exercise. Doh!

Before you appraise the (nonsense) code I've been trying to use I'll clarify
the following points:
The range width starts out at 34Columns and is ultimately reduced to 32
(A:AD). And, I've written code that runs prior to this one that deletes the
named ranges I'm trying to create with the below code.

Sheets("1").Select
Range("A1").Select
Range("A1:AF1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="='1'!R1C1:R30C32"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.ClearContents
Range("table1").RowHeight = 15
Range("A1").Select

Names.Add Name - RefersToR1C1
 

I don't like the fact that you are deleting rows in this code because it
makes selecting the table harder to perform. I don't know which column you
want to use to determine the last row since you are deleting columns A & B.
Try this code. Yo may have to change the column I'm using to get the last
row. You also may want to change the first cell A1 to another cell.


Sub Tst()
Call MakeTable("Sheet1")

End Sub

Sub MakeTable(ShtName As String)
With Sheets(ShtName)
.Columns("A:B").Delete
.Columns("A:A").ClearContents

LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Cells(Rows.Count, LastCol).End(xlUp).Row
Set LastCell = .Cells(LastRow, LastCol)

TableRange = .Range(.Range("A1"), LastCell).Address( _
ReferenceStyle:=xlR1C1, external:=True)

ActiveWorkbook.Names.Add _
Name:="table1", _
RefersToR1C1:="=" & TableRange

.Range("table1").RowHeight = 15
End With
End Sub



"VickiMc" wrote:

Hello,
I've tried to Create a Procedure that Creates & Names a Range from scratch
each time the Sub is called so that I can copy & insert it elsewhere in a
Workbook later in the Procedure.

What I've got is a Do Loop that copies and pastes each (criteriated) row
from Sheet1 to Sheet2. Then I've tried to find the size of that range
(sheet2) and name it "table1".
Table1 is then reformatted by copying & pasting the formats from another row
elsewhere in the spreadsheet so that it (table1) can be copied & inserted
into another templated sheet (lets call it sheet3).
I was pretty proud of myself until I realised that the RefersToR1C1 (see
below) overwrites the previous three lines of code essentially defuncting the
entire exercise. Doh!

Before you appraise the (nonsense) code I've been trying to use I'll clarify
the following points:
The range width starts out at 34Columns and is ultimately reduced to 32
(A:AD). And, I've written code that runs prior to this one that deletes the
named ranges I'm trying to create with the below code.

Sheets("1").Select
Range("A1").Select
Range("A1:AF1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="='1'!R1C1:R30C32"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.ClearContents
Range("table1").RowHeight = 15
Range("A1").Select

Names.Add Name - RefersToR1C1
 

i don't get why you xl down xldown xlup

but naming the range can be a lot tidier

with Worksheets("Sheet1")
.Range( .Range("A1"), .Range("AF1").End(xldown) ).Name = "table1"
end with





"VickiMc" wrote:

Hello,
I've tried to Create a Procedure that Creates & Names a Range from scratch
each time the Sub is called so that I can copy & insert it elsewhere in a
Workbook later in the Procedure.

What I've got is a Do Loop that copies and pastes each (criteriated) row
from Sheet1 to Sheet2. Then I've tried to find the size of that range
(sheet2) and name it "table1".
Table1 is then reformatted by copying & pasting the formats from another row
elsewhere in the spreadsheet so that it (table1) can be copied & inserted
into another templated sheet (lets call it sheet3).
I was pretty proud of myself until I realised that the RefersToR1C1 (see
below) overwrites the previous three lines of code essentially defuncting the
entire exercise. Doh!

Before you appraise the (nonsense) code I've been trying to use I'll clarify
the following points:
The range width starts out at 34Columns and is ultimately reduced to 32
(A:AD). And, I've written code that runs prior to this one that deletes the
named ranges I'm trying to create with the below code.

Sheets("1").Select
Range("A1").Select
Range("A1:AF1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="='1'!R1C1:R30C32"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.ClearContents
Range("table1").RowHeight = 15
Range("A1").Select

Names.Add Name - RefersToR1C1
 

Thanks Joel,
In explanation regarding the deletion of the columns (not rows): the Loop
function that copies & pastes from "Sheet1" does so with the entire row. (I
copied and adapted this procedure from another posting on this site.)
Of the 175 Columns containing information, and with the assistance of other
Subs that hide columns on Sheet1, I can manage to get this down to 32 Columns
before the transfer to Sheet2.
Information contained in the first three columns is not essential to the
information that will ultimately be transferred from Sheet2 into the Template
Sheet (30 columns wide including blank columns), but that the transferred
column A contains Nonblank Cells it assists me in the Selecting of the range
for Table1 after which I delete it and ColB, and clear the contents of the
new ColA (was ColC).
(Are you still with me?)
I have repeated the same process to create a 'Table2' & a 'Table3', which
together with 'Table1' are all inserted onto Sheet4 (Template).

Maybe now that I've developed all these snippets and pinned them together,
it's time to go back, re-assess the whole and glue it together properly?

Cheers
And once again, Thank You for your assistance.

"Joel" wrote:

I don't like the fact that you are deleting rows in this code because it
makes selecting the table harder to perform. I don't know which column you
want to use to determine the last row since you are deleting columns A & B.
Try this code. Yo may have to change the column I'm using to get the last
row. You also may want to change the first cell A1 to another cell.


Sub Tst()
Call MakeTable("Sheet1")

End Sub

Sub MakeTable(ShtName As String)
With Sheets(ShtName)
.Columns("A:B").Delete
.Columns("A:A").ClearContents

LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Cells(Rows.Count, LastCol).End(xlUp).Row
Set LastCell = .Cells(LastRow, LastCol)

TableRange = .Range(.Range("A1"), LastCell).Address( _
ReferenceStyle:=xlR1C1, external:=True)

ActiveWorkbook.Names.Add _
Name:="table1", _
RefersToR1C1:="=" & TableRange

.Range("table1").RowHeight = 15
End With
End Sub



"VickiMc" wrote:

Hello,
I've tried to Create a Procedure that Creates & Names a Range from scratch
each time the Sub is called so that I can copy & insert it elsewhere in a
Workbook later in the Procedure.

What I've got is a Do Loop that copies and pastes each (criteriated) row
from Sheet1 to Sheet2. Then I've tried to find the size of that range
(sheet2) and name it "table1".
Table1 is then reformatted by copying & pasting the formats from another row
elsewhere in the spreadsheet so that it (table1) can be copied & inserted
into another templated sheet (lets call it sheet3).
I was pretty proud of myself until I realised that the RefersToR1C1 (see
below) overwrites the previous three lines of code essentially defuncting the
entire exercise. Doh!

Before you appraise the (nonsense) code I've been trying to use I'll clarify
the following points:
The range width starts out at 34Columns and is ultimately reduced to 32
(A:AD). And, I've written code that runs prior to this one that deletes the
named ranges I'm trying to create with the below code.

Sheets("1").Select
Range("A1").Select
Range("A1:AF1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="='1'!R1C1:R30C32"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.ClearContents
Range("table1").RowHeight = 15
Range("A1").Select

Names.Add Name - RefersToR1C1
 

Thanks Patrick,
Because I'm as green as Ireland when it comes to VBA, all I did was record a
Macro and copied the created code into the sub - one too many keystrokes hey?
Love your Code though, I'm gonna set about implementing it straight away.
Regards
Vicki

"Patrick Molloy" wrote:

i don't get why you xl down xldown xlup

but naming the range can be a lot tidier

with Worksheets("Sheet1")
.Range( .Range("A1"), .Range("AF1").End(xldown) ).Name = "table1"
end with





"VickiMc" wrote:

Hello,
I've tried to Create a Procedure that Creates & Names a Range from scratch
each time the Sub is called so that I can copy & insert it elsewhere in a
Workbook later in the Procedure.

What I've got is a Do Loop that copies and pastes each (criteriated) row
from Sheet1 to Sheet2. Then I've tried to find the size of that range
(sheet2) and name it "table1".
Table1 is then reformatted by copying & pasting the formats from another row
elsewhere in the spreadsheet so that it (table1) can be copied & inserted
into another templated sheet (lets call it sheet3).
I was pretty proud of myself until I realised that the RefersToR1C1 (see
below) overwrites the previous three lines of code essentially defuncting the
entire exercise. Doh!

Before you appraise the (nonsense) code I've been trying to use I'll clarify
the following points:
The range width starts out at 34Columns and is ultimately reduced to 32
(A:AD). And, I've written code that runs prior to this one that deletes the
named ranges I'm trying to create with the below code.

Sheets("1").Select
Range("A1").Select
Range("A1:AF1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="='1'!R1C1:R30C32"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.ClearContents
Range("table1").RowHeight = 15
Range("A1").Select

Names.Add Name - RefersToR1C1
 

Thanks Ever So Patrick,
I implemented your Code (with a couple of modifications) and it works
"be-ute-ifully".

The modifications I made were reversing the order of the ranges IE
Range("AF1"), .Range("A1") because ColAF contains no entries, which caused
your original code to highlight all 65K rows.
See, I am learning - with all of your help of course!

"Patrick Molloy" wrote:

i don't get why you xl down xldown xlup

but naming the range can be a lot tidier

with Worksheets("Sheet1")
.Range( .Range("A1"), .Range("AF1").End(xldown) ).Name = "table1"
end with





"VickiMc" wrote:

Hello,
I've tried to Create a Procedure that Creates & Names a Range from scratch
each time the Sub is called so that I can copy & insert it elsewhere in a
Workbook later in the Procedure.

What I've got is a Do Loop that copies and pastes each (criteriated) row
from Sheet1 to Sheet2. Then I've tried to find the size of that range
(sheet2) and name it "table1".
Table1 is then reformatted by copying & pasting the formats from another row
elsewhere in the spreadsheet so that it (table1) can be copied & inserted
into another templated sheet (lets call it sheet3).
I was pretty proud of myself until I realised that the RefersToR1C1 (see
below) overwrites the previous three lines of code essentially defuncting the
entire exercise. Doh!

Before you appraise the (nonsense) code I've been trying to use I'll clarify
the following points:
The range width starts out at 34Columns and is ultimately reduced to 32
(A:AD). And, I've written code that runs prior to this one that deletes the
named ranges I'm trying to create with the below code.

Sheets("1").Select
Range("A1").Select
Range("A1:AF1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveWorkbook.Names.Add Name:="table1", RefersToR1C1:="='1'!R1C1:R30C32"
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.ClearContents
Range("table1").RowHeight = 15
Range("A1").Select