naming two open workbooks

[zSplash]

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

 

[Tom Ogilvy]

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

 

[zSplash]

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).Select - 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).Activate
ActiveSheet.Range("a14").Select

Why can't I refer to the object I declared at the very beginning (oldSheet)?

st.

 

[zSplash]

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