Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
Sorry for the mistake in the title - it should read, "One workBOOK to another."
"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
That works great. Thanks for the quick help. A question, though... why
declare OldFile as a variant, instead of a string? "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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. wrote: That works great. Thanks for the quick help. A question, though... why declare OldFile as a variant, instead of a string? "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
That makes sense. Thanks for the explanation.
~ Horatio "Dave Peterson" wrote: 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. wrote: That works great. Thanks for the quick help. A question, though... why declare OldFile as a variant, instead of a string? "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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
I'd pass the workbook name, too:
If SheetExists("Times", OldWkBk.Name) Then Horatio J. Bilge, Jr. wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Worksheet wont copy objects. | Excel Worksheet Functions | |||
copy graph from a Worksheet based on a field in another Worksheet | Excel Worksheet Functions | |||
Move/Copy or Copy/Insert worksheet? | Excel Discussion (Misc queries) | |||
copy part of a worksheet into a worksheet in the same file/keepi. | Excel Worksheet Functions | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions |