I discovered a glitch. In some old versions of the log, the sheets and named
ranges are slightly different. I solved that with a function to test whether
a given sheet name exists. The problem now is that the range I am copying has
a conditional format, and the format being copied doesn't fit with the new
version of the log.

The conditional format in the old version is "Cell Value Is equal to =B$16"
but in the new version it should be "=B$18." Is there a way to copy the old
times without changing the conditional format?

Here is a code sample. In old versions of the log, the sheet is named
"MyTimes" instead of "Times":
With OldWkBk
If SheetExists("Times") Then
.Worksheets("Times").Range("Log").Copy _
Destination:=NewWkBk.Worksheets("Times").Range("Lo g").Cells(1)
Else
.Worksheets("MyTimes").Range("MyLog").Copy _
Destination:=NewWkBk.Worksheets("Times").Range("Lo g").Cells(1)
End If
End With

Here is the SheetExists function that I'm using (from VisibleVisual.com):
Function SheetExists(ByVal sheetname As String, Optional ByVal wrkbookname
As String) As Boolean
On Error Resume Next
SheetExists = False
Dim ws As Worksheet
Dim newSheetName As String
Dim IsSheetThere As Boolean

newSheetName = sheetname
For Each ws In Workbooks(wrkbookname).Worksheets
If ws.Name = newSheetName Or newSheetName = "" Then
IsSheetThere = True
Exit Function
End If
Next
End Function

"Dave Peterson" wrote:

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