Copy from one worksheet to another

[Horatio J. Bilge, Jr.]

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

 

[Horatio J. Bilge, Jr.]

Sorry for the mistake in the title - it should read, "One workBOOK to another."

 

[Dave Peterson]

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("Log").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.]

That works great. Thanks for the quick help. A question, though... why
declare OldFile as a variant, instead of a string?

 

[Dave Peterson]

If I declare it as a variant, I can do a boolean comparison:

If OldFile = False Then

If I declare it as a string, then I have to be careful.
If OldFile = "False" Then

I have to worry about the double quotes, the upper/lower case and I'm not sure
how False would be treated in a non-English language.

So instead of worrying, I'll just take the easy way out.

 

[Horatio J. Bilge, Jr.]

That makes sense. Thanks for the explanation.
~ Horatio

 

[Horatio J. Bilge, Jr.]

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("Log").Cells(1)
Else
.Worksheets("MyTimes").Range("MyLog").Copy _
Destination:=NewWkBk.Worksheets("Times").Range("Log").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]

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

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("Log").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]

I'd pass the workbook name, too:

If SheetExists("Times", OldWkBk.Name) Then

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("Log").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

 

[Horatio J. Bilge, Jr.]

Thanks, but it still doesn't find the sheet name. When I run it, it goes to
the Else statement, and I get a "Subscript out of range" error (I assume
because the sheet name in the Else statement don't exist).

 

[Horatio J. Bilge, Jr.]

Oh, I think I've got it. The SheetExists function set an "IsSheetThere"
variable to True if the sheet name was found. Instead, it should have been
setting the SheetExists function itself to True. I've changed it, and so far
it seems to be working as expected.

Thanks for all your help,
~ Horatio

Thanks, but it still doesn't find the sheet name. When I run it, it goes to
the Else statement, and I get a "Subscript out of range" error (I assume
because the sheet name in the Else statement don't exist).

 

[Dave Peterson]

Yep. I didn't notice that.

I'd still pass it the second parm.