naming range of cells and moving from another book

POCKAR · Joined: 04 Oct 2007 Posts: 1

I am currently trying to copy cells from one book to a new workbook. I then
name that group of cells in the new workbook. This is repeated multiple
times. This would work for me if the copying and pasting was to remain the
same, but this is not the case. I am finding that the new named range is
remaining in the same area even though the code that the name is supposed to
referance is elsewhere

PLEASE HELP!!

Heres my sample code

Windows("Brick Block Takeoff.xls").Activate
Application.Goto Reference:="REBAR"
Selection.Copy
Windows(filenameTakeoff & ".xls").Activate
Application.Goto Reference:="start"
Selection.Insert Shift:=xlDown
Application.Goto Reference:="start"
ActiveCell.Offset(-7, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="START1", RefersToR1C1:="=Sheet1!R20C1"
ActiveCell.Offset(6, 6 Cool

.Range("A1").Select
ActiveWorkbook.Names.Add Name:="FINISH1", RefersToR1C1:="=Sheet1!R26C69"
ActiveCell.Offset(-6, -6 Cool

.Range("START1:FINISH1").Select
ActiveCell.Activate
ActiveWorkbook.Names.Add Name:="REBAR190", RefersToR1C1:= _
"=Sheet1!R20C1:R26C69"

I need to find out how to change the RefersToR1C1:="=Sheet1!R26C69" to
something that isnt fixed in place. This way if i do insert something before
this group of cells it will still name the right range of cells.

Archived from group: microsoft>public>vb>syntax

Veign · Joined: 04 Oct 2007 Posts: 1759

Question would be better asked and thus better answered in the Excel
Programming newsgroup...

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--
Read. Decide. Sign the petition to Microsoft.
http://classicvb.org/petition/

"POCKAR" wrote in message@microsoft.com...
> I am currently trying to copy cells from one book to a new workbook. I
then
> name that group of cells in the new workbook. This is repeated multiple
> times. This would work for me if the copying and pasting was to remain the
> same, but this is not the case. I am finding that the new named range is
> remaining in the same area even though the code that the name is supposed
to
> referance is elsewhere
>
> PLEASE HELP!!
>
> Heres my sample code
>
> Windows("Brick Block Takeoff.xls").Activate
> Application.Goto Reference:="REBAR"
> Selection.Copy
> Windows(filenameTakeoff & ".xls").Activate
> Application.Goto Reference:="start"
> Selection.Insert Shift:=xlDown
> Application.Goto Reference:="start"
> ActiveCell.Offset(-7, 0).Range("A1").Select
> Application.CutCopyMode = False
> ActiveWorkbook.Names.Add Name:="START1", RefersToR1C1:="=Sheet1!R20C1"
> ActiveCell.Offset(6, 6 Cool

.Range("A1").Select
> ActiveWorkbook.Names.Add Name:="FINISH1",
RefersToR1C1:="=Sheet1!R26C69"
> ActiveCell.Offset(-6, -6 Cool

.Range("START1:FINISH1").Select
> ActiveCell.Activate
> ActiveWorkbook.Names.Add Name:="REBAR190", RefersToR1C1:= _
> "=Sheet1!R20C1:R26C69"
>
> I need to find out how to change the RefersToR1C1:="=Sheet1!R26C69" to
> something that isnt fixed in place. This way if i do insert something
before
> this group of cells it will still name the right range of cells.
>
>

Noah · Joined: 04 Oct 2007 Posts: 1

Noah wrote:
> *See the attached text file. I have been working on the same issue.
> You will have to change file names as applicable. I am not an expert
> on this issue, but this works for me. You create named ranges and
> copy and paste them. If you have any questions, feel free to email
> me. *

Workbooks.Open Filename:="Z:\NCochran\ad62206.xls"
Range("A1").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Select
ActiveSheet.Names.Add Name:="StartTVPaste", RefersTo:="=" &
Selection.Address()
Workbooks.Open Filename:="Z:\NCochran\tv62206.xls"
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Names.Add Name:="TV", RefersTo:="=" &
Selection.Address()
Application.Goto Reference:="TV"
Selection.Copy
Windows("ad62206.xls").Activate
Application.Goto Reference:="StartTVPaste"
ActiveSheet.Paste
Application.Goto Reference:="StartTVPaste"
Application.CutCopyMode = False
Selection.EntireRow.Delete
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Select
ActiveSheet.Names.Add Name:="StartRadioPaste", RefersTo:="=" &
Selection.Address()
Workbooks.Open Filename:="Z:\NCochran\rad62206.xls"
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Names.Add Name:="Rad", RefersTo:="=" &
Selection.Address()
Application.Goto Reference:="Rad"
Selection.Copy
Windows("ad62206.xls").Activate
Application.Goto Reference:="StartRadioPaste"
ActiveSheet.Paste
Application.Goto Reference:="StartRadioPaste"
Application.CutCopyMode = False
Selection.EntireRow.Delete
ActiveWorkbook.SaveAs Filename:="Z:\NCochran\combined.xls",
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

--
Noah
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

Noah · Joined: 04 Oct 2007 Posts: 1

See the attached text file. I have been working on the same issue. You
will have to change file names as applicable. I am not an expert on
this issue, but this works for me. You create named ranges and copy
and paste them. If you have any questions, feel free to email me.

POCKAR wrote:
> *I am currently trying to copy cells from one book to a new workbook.
> I then
> name that group of cells in the new workbook. This is repeated
> multiple
> times. This would work for me if the copying and pasting was to
> remain the
> same, but this is not the case. I am finding that the new named range
> is
> remaining in the same area even though the code that the name is
> supposed to
> referance is elsewhere
>
> PLEASE HELP!!
>
> Heres my sample code
>
> Windows("Brick Block Takeoff.xls").Activate
> Application.Goto Reference:="REBAR"
> Selection.Copy
> Windows(filenameTakeoff & ".xls").Activate
> Application.Goto Reference:="start"
> Selection.Insert Shift:=xlDown
> Application.Goto Reference:="start"
> ActiveCell.Offset(-7, 0).Range("A1").Select
> Application.CutCopyMode = False
> ActiveWorkbook.Names.Add Name:="START1",
> RefersToR1C1:="=Sheet1!R20C1"
> ActiveCell.Offset(6, 6 Cool

.Range("A1").Select
> ActiveWorkbook.Names.Add Name:="FINISH1",
> RefersToR1C1:="=Sheet1!R26C69"
> ActiveCell.Offset(-6, -6 Cool

.Range("START1:FINISH1").Select
> ActiveCell.Activate
> ActiveWorkbook.Names.Add Name:="REBAR190", RefersToR1C1:= _
> "=Sheet1!R20C1:R26C69"
>
> I need to find out how to change the RefersToR1C1:="=Sheet1!R26C69"
> to
> something that isnt fixed in place. This way if i do insert something
> before
> this group of cells it will still name the right range of cells. *

+----------------------------------------------------------------+
| Attachment filename: solution.txt |
|Download attachment: http://www.codecomments.com/attachment.php?postid=2946927 |
+----------------------------------------------------------------+

--
Noah
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

Freezing cells in Excel Hello all I have a question that I can't find the answer to concerning freezing cells in Excel from my VB6 app. My app exports a sizable chunk of data to an Excel spreadsheet, (Excel 2003 and 2002). As the datasheet is quite large, I want to freeze the co

Type Mismatch on RangeObj and Cells.Find When Cells.find is null the script returns an error 91 (stops dead). According to some MS help I found at the URL below I should be able to use Set RangeObj = Cells.Find (...) in order to evaluate the result. However I always get a Type mismatch. The Ce

Date Range Hi All, I need to check if a date entered is within a date range specified by two MonthView controls - MonthView(0), which controls the beginning of the range, and MonthView(1), which controls the end of the range. Also, if the date entered is +1 day fro

data range hi I don't know if I am in the right news group, so I will apologise now if I am I am using excel and I want to filter down data automatically i can do this in excel if i write approx 6000 formulas (few to many) I want to do is check data from database

subscript out of range (why) When I run the code below I get an "subscript out of range" error. I don't understand why. Can some answer the question? Dim TempText As String Dim As Byte Dim As Byte Dim ArrayPosition As Integer Dim A