Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
calling from one worksheet sheet to source from another
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copied formulas refer to destination sheet not source sheet | Excel Worksheet Functions | |||
CALLING A RANGE OF CELLS FROM EXTERNAL SOURCE | Excel Discussion (Misc queries) | |||
copying and pasting from source sheet to destination sheet without naming source sht? | Excel Programming | |||
Calling a Sheet | Excel Programming | |||
Calling HTML Source code from within VBA for Excel | Excel Programming |