I like the idea of just assigning the values. I've run into a problem with my
SheetExists function, though.

When I use SheetExists("Times") in a new version of the log, the function
does not catch the "Times" worksheet, even though it exists in the test
workbook. I have a suspicion that it has to do with passing the workbook name
to the function, but I haven't been able to figure out how to fix it.

Here is the SheetExists function:
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

Here is the macro that uses the SheetExists function:
Private Sub cmdImport_Click()
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)

With OldWkBk
If SheetExists("Times") Then
.Worksheets("Times").Range("Log").Copy _
Destination:=NewWkBk.Worksheets("Times").Range("Lo g").Cells(1)
Else
With .Worksheets("MyTimes").Range("MyLog")
NewWkBk.Worksheets("Times").Range("Log") _
.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End If
End With
End Sub




"Dave Peterson" wrote:

Maybe you could just paste special|values:

..Worksheets("MyTimes").Range("MyLog").Copy
NewWkBk.Worksheets("Times").Range("Log").Cells(1) _
.pastespecial paste:=xlpastevalues

(do it both spots.)

Or just assign the value...

with .worksheets("mytimes").range("mylog")
newwkbk.worksheets("times").range("Log") _
.resize(.rows.count,.columns.count).value = .value
end with

I didn't need the .cells() stuff, since I was resizing "Log" no matter what size
it started.

Horatio J. Bilge, Jr. wrote:

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


--

Dave Peterson