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

[dk_]

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]

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

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.