Macro for EACH workbook-->help modify to ONE macro for ALL workbooks?

[dk_]

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
ActiveSheet.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

 

[Dave Peterson]

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

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

Application.CutCopyMode = False

dkwkbk.close savechanges:=false

Application.ScreenUpdating = True
End Sub

(Untested, watch for typos.)

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
ActiveSheet.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

 

[dk_]

Dave, two things...

1) what does "Option Explicit" mean and where does it go?

2) I think you wrote your macro to do something with my data source file
("a_DK.xls"), but this is not the problem, is it?

I may be misunderstanding what you did.

I thought I just needed to be able to set or define, for example
("Ben.xls"), which is the open workbook's name from which I start my
macro.???

Thanks.

-Dennis

--------------------------------------

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

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

Application.CutCopyMode = False

dkwkbk.close savechanges:=false

Application.ScreenUpdating = True
End Sub

(Untested, watch for typos.)

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
ActiveSheet.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

 

[dk_]

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

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

Application.CutCopyMode = False

dkwkbk.close savechanges:=false

Application.ScreenUpdating = True
End Sub

(Untested, watch for typos.)

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
ActiveSheet.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

 

[Guest]

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.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

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

Application.CutCopyMode = False

dkwkbk.close savechanges:=false

Application.ScreenUpdating = True
End Sub

(Untested, watch for typos.)

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
ActiveSheet.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

 

[Dave Peterson]

Woohoo!!!

Option explicit will force you to declare any variables that you use. Without
that, your code may have run, but may not have done what you wanted.
(Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your macro
from the worksheet that gets the data (that owns the range named "QuoteDate").
If you don't start there, the code will fail.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

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

Application.CutCopyMode = False

dkwkbk.close savechanges:=false

Application.ScreenUpdating = True
End Sub

(Untested, watch for typos.)

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
ActiveSheet.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

 

[Dave Peterson]

Saved from a previous post.

I do it for a much more selfish reason.

If I add "Option Explicit" to the top of a module (or have the VBE do it for me
via tools|options|Editor tab|check require variable declaration), I know that
most of my typos will stop my code from compiling.

Then I don't have to spend minutes/hours looking at code like this:
ctr1 = ctrl + 1
(One is ctr-one and one is ctr-ell)
trying to find why my counter isn't incrementing.

And if I declare my variables nicely:

Dim wks as worksheet
not
dim wks as object
and not
dim wks as variant

I get to use the VBE's intellisense.

If I use "dim wks as worksheet", then I can type:
wks.
(including the dot)
and the VBE will pop up a list of all the properties and methods that I can
use. It saves time coding (for me anyway).

And one final selfish reason.

If I use a variable like:

Dim ThisIsACounterOfValidResponses as Long

I can type
Thisis
and hit ctrl-space and the VBE will either complete the variable name or give me
a list of things that start with those characters.

And by using a combination of upper and lower case letters in my variables, the
VBE will match the case found in the declaration statement.

Woohoo!!!

Option explicit will force you to declare any variables that you use. Without
that, your code may have run, but may not have done what you wanted.
(Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your macro
from the worksheet that gets the data (that owns the range named "QuoteDate").
If you don't start there, the code will fail.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[dk_]

Woohoo!!! is Right!!!

Your routine runs on both my old Mac OS 9, and in Excel 97 Windows 98.

Initially I just copied your code, including the line Option explicit. When
I pasted that code into the VB Editor, a separation line was created above
the line Sub QuoteCopy_Ben(). The macro worked.

I then went back a put a single quote (') before "Option explicit", and the
the macro worked once again.

I do/did run the macro while I'm looking at the worksheet that does contain
the range named "QuoteDate" on the sheet.

I did need to add back one line to your macro to make it select the active
cell that I like to keep selected when I close each workbook, i.e.,

Range("QuoteDate").Select

I added that line back just above the line

Application.ScreenUpdating = True

This is really teriffc! Thank you!!!

I will now need to go back and study your macro.

OTH: Your explanation of "Option explicit" went right over my head,
including the part about typos. ...I gotta go have breakfast.


Thank you!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you wanted.
(Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your macro
from the worksheet that gets the data (that owns the range named
"QuoteDate").
If you don't start there, the code will fail.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[Dave Peterson]

Glad it worked for you.

Someday, you'll know what typos are <vbg>.

Woohoo!!! is Right!!!

Your routine runs on both my old Mac OS 9, and in Excel 97 Windows 98.

Initially I just copied your code, including the line Option explicit. When
I pasted that code into the VB Editor, a separation line was created above
the line Sub QuoteCopy_Ben(). The macro worked.

I then went back a put a single quote (') before "Option explicit", and the
the macro worked once again.

I do/did run the macro while I'm looking at the worksheet that does contain
the range named "QuoteDate" on the sheet.

I did need to add back one line to your macro to make it select the active
cell that I like to keep selected when I close each workbook, i.e.,

Range("QuoteDate").Select

I added that line back just above the line

Application.ScreenUpdating = True

This is really teriffc! Thank you!!!

I will now need to go back and study your macro.

OTH: Your explanation of "Option explicit" went right over my head,
including the part about typos. ...I gotta go have breakfast.

Thank you!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you wanted.
(Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your macro
from the worksheet that gets the data (that owns the range named
"QuoteDate").
If you don't start there, the code will fail.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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


Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[dk_]

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

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.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[Dave Peterson]

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.

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

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.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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




Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[dk_]

Dave,

I tried to use your instruction (just below), but I couldn't figure out how
to implement it. Would you show me how I can use this line in my original
macro?

"This is the line that I think you had concerns about:
Set ActSheet = activesheet"


Thanks for your help and for the lessons!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you
wanted. (Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your
macro from the worksheet that gets the data (that owns the range named
"QuoteDate"). If you don't start there, the code will fail.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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

Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[dk_]

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

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.

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

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.



:

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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




Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[Dave Peterson]

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.

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

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.

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



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.



:

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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




Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[Dave Peterson]

If you're opening other workbooks or switching to other worksheets (in the same
workbook or a different workbook), it's sometimes nice to know where you
started.

Set ActSheet = ActiveSheet

is the code that sets a variable that keeps track of where you were.

In your code, I guessed that you wanted everything done to whatever sheet was
active when you started the macro.

If you look back at the suggested code, you'll see that before anything really
happens, there's that line that sets that variable.

Later in the code, that same variable is used to qualify what ranges should be
used--the range named QuoteDate on the worksheet that was active when you
started the macro.

Dave,

I tried to use your instruction (just below), but I couldn't figure out how
to implement it. Would you show me how I can use this line in my original
macro?

"This is the line that I think you had concerns about:
Set ActSheet = activesheet"

Thanks for your help and for the lessons!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you
wanted. (Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your
macro from the worksheet that gets the data (that owns the range named
"QuoteDate"). If you don't start there, the code will fail.

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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


Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[dk_]

Dave,

I followed your explanation (just below), and that was what I assumed the
Set line was supposed to work, but I still couldn't get 'ActSheet' to work
in my original macro. Here's what I tried...

I used your set line...

Set ActSheet = ActiveSheet

as one of the frist few lines in my original macro, and then I inserted
("ActSheet") in place of ("Ben.xls") as in the following line...

Windows("Ben.xls").Activate

What am I missing or misunderstanding?

-Dennis

If you're opening other workbooks or switching to other worksheets (in the
same workbook or a different workbook), it's sometimes nice to know where
you started.

Set ActSheet = ActiveSheet

is the code that sets a variable that keeps track of where you were.

In your code, I guessed that you wanted everything done to whatever sheet
was active when you started the macro.

If you look back at the suggested code, you'll see that before anything
really happens, there's that line that sets that variable.

Later in the code, that same variable is used to qualify what ranges should
be used--the range named QuoteDate on the worksheet that was active when
you started the macro.

Dave,

I tried to use your instruction (just below), but I couldn't figure out how
to implement it. Would you show me how I can use this line in my original
macro?

"This is the line that I think you had concerns about:
Set ActSheet = activesheet"

Thanks for your help and for the lessons!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you
wanted. (Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your
macro from the worksheet that gets the data (that owns the range named
"QuoteDate"). If you don't start there, the code will fail.




dk_ wrote:

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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


Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[Dave Peterson]

Is the active sheet a worksheet or a chart sheet or what?????

And if you changed the code, I think it's time to share what you're currently
running.

Dave,

I followed your explanation (just below), and that was what I assumed the
Set line was supposed to work, but I still couldn't get 'ActSheet' to work
in my original macro. Here's what I tried...

I used your set line...

Set ActSheet = ActiveSheet

as one of the frist few lines in my original macro, and then I inserted
("ActSheet") in place of ("Ben.xls") as in the following line...

Windows("Ben.xls").Activate

What am I missing or misunderstanding?

-Dennis

If you're opening other workbooks or switching to other worksheets (in the
same workbook or a different workbook), it's sometimes nice to know where
you started.

Set ActSheet = ActiveSheet

is the code that sets a variable that keeps track of where you were.

In your code, I guessed that you wanted everything done to whatever sheet
was active when you started the macro.

If you look back at the suggested code, you'll see that before anything
really happens, there's that line that sets that variable.

Later in the code, that same variable is used to qualify what ranges should
be used--the range named QuoteDate on the worksheet that was active when
you started the macro.

Dave,

I tried to use your instruction (just below), but I couldn't figure out how
to implement it. Would you show me how I can use this line in my original
macro?

"This is the line that I think you had concerns about:
Set ActSheet = activesheet"

Thanks for your help and for the lessons!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you
wanted. (Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you start your
macro from the worksheet that gets the data (that owns the range named
"QuoteDate"). If you don't start there, the code will fail.




dk_ wrote:

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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


Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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
ActiveSheet.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

 

[dk_]

Dave,

1) The active sheet is a worksheet, yes!!!!

I see that I have edited the 'name' on the worksheet tab, in each of my 6
workbooks, if that makes any difference. And there is a named range on each
of the 6 worksheet/workbooks named: "QuoteArea".

I have a button on each sheet that I click on, to run the macro. I have 6
workbooks that I look at, one at a time, and I manually click a button on
the sheet in the workbook that I currently have open. This updates my data,
on demand. The button runs my macro (that is shown at the bottom of this
page). Now it runs your macro.


2) What do you mean, 'share what you're currently running'?

I am now using the marco that you wrote, and it works!
(Thank your very much!)...

....But, I'm trying to learn by doing, how I could have made my original
macro, (shown at the very bottom of this page), work for each of my 6
workbooks. What you see, (waaaay below), is what I was running.

I was originally thinking that some small tweak to my macro that would 'Set'
or mark the name of the worksheet/workbook that I run the macro from, would
have allowed me to use a single macro routine, rather than 6 macros, (one
for each of my worksheet/workbooks). I'd still like to understand how to do
this.

-Dennis

Is the active sheet a worksheet or a chart sheet or what?????

And if you changed the code, I think it's time to share what you're currently
running.

Dave,

I followed your explanation (just below), and that was what I assumed the
Set line was supposed to work, but I still couldn't get 'ActSheet' to work
in my original macro. Here's what I tried...

I used your set line...

Set ActSheet = ActiveSheet

as one of the frist few lines in my original macro, and then I inserted
("ActSheet") in place of ("Ben.xls") as in the following line...

Windows("Ben.xls").Activate

What am I missing or misunderstanding?

-Dennis

If you're opening other workbooks or switching to other worksheets (in
the same workbook or a different workbook), it's sometimes nice to
know where you started.

Set ActSheet = ActiveSheet

is the code that sets a variable that keeps track of where you were.

In your code, I guessed that you wanted everything done to whatever sheet
was active when you started the macro.

If you look back at the suggested code, you'll see that before anything
really happens, there's that line that sets that variable.

Later in the code, that same variable is used to qualify what ranges
should be used--the range named QuoteDate on the worksheet that was
active when you started the macro.

dk_ wrote:

Dave,

I tried to use your instruction (just below), but I couldn't figure
out how to implement it. Would you show me how I can use this line
in my original macro?

"This is the line that I think you had concerns about:
Set ActSheet = activesheet"

Thanks for your help and for the lessons!

-Dennis

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

Woohoo!!!

Option explicit will force you to declare any variables that you use.
Without that, your code may have run, but may not have done what you
wanted. (Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

This is the line that I think you had concerns about:
Set ActSheet = activesheet

So if you want your macro to work, you have to make sure you
start your macro from the worksheet that gets the data (that owns
the range named "QuoteDate"). If you don't start there, the code
will fail.




dk_ wrote:

It works of the PC!

I haven't tested the Mac yet.

Whoa!

-Dennis

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


Maybe...

Option Explicit
Sub QuoteCopy_Ben()
Dim DKWkbk as workbook
Dim ActSheet as worksheet
Dim myDir As String

Application.ScreenUpdating = False

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

'is Quotearea on the first worksheet in a_DK.xls?
'and is Quotedate on the activesheet?

dkwkbk.worksheets(1).range("quotearea").copy _
destination:=actsheet.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" '<--This is on the line above
Range("QuoteArea").Copy
Windows("Ben.xls").Activate '<--NEED TO EDIT THE WORKBOOK
' NAME HERE
Range("QuoteDate").Select
ActiveSheet.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

 

[dk_]

Dave,

I got ActSheet to work...


1) I added the line near the top of the macro...

Set ActSheet = ActiveSheet


2) Then I changed the line...

Windows ("Ben.xls").activate
to...
ActSheet.Activate

and now my original macro works for each of my 6 workbooks!



Q) Why did I NOT NEED to also have this line?...

Dim ActSheet as Worksheet

-Dennis

 

[Dave Peterson]

Glad you got it working, but I don't understand your comments.

If you look at the original code that I suggested, there was a line that
declared ActSheet. So I think that you made a change (however minor) to that
suggested code (or changed your own code???). That's why I asked to see the
code that you were currently running.