Copy from one worksheet to another
I'd use:
Option Explicit
Private Sub cmdImport_Click()
Dim WkBk As Workbook
Dim NewWkbk As Workbook
Dim OldWkbk As Workbook
Dim OldFile As Variant
Dim NewFile As String
OldFile = Application.GetOpenFilename
If OldFile = False Then
Exit Sub
End If
NewFile = ThisWorkbook.Path & "\" & "NewLog.xls"
Set OldWkbk = Workbooks.Open(OldFile)
Set NewWkbk = Workbooks.Open(NewFile)
OldWkBk.Worksheets("Times").Range("Log").Copy _
Destination:=NewWkBk.Worksheets("Times").Range("Lo g").Cells(1)
End Sub
I moved the open's before the .copy. Sometimes a workbook will have a
workbook_open/auto_open procedure in it. And most macros (that do anything)
will destroy that clipboard.
By using .cells(1), I don't have to worry about the size of the Log ranges being
different. I'll just let excel determine the size like it does when you copy a
range and just select the top left corner to paste.
Horatio J. Bilge, Jr. wrote:
I am trying to use vba to copy a range from one file to another. The old file
is a log of dates and times, and the new file is an updated version of the
log. The code is in a third workbook, and I have a command button to run the
code. I get an error on the paste line, "Object doesn't support this property
or method."
Private Sub cmdImport_Click()
Dim WkBk As Workbook
Dim OldFile As String
Dim NewFile As String
OldFile = Application.GetOpenFilename
If OldFile = "False" Then Exit Sub
NewFile = ThisWorkbook.Path & "\" & "NewLog.xls"
Set WkBk = Workbooks.Open(OldFile)
With WkBk.Worksheets("Times")
.Range("Log").Copy
End With
Set WkBk = Workbooks.Open(NewFile)
With WkBk.Worksheets("Times")
.Range("Log").Paste
End With
End Sub
--
Dave Peterson
|