Re: Macro for EACH workbook-->help modify to ONE macro for ALL wor

Tech-Archive recommends: Speed Up your PC by fixing your registry

Yep.

"Option Explicit" forces you to make sure any variable you use in any procedure
(Sub or Function) has been declared.

If you miss declaring even a single variable in that module, then when you try
to run the procedure with the undeclared variable, it won't run.



dk_ wrote:

Oh! ...Since there was a separation line in the VBA editor above the line
Sub QuoteCopy_Ben(), it looked to me, like it had nothing to do with the
Sub QuoteCopy_Ben() macro. That's why I put Option Explicit after Sub ().

In the module that contains Sub QuoteCopy_Ben(), there are other macros.
Does the Option Explicit line affect the other macros that are above and
below the Sub QuoteCopy_Ben() macro in the same module?

-Dennis

In article <4661A0FB.CBE3EAA2@xxxxxxxxxxxxxxxx>,
Dave Peterson <petersod@xxxxxxxxxxxxxxxx> wrote:

Option explict goes at the top of the module--before any Sub or Function.

It's not part of your macro. It's your way of telling excel/VBA that you
want it to check to make sure all the variables that you use in that module
are declared.

dk_ wrote:

Am I doing something wrong here?

I tried including the line...

Option Explicit

after the line

Sub QuoteCopy_Ben()

and the macro did not run. I got Compile error, and Option Explicit was
highlighted in the VBA Editor. When I included Option Explicit before the
line Sub QuoteCopy_Ben(), it looked to me, that it had nothing to do with
the Sub QuoteCopy_Ben() module.

What's the deal here? ;)

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture


Barb Reinhardt <BarbReinhardt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

From the VBA Help


Option Explicit Statement


Used at module level to force explicit declaration of all variables in
that
module.

Syntax

Option Explicit

Remarks

If used, the Option Explicit statement must appear in a module before any
procedures.

When Option Explicit appears in a module, you must explicitly declare all
variables using the Dim, Private, Public, ReDim, or Static statements. If
you
attempt to use an undeclared variable name, an error occurs at compile
time.

If you don't use the Option Explicit statement, all undeclared variables
are
of Variant type unless the default type is otherwise specified with a
Deftype
statement.

Note Use Option Explicit to avoid incorrectly typing the name of an
existing variable or to avoid confusion in code where the scope of the
variable is not clear.



"dk_" wrote:

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture



Dave Peterson <petersod@xxxxxxxxxxxxxxxx> wrote:

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim Act*** as work***
Dim myDir As String

Application.ScreenUpdating = False

Set Act*** = active***
mydir = ActiveWorkbook.Path
set dkwkbk = Workbooks.Open _
(FileName:=mydir & Application.PathSeparator & "a_DK.xls")

'is Quotearea on the first work*** in a_DK.xls?
'and is Quotedate on the active***?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=act***.range("QuoteDate")

Application.CutCopyMode = False

dkwkbk.close savechanges:=false

Application.ScreenUpdating = True
End Sub

(Untested, watch for typos.)

dk_ wrote:

Below is my macro that works 'perfectly', cross-platform on both
Windows
Excel97 and Mac OS 9, Excel 98, except...

My problem is that I am using '6 edited individual macros', rather
than
just
'a single macro' for 6 workbooks that call the macro.

How can I have my macro set a file name in the macro to that of the
workbook's name that is calling the macro???

I currently run an 'edited copy' of my macro (see below), from
within 6
different workbooks by clicking a button in the open workbook

The macro finds and opens a specific workbook named "a_DK.xls" in
the
current directory and copies a named range "QuoteArea", then
re-activates the workbook that called the macro (e.g."Ben.xls"),
pastes the data, and then finally closes "a_DK.xls", (the
data-source
workbook).

My problem is that in each of 6 copies of my macro, I've had to
change/customize a 'file name' in the macro to that of the 'file
name
of the current active workbook's name' which is calling the macro.

See the 5th line: *_Windows("Ben.xls") *, [in my macro below]...

I've had to customize/change the file name "Ben.xls" to the name of
the
workbook that I call the macro from. (I've had to create a seperate
macro for each of my 6 workbooks).

Sub QuoteCopy_Ben()
Application.ScreenUpdating = False
mydir = ActiveWorkbook.Path
Workbooks.Open FileName:=mydir & Application.PathSeparator &
"a_DK.xls"
Range("QuoteArea").Copy
Windows("Ben.xls").Activate ' <--NEED TO EDIT THE WORKBOOK NAME
HERE
Range("QuoteDate").Select
Active***.Paste
Range("QuoteDate").Select
Windows("a_DSK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

Thanks for loooking.

-Dennis

--
Dennis Kessler
http://www.denniskessler.com/acupuncture


--

Dave Peterson
.

Quantcast