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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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" wrote: 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
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 "Horatio J. Bilge, Jr." wrote: 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" wrote: 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy from one worksheet to another
Yep. I didn't notice that.
I'd still pass it the second parm. Horatio J. Bilge, Jr. wrote: 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 "Horatio J. Bilge, Jr." wrote: 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" wrote: 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 -- 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 |