Help with macro opening 2nd sheet

[Steve Lowe]

Hi,

Scenario is spreadsheet No. 1 has a macro that opens spreadsheet No. 2
and then saves and closes sheet No. 1 - that all works ok - but I then
need the macro go move the cursor to a particular cell and worksheet
in spreadsheet No. 2 - this is where I'm having problems although
sheet No. 2 is the only sheet open my sheet select and application
goto reference commands seem to be ignored

I'm guessing that although only 1 sheet is open I still need to make
this the active sheet

Thanks if you can help

Regards

splowe



Sub OpenOld()
'
' OpenOld Macro
' Macro recorded 08/04/2005 by Steve Lowe
'

'
ChDir "C:\My Documents\steves stuff\journal"
Workbooks.Open Filename:= _
"C:\My Documents\steves stuff\Journal\Old-Combined.xls"
Windows("CurrentMonth.xls").Activate

' OK Now lets close and save the current diary

' First we need to go back to the data worksheet
' in the current diary as we are currently in the macro worksheet

Sheets("Sheet1").Select

' Now lets save and close the workbook

ActiveWorkbook.Save
ActiveWorkbook.Close

' Now lets go to the 1st real cell in the old historical work sheet



Sheets("X Days Ago").Select
Application.Goto Reference:="AdjLookBack"
Range("A5").Select



End Sub
- Steve Lowe
- E-Mail : (e-mail address removed)
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

 

[Bob Phillips]

Steve,

Try this

Sub OpenOld()
Dim oWb As Workbook

ChDir "C:\My Documents\steves stuff\journal"
Set oWb = Workbooks.Open(Filename:= _
"C:\My Documents\steves stuff\Journal\Old-Combined.xls")
Windows("CurrentMonth.xls").Activate

' OK Now lets close and save the current diary

' First we need to go back to the data worksheet
' in the current diary as we are currently in the macro worksheet

Sheets("Sheet1").Select

' Now lets save and close the workbook

ActiveWorkbook.Save
ActiveWorkbook.Close

' Now lets go to the 1st real cell in the old historical work sheet

oWb.Activate
Sheets("X Days Ago").Select
Application.Goto Reference:="AdjLookBack"
Range("A5").Select

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steve Lowe]

Thanks Bob, however after opening the old-combined sheet the X Days
ago worksheet is still not being made the active sheet - thanks for
the attempt though.

Steve,

Try this

Sub OpenOld()
Dim oWb As Workbook

ChDir "C:\My Documents\steves stuff\journal"
Set oWb = Workbooks.Open(Filename:= _
"C:\My Documents\steves stuff\Journal\Old-Combined.xls")
Windows("CurrentMonth.xls").Activate

' OK Now lets close and save the current diary

' First we need to go back to the data worksheet
' in the current diary as we are currently in the macro worksheet

Sheets("Sheet1").Select

' Now lets save and close the workbook

ActiveWorkbook.Save
ActiveWorkbook.Close

' Now lets go to the 1st real cell in the old historical work sheet

oWb.Activate
Sheets("X Days Ago").Select
Application.Goto Reference:="AdjLookBack"
Range("A5").Select

End Sub

- Steve Lowe
- E-Mail : (e-mail address removed)
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

 

[Steve Lowe]

Hi Bob -

Sorted it by moving the code :-

Sheets("X Days Ago").Select
Application.Goto Reference:="AdjLookBack"
Range("A5").Select

To immediately after to called sheet is opened - thanks for your help

Regards

Steve.

Thanks Bob, however after opening the old-combined sheet the X Days
ago worksheet is still not being made the active sheet - thanks for
the attempt though.



- Steve Lowe
- E-Mail : (e-mail address removed)
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

- Steve Lowe
- E-Mail : (e-mail address removed)
- Before Replying Remove .NO.SPAM
- UK Resident although my e-mail address is usa.net

 

[Bob Phillips]

Ctually, looking at my code again,I just replicated the problem you had. I
should have used
Sub OpenOld()
Dim oWb As Workbook

ChDir "C:\My Documents\steves stuff\journal"
Set oWb = ActiveWorkbook
Workbooks.Open(Filename:= _
"C:\My Documents\steves stuff\Journal\Old-Combined.xls")
Windows("CurrentMonth.xls").Activate

' OK Now lets close and save the current diary

' First we need to go back to the data worksheet
' in the current diary as we are currently in the macro worksheet

Sheets("Sheet1").Select

' Now lets save and close the workbook

ActiveWorkbook.Save
ActiveWorkbook.Close

' Now lets go to the 1st real cell in the old historical work sheet

oWb.Activate
Sheets("X Days Ago").Select
Application.Goto Reference:="AdjLookBack"
Range("A5").Select

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)