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

  • Thread starter Thread starter dk_
  • Start date Start date
Dave,

Below are three WORKING versions of the data copy macro...

1. The first is the first that you posted, i.e., a
complete re-do of mine; --> ONE macro for ALL workbooks.

2. The second is the "Set ActSheet" modification of my original;
ONE macro for ALL workbooks.


Q). In this version (#2 below), why did I not need the line?...

Dim ActSheet As Worksheet


3. The third is my original, which
works only for ONE "named" workbook.


I did see that in my very first post,
there was a TYPO in the name of my source data file.

All three of these macros work cross platform,
(Mac OS 9, and Windows 98se Excel 97)...


1...
Sub QuoteCopy_ActiveSheet_to() ' <--[Dave's ONE macro for ALL]
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")
DKWkbk.Worksheets(1).Range("quotearea").Copy _
Destination:=ActSheet.Range("QuoteDate")
Application.CutCopyMode = False
DKWkbk.Close savechanges:=False
Range("QuoteDate").Select
Application.ScreenUpdating = True
End Sub

2...
Sub QuoteCopy_BenDaveSET() ' <--[Dave's "Set ActSheet", ONE for ALL]
Application.ScreenUpdating = False
Set ActSheet = ActiveSheet
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
ActSheet.Activate ' <--[This line replaces the line below]
' Windows("Ben.xls").Activate <--[This line is COMMENTED OUT]
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

3...
Sub QuoteCopy_Ben() ' <--[My Original Macro, ONE for EACH workbook]
Application.ScreenUpdating = False
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
Windows("Ben.xls").Activate
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub
 
For #2. Technically, you don't need to declare your variables.

If you don't put "Option Explicit" at the top of the module that contains that
procedure, you don't need to declare any variable. I think that this is a very,
very bad idea. I think each variable should be declared--and declared as the
correct type ("as range", "as workbook", "as worksheet", "as long", "as
variant").

But there's no law that says you have to do this. But if you post back with a
question about why your procedure doesn't work as expected and it's becaused of
a typo that would have been caught if you had used "option Explicit", then
expect some grief <vbg>.

Personally, I don't want to spend minutes/hours debugging these kinds of errors
and I like the intellisense and autocomplete that I get with declared variables.

===
This was the same reason (lack of "option Explicit") that you didn't need the
"dim myDir as string" in the 2nd procedure, too.

But if you would have mistyped this line:

Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
as
Workbooks.Open FileName:=miDir & Application.PathSeparator & "a_DK.xls"

it might have taken a little time to notice that typo

=======
If you decide that you want to declare those variables (and I think you should),
then you can have excel help you.

Inside the VBE:
Tools|Options|Editor tab|check "require variable declaration"

Then each new module that you create will have "option explicit" added to the
top--you don't need to type it.

=====
ps. If you're looking through any of my posts, you'll see that I try to include
that "option explicit" on any procedure I post. It's my passive-aggressive way
of trying to make people do what I want <vvbg>.

pps. I would also modify that first macro.

Instead of this line:

Range("QuoteDate").Select

I'd use:
ActSheet.Select
actsheet.Range("QuoteDate").Select

I don't like to just rely on excel to determine the activesheet. It may be
overkill in 99.99999% of the cases, but sometimes....


dk_ said:
Dave,

Below are three WORKING versions of the data copy macro...

1. The first is the first that you posted, i.e., a
complete re-do of mine; --> ONE macro for ALL workbooks.

2. The second is the "Set ActSheet" modification of my original;
ONE macro for ALL workbooks.

Q). In this version (#2 below), why did I not need the line?...

Dim ActSheet As Worksheet

3. The third is my original, which
works only for ONE "named" workbook.

I did see that in my very first post,
there was a TYPO in the name of my source data file.

All three of these macros work cross platform,
(Mac OS 9, and Windows 98se Excel 97)...

1...
Sub QuoteCopy_ActiveSheet_to() ' <--[Dave's ONE macro for ALL]
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")
DKWkbk.Worksheets(1).Range("quotearea").Copy _
Destination:=ActSheet.Range("QuoteDate")
Application.CutCopyMode = False
DKWkbk.Close savechanges:=False
Range("QuoteDate").Select
Application.ScreenUpdating = True
End Sub

2...
Sub QuoteCopy_BenDaveSET() ' <--[Dave's "Set ActSheet", ONE for ALL]
Application.ScreenUpdating = False
Set ActSheet = ActiveSheet
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
ActSheet.Activate ' <--[This line replaces the line below]
' Windows("Ben.xls").Activate <--[This line is COMMENTED OUT]
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

3...
Sub QuoteCopy_Ben() ' <--[My Original Macro, ONE for EACH workbook]
Application.ScreenUpdating = False
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
Windows("Ben.xls").Activate
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

Dave Peterson said:
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.
Click to expand...
Click to expand...
 
Dave,

Thank you very much for your help, your patience,
and for the excellent lessons!

Very much appreciated!



I am curious why the following statement needed () around the file's path?...


Set DKWkbk = Workbooks.Open _
(FileName:=myDir & Application.PathSeparator & "a_DK.xls")



And in another version of the macro, the ()'s were not needed?...


myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"



Thank you, once again. ;)

-Dennis

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


Dave Peterson said:
For #2. Technically, you don't need to declare your variables.

If you don't put "Option Explicit" at the top of the module that contains
that procedure, you don't need to declare any variable. I think that
this is a very, very bad idea. I think each variable should be
declared--and declared as the correct type ("as range", "as workbook",
"as worksheet", "as long", "as variant").

But there's no law that says you have to do this. But if you post back
with a question about why your procedure doesn't work as expected and
it's becaused of a typo that would have been caught if you had used
"option Explicit", then expect some grief <vbg>.

Personally, I don't want to spend minutes/hours debugging these kinds of
errors and I like the intellisense and autocomplete that I get with
declared variables.

===
This was the same reason (lack of "option Explicit") that you didn't need the
"dim myDir as string" in the 2nd procedure, too.

But if you would have mistyped this line:

Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
as
Workbooks.Open FileName:=miDir & Application.PathSeparator & "a_DK.xls"

it might have taken a little time to notice that typo

=======
If you decide that you want to declare those variables (and I think you
should), then you can have excel help you.

Inside the VBE:
Tools|Options|Editor tab|check "require variable declaration"

Then each new module that you create will have "option explicit" added to the
top--you don't need to type it.

=====
ps. If you're looking through any of my posts, you'll see that I try to
include that "option explicit" on any procedure I post. It's my
passive-aggressive way of trying to make people do what I want <vvbg>.

pps. I would also modify that first macro.

Instead of this line:

Range("QuoteDate").Select

I'd use:
ActSheet.Select
actsheet.Range("QuoteDate").Select

I don't like to just rely on excel to determine the activesheet. It may be
overkill in 99.99999% of the cases, but sometimes....


dk_ said:
Dave,

Below are three WORKING versions of the data copy macro...

1. The first is the first that you posted, i.e., a
complete re-do of mine; --> ONE macro for ALL workbooks.

2. The second is the "Set ActSheet" modification of my original;
ONE macro for ALL workbooks.

Q). In this version (#2 below), why did I not need the line?...

Dim ActSheet As Worksheet

3. The third is my original, which
works only for ONE "named" workbook.

I did see that in my very first post,
there was a TYPO in the name of my source data file.

All three of these macros work cross platform,
(Mac OS 9, and Windows 98se Excel 97)...

1...
Sub QuoteCopy_ActiveSheet_to() ' <--[Dave's ONE macro for ALL]
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")
DKWkbk.Worksheets(1).Range("quotearea").Copy _
Destination:=ActSheet.Range("QuoteDate")
Application.CutCopyMode = False
DKWkbk.Close savechanges:=False
Range("QuoteDate").Select
Application.ScreenUpdating = True
End Sub

2...
Sub QuoteCopy_BenDaveSET() ' <--[Dave's "Set ActSheet", ONE for ALL]
Application.ScreenUpdating = False
Set ActSheet = ActiveSheet
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
ActSheet.Activate ' <--[This line replaces the line below]
' Windows("Ben.xls").Activate <--[This line is COMMENTED OUT]
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

3...
Sub QuoteCopy_Ben() ' <--[My Original Macro, ONE for EACH workbook]
Application.ScreenUpdating = False
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
Windows("Ben.xls").Activate
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

Dave Peterson said:
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.


dk_ wrote:

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

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.
Click to expand...
Click to expand...
Click to expand...
 
Notice that the top version uses a "Set" statement. And I need the parens
there.

The bottom version just calls workbooks.open and doesn't require them.



dk_ said:
Dave,

Thank you very much for your help, your patience,
and for the excellent lessons!

Very much appreciated!

I am curious why the following statement needed () around the file's path?...

Set DKWkbk = Workbooks.Open _
(FileName:=myDir & Application.PathSeparator & "a_DK.xls")

And in another version of the macro, the ()'s were not needed?...

myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"

Thank you, once again. ;)

-Dennis

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

Dave Peterson said:
For #2. Technically, you don't need to declare your variables.

If you don't put "Option Explicit" at the top of the module that contains
that procedure, you don't need to declare any variable. I think that
this is a very, very bad idea. I think each variable should be
declared--and declared as the correct type ("as range", "as workbook",
"as worksheet", "as long", "as variant").

But there's no law that says you have to do this. But if you post back
with a question about why your procedure doesn't work as expected and
it's becaused of a typo that would have been caught if you had used
"option Explicit", then expect some grief <vbg>.

Personally, I don't want to spend minutes/hours debugging these kinds of
errors and I like the intellisense and autocomplete that I get with
declared variables.

===
This was the same reason (lack of "option Explicit") that you didn't need the
"dim myDir as string" in the 2nd procedure, too.

But if you would have mistyped this line:

Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
as
Workbooks.Open FileName:=miDir & Application.PathSeparator & "a_DK.xls"

it might have taken a little time to notice that typo

=======
If you decide that you want to declare those variables (and I think you
should), then you can have excel help you.

Inside the VBE:
Tools|Options|Editor tab|check "require variable declaration"

Then each new module that you create will have "option explicit" added to the
top--you don't need to type it.

=====
ps. If you're looking through any of my posts, you'll see that I try to
include that "option explicit" on any procedure I post. It's my
passive-aggressive way of trying to make people do what I want <vvbg>.

pps. I would also modify that first macro.

Instead of this line:

Range("QuoteDate").Select

I'd use:
ActSheet.Select
actsheet.Range("QuoteDate").Select

I don't like to just rely on excel to determine the activesheet. It may be
overkill in 99.99999% of the cases, but sometimes....


dk_ said:
Dave,

Below are three WORKING versions of the data copy macro...

1. The first is the first that you posted, i.e., a
complete re-do of mine; --> ONE macro for ALL workbooks.

2. The second is the "Set ActSheet" modification of my original;
ONE macro for ALL workbooks.

Q). In this version (#2 below), why did I not need the line?...

Dim ActSheet As Worksheet

3. The third is my original, which
works only for ONE "named" workbook.

I did see that in my very first post,
there was a TYPO in the name of my source data file.

All three of these macros work cross platform,
(Mac OS 9, and Windows 98se Excel 97)...

1...
Sub QuoteCopy_ActiveSheet_to() ' <--[Dave's ONE macro for ALL]
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")
DKWkbk.Worksheets(1).Range("quotearea").Copy _
Destination:=ActSheet.Range("QuoteDate")
Application.CutCopyMode = False
DKWkbk.Close savechanges:=False
Range("QuoteDate").Select
Application.ScreenUpdating = True
End Sub

2...
Sub QuoteCopy_BenDaveSET() ' <--[Dave's "Set ActSheet", ONE for ALL]
Application.ScreenUpdating = False
Set ActSheet = ActiveSheet
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
ActSheet.Activate ' <--[This line replaces the line below]
' Windows("Ben.xls").Activate <--[This line is COMMENTED OUT]
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

3...
Sub QuoteCopy_Ben() ' <--[My Original Macro, ONE for EACH workbook]
Application.ScreenUpdating = False
myDir = ActiveWorkbook.Path
Workbooks.Open FileName:=myDir & Application.PathSeparator & "a_DK.xls"
Range("QuoteArea").Copy
Windows("Ben.xls").Activate
Range("QuoteDate").Select
ActiveSheet.Paste
Range("QuoteDate").Select
Windows("a_DK.xls").Activate
Application.CutCopyMode = False
Range("dkquotedate").Select
Application.ScreenUpdating = True
ActiveWindow.Close
End Sub

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.


dk_ wrote:

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

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top