Hello.
I'm working on a macro to link two worksheets.
I've written something that appears to work, but when I went to test it I
found that it picks the range on the primary worksheet.
I need to have it select the range on the secondary worksheet.
E.g.,

MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select

Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
input box.

How do I get the MyRngPer to select the range desired from another worksheet?

Thank you.

The best way to reference cell when more than one workbook is oopen is to
remember at every instruction to reference a workbook.

To reference the workbook where the macro is located use
Thisworkbook.sheets("sheet1").Range("A1").select

When you create a new workbook or open a workbook it autoimatically becomes
the active workbook so do something like this

workbooks.add
set newbk = activeworkbook

or
workbooks.open filename:="C:\temp\abc.xls"
set newbk = activeworkbook

Your code is wrong in selecting a range, you need a set statement. also you
must have a sheet reference.



newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
or
set MyRngPer = newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA))
MyRngPer.select



then
"SteveDB1" wrote:

> Hello.
> I'm working on a macro to link two worksheets.
> I've written something that appears to work, but when I went to test it I
> found that it picks the range on the primary worksheet.
> I need to have it select the range on the secondary worksheet.
> E.g.,
>
> MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
>
> Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> input box.
>
> How do I get the MyRngPer to select the range desired from another worksheet?
>
> Thank you.
>

I just noticed something else. Excel has problem remebering that the Range
and Cell in the statement below are on the same worksheets. You need to do
something like this. Notice I have three dots (before range and cells)

with newbk.sheets("Sheet1")

set MyRngPer = .Range(.Cells(NuB, NuA), .Cells(NuC, NuA))

end with

"SteveDB1" wrote:

> Hello.
> I'm working on a macro to link two worksheets.
> I've written something that appears to work, but when I went to test it I
> found that it picks the range on the primary worksheet.
> I need to have it select the range on the secondary worksheet.
> E.g.,
>
> MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
>
> Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> input box.
>
> How do I get the MyRngPer to select the range desired from another worksheet?
>
> Thank you.
>

You could even do:
set newbk = workbooks.open(filename:="C:\temp\abc.xls")

And remember if you're going to select a range, then the worksheet has to be
selected and the workbook has to be active.

with newbk.sheets("Sheet1")
.select 'newbk must be active
set MyRngPer = .Range(.Cells(NuB, NuA), .Cells(NuC, NuA))
myrngper.select
end with


Joel wrote:
>
> The best way to reference cell when more than one workbook is oopen is to
> remember at every instruction to reference a workbook.
>
> To reference the workbook where the macro is located use
> Thisworkbook.sheets("sheet1").Range("A1").select
>
> When you create a new workbook or open a workbook it autoimatically becomes
> the active workbook so do something like this
>
> workbooks.add
> set newbk = activeworkbook
>
> or
> workbooks.open filename:="C:\temp\abc.xls"
> set newbk = activeworkbook
>
> Your code is wrong in selecting a range, you need a set statement. also you
> must have a sheet reference.
>
> newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> or
> set MyRngPer = newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA))
> MyRngPer.select
>
> then
> "SteveDB1" wrote:
>
> > Hello.
> > I'm working on a macro to link two worksheets.
> > I've written something that appears to work, but when I went to test it I
> > found that it picks the range on the primary worksheet.
> > I need to have it select the range on the secondary worksheet.
> > E.g.,
> >
> > MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> >
> > Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> > input box.
> >
> > How do I get the MyRngPer to select the range desired from another worksheet?
> >
> > Thank you.
> >

--

Dave Peterson

Hi.
I want to link two worksheets together. These are within the same workbook.

How would I link two worksheets within the same workbook?

I tried the :

set MyRngPer = Sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA))
MyRngPer.select

and it did not work.
Do I need to include the workbook name?


"Joel" wrote:

> I just noticed something else. Excel has problem remebering that the Range
> and Cell in the statement below are on the same worksheets. You need to do
> something like this. Notice I have three dots (before range and cells)
>
> with newbk.sheets("Sheet1")
>
> set MyRngPer = .Range(.Cells(NuB, NuA), .Cells(NuC, NuA))
>
> end with
>
> "SteveDB1" wrote:
>
> > Hello.
> > I'm working on a macro to link two worksheets.
> > I've written something that appears to work, but when I went to test it I
> > found that it picks the range on the primary worksheet.
> > I need to have it select the range on the secondary worksheet.
> > E.g.,
> >
> > MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> >
> > Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> > input box.
> >
> > How do I get the MyRngPer to select the range desired from another worksheet?
> >
> > Thank you.
> >

Dave: You are wrong. Workbook and Sheet do not have to be activated and
selected. The code below works

Thisworkbook.sheets("Sheet1").Range("A5:B10").copy _
destination:=newbk.sheets("Sheet2").Range("A1")


"Dave Peterson" wrote:

> You could even do:
> set newbk = workbooks.open(filename:="C:\temp\abc.xls")
>
> And remember if you're going to select a range, then the worksheet has to be
> selected and the workbook has to be active.
>
> with newbk.sheets("Sheet1")
> .select 'newbk must be active
> set MyRngPer = .Range(.Cells(NuB, NuA), .Cells(NuC, NuA))
> myrngper.select
> end with
>
>
> Joel wrote:
> >
> > The best way to reference cell when more than one workbook is oopen is to
> > remember at every instruction to reference a workbook.
> >
> > To reference the workbook where the macro is located use
> > Thisworkbook.sheets("sheet1").Range("A1").select
> >
> > When you create a new workbook or open a workbook it autoimatically becomes
> > the active workbook so do something like this
> >
> > workbooks.add
> > set newbk = activeworkbook
> >
> > or
> > workbooks.open filename:="C:\temp\abc.xls"
> > set newbk = activeworkbook
> >
> > Your code is wrong in selecting a range, you need a set statement. also you
> > must have a sheet reference.
> >
> > newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> > or
> > set MyRngPer = newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA))
> > MyRngPer.select
> >
> > then
> > "SteveDB1" wrote:
> >
> > > Hello.
> > > I'm working on a macro to link two worksheets.
> > > I've written something that appears to work, but when I went to test it I
> > > found that it picks the range on the primary worksheet.
> > > I need to have it select the range on the secondary worksheet.
> > > E.g.,
> > >
> > > MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> > >
> > > Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> > > input box.
> > >
> > > How do I get the MyRngPer to select the range desired from another worksheet?
> > >
> > > Thank you.
> > >
>
> --
>
> Dave Peterson
>

I didn't say that the worksheet and range had to be selected to do the work.

I said that if you select the range, then the worksheet had to be selected.

There's a difference.

Joel wrote:
>
> Dave: You are wrong. Workbook and Sheet do not have to be activated and
> selected. The code below works
>
> Thisworkbook.sheets("Sheet1").Range("A5:B10").copy _
> destination:=newbk.sheets("Sheet2").Range("A1")
>
> "Dave Peterson" wrote:
>
> > You could even do:
> > set newbk = workbooks.open(filename:="C:\temp\abc.xls")
> >
> > And remember if you're going to select a range, then the worksheet has to be
> > selected and the workbook has to be active.
> >
> > with newbk.sheets("Sheet1")
> > .select 'newbk must be active
> > set MyRngPer = .Range(.Cells(NuB, NuA), .Cells(NuC, NuA))
> > myrngper.select
> > end with
> >
> >
> > Joel wrote:
> > >
> > > The best way to reference cell when more than one workbook is oopen is to
> > > remember at every instruction to reference a workbook.
> > >
> > > To reference the workbook where the macro is located use
> > > Thisworkbook.sheets("sheet1").Range("A1").select
> > >
> > > When you create a new workbook or open a workbook it autoimatically becomes
> > > the active workbook so do something like this
> > >
> > > workbooks.add
> > > set newbk = activeworkbook
> > >
> > > or
> > > workbooks.open filename:="C:\temp\abc.xls"
> > > set newbk = activeworkbook
> > >
> > > Your code is wrong in selecting a range, you need a set statement. also you
> > > must have a sheet reference.
> > >
> > > newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> > > or
> > > set MyRngPer = newbk.sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA))
> > > MyRngPer.select
> > >
> > > then
> > > "SteveDB1" wrote:
> > >
> > > > Hello.
> > > > I'm working on a macro to link two worksheets.
> > > > I've written something that appears to work, but when I went to test it I
> > > > found that it picks the range on the primary worksheet.
> > > > I need to have it select the range on the secondary worksheet.
> > > > E.g.,
> > > >
> > > > MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> > > >
> > > > Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> > > > input box.
> > > >
> > > > How do I get the MyRngPer to select the range desired from another worksheet?
> > > >
> > > > Thank you.
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Read Joel's instructions again and try it with the With/end with structure.

And it wouldn't hurt to specify the workbook if sheets("sheet1") isn't in the
activeworkbook.

SteveDB1 wrote:
>
> Hi.
> I want to link two worksheets together. These are within the same workbook.
>
> How would I link two worksheets within the same workbook?
>
> I tried the :
>
> set MyRngPer = Sheets("Sheet1").Range(Cells(NuB, NuA), Cells(NuC, NuA))
> MyRngPer.select
>
> and it did not work.
> Do I need to include the workbook name?
>
> "Joel" wrote:
>
> > I just noticed something else. Excel has problem remebering that the Range
> > and Cell in the statement below are on the same worksheets. You need to do
> > something like this. Notice I have three dots (before range and cells)
> >
> > with newbk.sheets("Sheet1")
> >
> > set MyRngPer = .Range(.Cells(NuB, NuA), .Cells(NuC, NuA))
> >
> > end with
> >
> > "SteveDB1" wrote:
> >
> > > Hello.
> > > I'm working on a macro to link two worksheets.
> > > I've written something that appears to work, but when I went to test it I
> > > found that it picks the range on the primary worksheet.
> > > I need to have it select the range on the secondary worksheet.
> > > E.g.,
> > >
> > > MyRngPer = Range(Cells(NuB, NuA), Cells(NuC, NuA)).Select
> > >
> > > Where the NuA, NuB, and NuC are variables I've dim'd, and input through an
> > > input box.
> > >
> > > How do I get the MyRngPer to select the range desired from another worksheet?
> > >
> > > Thank you.
> > >

--

Dave Peterson