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
|