Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming two open workbooks
From a blank Excel application (toolbar), I am opening one workbook
("oldSheet"), and then opening another workbook ("newSheet"), and copying values from the old to the new. I need help identfying each workbook. I want to declare range objects (i.e. oldSheet and newSheet), but don't know how. Please help? So far, I have: Sub captureData() 'from old Dim B4 As String, D4 As String, K4 As String, O4 As String Dim C6 As String, H6 As String, O6 As String Dim oldSheet 'As Sheet1, newSheet Set oldSheet = ActiveWorkbook.Sheets(1) B4 = oldSheet.Range("B4").Value D4 = oldSheet.Range("d4").Value K4 = oldSheet.Range("k4").Value O4 = oldSheet.Range("o4").Value C6 = oldSheet.Range("c6").Value H6 = oldSheet.Range("h6").Value O6 = oldSheet.Range("o6").Value 'to new Workbooks.Open Filename:="newOne.xls" ActiveWorkbook.ActiveSheet.Range("b2").Value = B4 ActiveWorkbook.ActiveSheet.Range("e2").Value = D4 ActiveWorkbook.ActiveSheet.Range("b4").Value = K4 ActiveWorkbook.ActiveSheet.Range("b5").Value = O4 ActiveWorkbook.ActiveSheet.Range("c8").Value = C6 ActiveWorkbook.ActiveSheet.Range("f8").Value = H6 ActiveWorkbook.ActiveSheet.Range("o8").Value = O6 End Sub TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming two open workbooks
Sub captureData() 'from old
Dim oldSheet As Worksheet, newSheet as Worksheet Set oldSheet = ActiveWorkbook.Sheets(1) Workbooks.Open Filename:="newOne.xls" set newSheet = Activeworkbook.Worksheets(1) With newSheet .Range("b2").Value = oldSheet.Range("B4").Value .Range("e2").Value = oldSheet.Range("d4").Value .Range("b4").Value = oldSheet.Range("k4").Value .Range("b5").Value = oldSheet.Range("o4").Value .Range("c8").Value = oldSheet.Range("c6").Value .Range("f8").Value = oldSheet.Range("h6").Value .Range("o8").Value = oldSheet.Range("o6").Value End With End Sub -- Regards, Tom Ogilvy "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... From a blank Excel application (toolbar), I am opening one workbook ("oldSheet"), and then opening another workbook ("newSheet"), and copying values from the old to the new. I need help identfying each workbook. I want to declare range objects (i.e. oldSheet and newSheet), but don't know how. Please help? So far, I have: Sub captureData() 'from old Dim B4 As String, D4 As String, K4 As String, O4 As String Dim C6 As String, H6 As String, O6 As String Dim oldSheet 'As Sheet1, newSheet Set oldSheet = ActiveWorkbook.Sheets(1) B4 = oldSheet.Range("B4").Value D4 = oldSheet.Range("d4").Value K4 = oldSheet.Range("k4").Value O4 = oldSheet.Range("o4").Value C6 = oldSheet.Range("c6").Value H6 = oldSheet.Range("h6").Value O6 = oldSheet.Range("o6").Value 'to new Workbooks.Open Filename:="newOne.xls" ActiveWorkbook.ActiveSheet.Range("b2").Value = B4 ActiveWorkbook.ActiveSheet.Range("e2").Value = D4 ActiveWorkbook.ActiveSheet.Range("b4").Value = K4 ActiveWorkbook.ActiveSheet.Range("b5").Value = O4 ActiveWorkbook.ActiveSheet.Range("c8").Value = C6 ActiveWorkbook.ActiveSheet.Range("f8").Value = H6 ActiveWorkbook.ActiveSheet.Range("o8").Value = O6 End Sub TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming two open workbooks
Thanks, Tom.
Looks great, but when I then try to reference oldSheet, I get a '438' error - Select method of range class failed. I tried all these lines of code, getting errors with each: Application.Workbooks("oldBook.xls").Sheets(1).Sel ect - error 1004, select method of worksheet class failed oldSheet.Select - run-time error 91 - object variable ... not set oldSheet.Activate - run-time error 91 - object variable not set Finally, I "succeed" (?) with: Application.Workbooks("oldBook.xls).Sheets(1).Acti vate ActiveSheet.Range("a14").Select Why can't I refer to the object I declared at the very beginning (oldSheet)? st. "Tom Ogilvy" wrote in message ... Sub captureData() 'from old Dim oldSheet As Worksheet, newSheet as Worksheet Set oldSheet = ActiveWorkbook.Sheets(1) Workbooks.Open Filename:="newOne.xls" set newSheet = Activeworkbook.Worksheets(1) With newSheet .Range("b2").Value = oldSheet.Range("B4").Value .Range("e2").Value = oldSheet.Range("d4").Value .Range("b4").Value = oldSheet.Range("k4").Value .Range("b5").Value = oldSheet.Range("o4").Value .Range("c8").Value = oldSheet.Range("c6").Value .Range("f8").Value = oldSheet.Range("h6").Value .Range("o8").Value = oldSheet.Range("o6").Value End With End Sub -- Regards, Tom Ogilvy "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... From a blank Excel application (toolbar), I am opening one workbook ("oldSheet"), and then opening another workbook ("newSheet"), and copying values from the old to the new. I need help identfying each workbook. I want to declare range objects (i.e. oldSheet and newSheet), but don't know how. Please help? So far, I have: Sub captureData() 'from old Dim B4 As String, D4 As String, K4 As String, O4 As String Dim C6 As String, H6 As String, O6 As String Dim oldSheet 'As Sheet1, newSheet Set oldSheet = ActiveWorkbook.Sheets(1) B4 = oldSheet.Range("B4").Value D4 = oldSheet.Range("d4").Value K4 = oldSheet.Range("k4").Value O4 = oldSheet.Range("o4").Value C6 = oldSheet.Range("c6").Value H6 = oldSheet.Range("h6").Value O6 = oldSheet.Range("o6").Value 'to new Workbooks.Open Filename:="newOne.xls" ActiveWorkbook.ActiveSheet.Range("b2").Value = B4 ActiveWorkbook.ActiveSheet.Range("e2").Value = D4 ActiveWorkbook.ActiveSheet.Range("b4").Value = K4 ActiveWorkbook.ActiveSheet.Range("b5").Value = O4 ActiveWorkbook.ActiveSheet.Range("c8").Value = C6 ActiveWorkbook.ActiveSheet.Range("f8").Value = H6 ActiveWorkbook.ActiveSheet.Range("o8").Value = O6 End Sub TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
naming two open workbooks
Thanks, Tom, for your good help. FYI, I'm trying to stop using 'Selection'
objects when 'Range' objects will work. Selection seems so straight-forward to me; Range, less so. Anyway, I've gotten pretty far with your help, but have run into a problem "calling" oldSheet after I've opened "newSheet". I'm having to use the following code, when all I want to do is switch between the two sheets, until I finally close "oldSheet" and work on "newSheet". Sub captureData() 'from old Dim oldSheet As Worksheet, newSheet as Worksheet Set oldSheet = ActiveWorkbook.Sheets(1) Workbooks.Open Filename:="newOne.xls" set newSheet = Activeworkbook.Worksheets(1) With newSheet .Range("b2").Value = oldSheet.Range("B4").Value ... End With oldSheet.Select 'I get an error here - subscript out of range... -- oldSheet.Range("A12:P18").select Selection.Copy newSheet.Select newSheet.Range("A12").Select Selection.PasteSpecial ... End Sub Why do I have to use "Windows(oldSheetWinName).Activate" or Windows(newSheetWinName).Activate" (and declare 2 more variables) when I move between the two sheets? Shouldn't "oldSheet.range("a12").select" work? Sub captureData() 'from old Dim oldSheet As Worksheet, newSheet as Worksheet Dim oldSheetWinName, newSheetWinName Set oldSheet = ActiveWorkbook.Sheets(1) Workbooks.Open Filename:="newOne.xls" set newSheet = Activeworkbook.Worksheets(1) With newSheet .Range("b2").Value = oldSheet.Range("B4").Value End With Windows(oldSheetWinName).Activate 'oldSheet.Select 'I get an error here - subscript out of range... ActiveSheet.Range("A14:P18").Select Selection.Copy Windows(newSheetWinName).Activate 'newSheet.Select ActiveSheet.Range("A12").Select 'newSheet.Range("A12").Select Selection.PasteSpecial ... End Sub If someone can explain why I need the extra variables and why just calling oldSheet or newSheet does not work, I'd certainly appreciate it. TIA. st. "Tom Ogilvy" wrote in message ... Sub captureData() 'from old Dim oldSheet As Worksheet, newSheet as Worksheet Set oldSheet = ActiveWorkbook.Sheets(1) Workbooks.Open Filename:="newOne.xls" set newSheet = Activeworkbook.Worksheets(1) With newSheet .Range("b2").Value = oldSheet.Range("B4").Value .Range("e2").Value = oldSheet.Range("d4").Value .Range("b4").Value = oldSheet.Range("k4").Value .Range("b5").Value = oldSheet.Range("o4").Value .Range("c8").Value = oldSheet.Range("c6").Value .Range("f8").Value = oldSheet.Range("h6").Value .Range("o8").Value = oldSheet.Range("o6").Value End With End Sub -- Regards, Tom Ogilvy "zSplash" <zNOSPAMSplash@ gci.net wrote in message ... From a blank Excel application (toolbar), I am opening one workbook ("oldSheet"), and then opening another workbook ("newSheet"), and copying values from the old to the new. I need help identfying each workbook. I want to declare range objects (i.e. oldSheet and newSheet), but don't know how. Please help? So far, I have: Sub captureData() 'from old Dim B4 As String, D4 As String, K4 As String, O4 As String Dim C6 As String, H6 As String, O6 As String Dim oldSheet 'As Sheet1, newSheet Set oldSheet = ActiveWorkbook.Sheets(1) B4 = oldSheet.Range("B4").Value D4 = oldSheet.Range("d4").Value K4 = oldSheet.Range("k4").Value O4 = oldSheet.Range("o4").Value C6 = oldSheet.Range("c6").Value H6 = oldSheet.Range("h6").Value O6 = oldSheet.Range("o6").Value 'to new Workbooks.Open Filename:="newOne.xls" ActiveWorkbook.ActiveSheet.Range("b2").Value = B4 ActiveWorkbook.ActiveSheet.Range("e2").Value = D4 ActiveWorkbook.ActiveSheet.Range("b4").Value = K4 ActiveWorkbook.ActiveSheet.Range("b5").Value = O4 ActiveWorkbook.ActiveSheet.Range("c8").Value = C6 ActiveWorkbook.ActiveSheet.Range("f8").Value = H6 ActiveWorkbook.ActiveSheet.Range("o8").Value = O6 End Sub TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date calculation across workbooks / Workbook naming question. | Excel Discussion (Misc queries) | |||
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Naming Workbooks and Sheets | Excel Programming | |||
Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming |