Knowing from where macro started

[Rob]

Excel 2000

I am starting a macro from a spreadsheet that opens another file which then
carries out some operations. What I then need to do is copy from my
original file (where the macro started a range of data and paste it into the
newly opened file.

I can't hard code what directory or the name of the original file.

I'm thinking that I'll need to capture the original files path and name and
somehow use this in some copy and paste code.

Any pointers most welcome.

Thanks, Rob

 

[Frank Kabel]

Hi Rob
in your macro use something like the following
sub foo()
Dim source_wbk as workbook
Dim source_wks as worksheet
Set source_wbk = ActiveWorkbook
set source_wks = Activesheet
' do some code
'....
'....
msgbox "Now going back to: " & source_wbk.name _
& ", " source_wks.name<
source_wbk.activate
source_wks.activate
end sub

 

[Trevor Shuttleworth]

Rob

the workbook where the code is can be referred to as "ThisWorkbook". For
Example, "With ThisWorkbook" or "ThisWorkbook.Activate". The Workbook that
is selected is the "ActiveWorkbook"

For example:

MsgBox ThisWorkbook.Name
MsgBox ActiveWorkbook.Name
Workbooks("book2").Activate
MsgBox ThisWorkbook.Name
MsgBox ActiveWorkbook.Name
ThisWorkbook.Activate
MsgBox ThisWorkbook.Name
MsgBox ActiveWorkbook.Name


Regards

Trevor

 

[Chip Pearson]

Rob,

The ThisWorkbook object will return a reference to the workbook
that contains the executing code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Rob]

Frank,

Thanks very much I'll use as suggested.

Regards, Rob

 

[Rob]

Thanks everyone, This Workbook does the job.

Rob

Rob,

The ThisWorkbook object will return a reference to the workbook
that contains the executing code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com