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