I'm just starting out...
How do I get my macro to use the active worksheet.
before I used the filename:
With Workbooks("Calculatron.xls")
but I had two copies of the file, and the macro would only work in the
original file.
thank you...

Your with statement is actually pointing at that specific workbook.

You can use this:
with activeworkbook
(for the workbook)

or
with activesheet
(for the active sheet)



newbie wrote:

I'm just starting out...
How do I get my macro to use the active worksheet.
before I used the filename:
With Workbooks("Calculatron.xls")
but I had two copies of the file, and the macro would only work in the
original file.
thank you...

--

Dave Peterson

Thanks Dave,
I've tried both methods and they didn't work. I also changed the
filename and it doesn't make a difference.
It says the file cannot be accessed.
This file is on a netork hard-drive. The original on my external hard
drive works fine.
What do you think?
newbie

Dave Peterson wrote:
Your with statement is actually pointing at that specific workbook.

You can use this:
with activeworkbook
(for the workbook)

or
with activesheet
(for the active sheet)



newbie wrote:

I'm just starting out...
How do I get my macro to use the active worksheet.
before I used the filename:
With Workbooks("Calculatron.xls")
but I had two copies of the file, and the macro would only work in the
original file.
thank you...

--

Dave Peterson

If you want your code to act on the data on the activesheet, then that workbook
has to be open--and the correct sheet has to be active.

If your code needs to open the workbook to work on it, you can include that in
your procedure, too.

dim myFileName as Variant
dim Wkbk as workbook
dim wks as worksheet

myfilename = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")

if myfilename = false then
'user hit cancel
exit sub
end if

set wkbk = workbooks.open(filename:=myfilename)

set wks = wkbk.worksheets("sheet1") 'whichever you want

with wks
'do stuff
end with

wkbk.close savechanges:=true



newbie wrote:

Thanks Dave,
I've tried both methods and they didn't work. I also changed the
filename and it doesn't make a difference.
It says the file cannot be accessed.
This file is on a netork hard-drive. The original on my external hard
drive works fine.
What do you think?
newbie

Dave Peterson wrote:
Your with statement is actually pointing at that specific workbook.

You can use this:
with activeworkbook
(for the workbook)

or
with activesheet
(for the active sheet)



newbie wrote:

I'm just starting out...
How do I get my macro to use the active worksheet.
before I used the filename:
With Workbooks("Calculatron.xls")
but I had two copies of the file, and the macro would only work in the
original file.
thank you...

--

Dave Peterson

--

Dave Peterson