naming two open workbooks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zSplash - 21 Mar 2006 00:14 GMT

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 to this Message

Tom Ogilvy - 21 Mar 2006 01:45 GMT

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

Signature

Regards,
Tom Ogilvy

> From a blank Excel application (toolbar), I am opening one workbook
> ("oldSheet"), and then opening another workbook ("newSheet"), and copying
[quoted text clipped - 28 lines]
>
> TIA

Reply to this Message

zSplash - 21 Mar 2006 18:44 GMT

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.

> Sub captureData() 'from old
> Dim oldSheet As Worksheet, newSheet as Worksheet
[quoted text clipped - 45 lines]
> >
> > TIA

Reply to this Message

zSplash - 28 Mar 2006 20:20 GMT

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.

> Sub captureData() 'from old
> Dim oldSheet As Worksheet, newSheet as Worksheet
[quoted text clipped - 45 lines]
> >
> > TIA

Reply to this Message