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