VB macro - Nested Calls ( Parameter Passing )

[Guest]

Hi,
This is what I want to do:
(1)Open an excel app
(2)Run a macro.;Operate on the excel file (say samp1.xls); Save Samp1.xls
(3)Open another excel file (say Samp2.cls)
(4) Samp2.xls now has to operate on data inside Samp1.xls

Problems I am facing
(1) Currently, I can open, operate on Samp1.xls and save it.
(2) Copy the values to local variables
(3) Open Samp2.xls.
(4) Now, I cant seem to have access to the rows and columns of Samp2.xls
My code looks something like this
-----------------------------------------------------------------------------------------
Sub mc0()
Dim myVAL As Integer

Dim myRangeA As Range
Set myRangeA = Range("A1:A65000")
myRangeA.Select
Workbooks.Open Filename:= "\Samp1.xls

////do some processing and put values into Column A
myVAL = myRangeA.Cells(10,1).Value
//saved value into variable myVAL

ActiveWorkbook.Save Filename: "...\Samp1.xls"
Activeworkbook.close

Workbooks.Open Filename:= "\samp2.xls"
myRangeA.cells(1,1).Value = myVAL

Now, my macro stops in the above line. It complains that myRange is not
accessible.

---------------------------------------------------------------------------------------------
If I try to use call another macro within the above macro, then I cannot
pass the variables ( unless there is a way, and I dont know that! ).
So, the following structure is also usless to me, unless someone can tell me
how to pass parametsrs

Sub mc0()
//open and operate on Samp1.xls
//save valuesinto myVAL (local varaible )
//save and close Samp1.xls
mc1 //call macro mc1
End

Sub mc1()
//open and operate on Samp2.xls
//operate on local varable from Sub mc0()
//Save and close Samp2.xls
End



Thanks,

Deepak

 

[Tom Ogilvy]

myRangeA is specific to the first workbook. A range has a parent which is
its worksheet and the worksheet has a parent which is its workbook.

Sub mc0()
Dim myVAL As Integer
Dim sRange as String
Dim myRangeA As Range
sRange = "A1:A65000"
Set myRangeA = Range(sRange)
myRangeA.Select
Workbooks.Open Filename:= "\Samp1.xls

////do some processing and put values into Column A
myVAL = myRangeA.Cells(10,1).Value
//saved value into variable myVAL

ActiveWorkbook.Save Filename: "...\Samp1.xls"
Activeworkbook.close

Workbooks.Open Filename:= "\samp2.xls"
Activesheet.cells(1,1).Value = myVAL
' or
' Activesheet.Range(sRange).Cells(1,1).Value = myVAL