calling from one worksheet sheet to source from another

[SteveDB1]

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.

 

[Joel]

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

 

[Joel]

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

 

[Dave Peterson]

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

 

[SteveDB1]

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]

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]

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.

 

[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.