V
VickiMc
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
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