Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with macro opening 2nd sheet
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 : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with macro opening 2nd sheet
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" wrote in message ... 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 : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with macro opening 2nd sheet
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. On Mon, 2 May 2005 11:57:11 +0100 "Bob Phillips" wrote: 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 : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with macro opening 2nd sheet
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. On Mon, 02 May 2005 13:09:09 +0100 Steve Lowe wrote: 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. On Mon, 2 May 2005 11:57:11 +0100 "Bob Phillips" wrote: 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 : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net - Steve Lowe - E-Mail : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with macro opening 2nd sheet
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) "Steve Lowe" wrote in message ... 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. On Mon, 02 May 2005 13:09:09 +0100 Steve Lowe wrote: 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. On Mon, 2 May 2005 11:57:11 +0100 "Bob Phillips" wrote: 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 : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net - Steve Lowe - E-Mail : - Before Replying Remove .NO.SPAM - UK Resident although my e-mail address is usa.net |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007-macro enabled, multi-sheet opening in minimized mode | Excel Discussion (Misc queries) | |||
Macro buttons opening saved macro | Excel Discussion (Misc queries) | |||
Excel blank sheet opening but the sheet has data | Excel Discussion (Misc queries) | |||
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet | Excel Programming | |||
Excel macro - Opening a sheet in accordance with the value in a particular cell | Excel Programming |