Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
I feel like I'm missing the answer to a pretty simple question, but I have
not been able to find any other threads related to this. In the program that I am working on, I run a series of sub routines that copy data from the original workbook to a new workbook. After I run each sub, I need to be able to come back (or activate, I guess) to the original workbook before I call the next sub. I tried naming the original workbook Dim RunRateMain As Workbook and then re-activating it after I ran the first of the series of the subs, but that didn't seem to work Any help would be appreciated... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
I asume that the code is running in the original workbook. If so then you can
use... ThisWorkbook.Select As the workbook running the code is ThisWorkbook... -- HTH... Jim Thomlinson "ajvasel" wrote: I feel like I'm missing the answer to a pretty simple question, but I have not been able to find any other threads related to this. In the program that I am working on, I run a series of sub routines that copy data from the original workbook to a new workbook. After I run each sub, I need to be able to come back (or activate, I guess) to the original workbook before I call the next sub. I tried naming the original workbook Dim RunRateMain As Workbook and then re-activating it after I ran the first of the series of the subs, but that didn't seem to work Any help would be appreciated... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
Jim, thanks for the reply
That didn't really seem to work. I used ThisWorkbook.Activate as Select is not an option. It seems to want to refer to the workbook that the previous sub just created. Is there a way that I can name the original workbook and call that workbook before running the next sub? "Jim Thomlinson" wrote: I asume that the code is running in the original workbook. If so then you can use... ThisWorkbook.Select As the workbook running the code is ThisWorkbook... -- HTH... Jim Thomlinson "ajvasel" wrote: I feel like I'm missing the answer to a pretty simple question, but I have not been able to find any other threads related to this. In the program that I am working on, I run a series of sub routines that copy data from the original workbook to a new workbook. After I run each sub, I need to be able to come back (or activate, I guess) to the original workbook before I call the next sub. I tried naming the original workbook Dim RunRateMain As Workbook and then re-activating it after I ran the first of the series of the subs, but that didn't seem to work Any help would be appreciated... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
Dim mySelection as range
Dim myActCell as Range set myActcell = activecell set mySelection = selection 'do lots of stuff application.goto mySelection myActcell.activate ajvasel wrote: I feel like I'm missing the answer to a pretty simple question, but I have not been able to find any other threads related to this. In the program that I am working on, I run a series of sub routines that copy data from the original workbook to a new workbook. After I run each sub, I need to be able to come back (or activate, I guess) to the original workbook before I call the next sub. I tried naming the original workbook Dim RunRateMain As Workbook and then re-activating it after I ran the first of the series of the subs, but that didn't seem to work Any help would be appreciated... -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
Public Sub StandardsDates()
'Standards Dates ActiveSheet.Unprotect Workbooks.Open Filename:="\OriginalName&Path.xls" Range("StartPoint.EndPoint").Select 'Copy Original Range Data Selection.Copy ActiveWindow.ActivateNext 'Select New Workbook Range("StartPoint:EndPoint").Select 'Paste Original Range Data to new Workbook ActiveSheet.Paste ActiveWindow.ActivatePrevious 'Select & Close Original Workbook Application.CutCopyMode = False ActiveWindow.Close ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub I use this to copy €œTest Equipment Nomenclatures:CalibrationDates from an existing workbook into a new one. If I understand what you are asking a little modification will make it fill your needs Lou "Dave Peterson" wrote: Dim mySelection as range Dim myActCell as Range set myActcell = activecell set mySelection = selection 'do lots of stuff application.goto mySelection myActcell.activate ajvasel wrote: I feel like I'm missing the answer to a pretty simple question, but I have not been able to find any other threads related to this. In the program that I am working on, I run a series of sub routines that copy data from the original workbook to a new workbook. After I run each sub, I need to be able to come back (or activate, I guess) to the original workbook before I call the next sub. I tried naming the original workbook Dim RunRateMain As Workbook and then re-activating it after I ran the first of the series of the subs, but that didn't seem to work Any help would be appreciated... -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
Thanks Dave and Rookie for the suggestions. After trying to work with your
suggestion for some time, Dave, I finally figured out that Windows(" maint run rate.xls").Activate Range("A4").Select would work just as I needed. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
Here's hoping that you didn't change worksheets in " maint run rate.xls"--or
opened a second window. ajvasel wrote: Thanks Dave and Rookie for the suggestions. After trying to work with your suggestion for some time, Dave, I finally figured out that Windows(" maint run rate.xls").Activate Range("A4").Select would work just as I needed. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
There is only 1 worksheet in 'maint run rate.xls'. What do you mean by open a
2nd window? "Dave Peterson" wrote: Here's hoping that you didn't change worksheets in " maint run rate.xls"--or opened a second window. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
Window|New window will create a new window.
And then windows("maint run rate.xls").activate won't work. I still like that other suggestion better--is there a reason you didn't like it: 'declare some variables Dim mySelection as range Dim myActCell as Range 'do this at the top. 'myActCell will represent the activecell in the active worksheet set myActcell = activecell 'mySelection will represent the current selection in the active worksheet set mySelection = selection 'You could have selected A1:D20 and have C13 the activecell. 'do lots of stuff 'right before you finish 'select the range that was selected when you start application.goto mySelection 'and activate the cell within that range myActcell.activate ajvasel wrote: There is only 1 worksheet in 'maint run rate.xls'. What do you mean by open a 2nd window? "Dave Peterson" wrote: Here's hoping that you didn't change worksheets in " maint run rate.xls"--or opened a second window. -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
It wasn't that I didn't like it... I just had a hard time understanding how
to implement it. I don't understand why it has to be a specific range. I just wanted to be able to return to the workbook (and since it only has 1 worksheet, to the worksheet on it). Seeing as how you expanded on your reply, I will again attempt to use what you have suggested, though. By doing what you suggested though, how does it know what workbook to reference? "Dave Peterson" wrote: Window|New window will create a new window. And then windows("maint run rate.xls").activate won't work. I still like that other suggestion better--is there a reason you didn't like it: 'declare some variables Dim mySelection as range Dim myActCell as Range 'do this at the top. 'myActCell will represent the activecell in the active worksheet set myActcell = activecell 'mySelection will represent the current selection in the active worksheet set mySelection = selection 'You could have selected A1:D20 and have C13 the activecell. 'do lots of stuff 'right before you finish 'select the range that was selected when you start application.goto mySelection 'and activate the cell within that range myActcell.activate ajvasel wrote: There is only 1 worksheet in 'maint run rate.xls'. What do you mean by open a 2nd window? "Dave Peterson" wrote: Here's hoping that you didn't change worksheets in " maint run rate.xls"--or opened a second window. -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
I used a range variable to keep track of the current selection (and activecell).
Range objects have properties like .value, .font, and even .parent. The .parent of a range is the worksheet that owns that range. the .parent of a worksheet is the workbook that owns that worksheet. the .parent of the workbook is the application (excel itself). So dim myActCell as range set myActCell = Activecell 'do lots of stuff 'this activates the workbook myactcell.parent.parent.activate 'this selects the worksheet myactcell.parent.select 'this selects the cell myactcell.select ====== But excel has another way to combine all those things into one statement. application.goto myactcell ====== What's really nice about using this kind of technique is that you don't have to worry about the workbook name changing. It can be a pain if you hardcode too much into your procedures. ajvasel wrote: It wasn't that I didn't like it... I just had a hard time understanding how to implement it. I don't understand why it has to be a specific range. I just wanted to be able to return to the workbook (and since it only has 1 worksheet, to the worksheet on it). Seeing as how you expanded on your reply, I will again attempt to use what you have suggested, though. By doing what you suggested though, how does it know what workbook to reference? "Dave Peterson" wrote: Window|New window will create a new window. And then windows("maint run rate.xls").activate won't work. I still like that other suggestion better--is there a reason you didn't like it: 'declare some variables Dim mySelection as range Dim myActCell as Range 'do this at the top. 'myActCell will represent the activecell in the active worksheet set myActcell = activecell 'mySelection will represent the current selection in the active worksheet set mySelection = selection 'You could have selected A1:D20 and have C13 the activecell. 'do lots of stuff 'right before you finish 'select the range that was selected when you start application.goto mySelection 'and activate the cell within that range myActcell.activate ajvasel wrote: There is only 1 worksheet in 'maint run rate.xls'. What do you mean by open a 2nd window? "Dave Peterson" wrote: Here's hoping that you didn't change worksheets in " maint run rate.xls"--or opened a second window. -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
For some reason, your detailed explanation (which is greatly appreciated)
just isn't making sense to me. "Dave Peterson" wrote: dim myActCell as range set myActCell = Activecell What part of this am I modifying to meet the needs of my code? Am I inserting actual cell ranges in place of myActCell, another custom name, or something else? Again, I am still confused as to why I need to deal with specific cell ranges. All I want to do is to have the code look at my original workbook (only 1 worksheet), go to A4 and run the next sub. Here is the beginning of my code... Intro.Hide Selection.AutoFilter Selection.AutoFilter Cells.Select Selection.EntireColumn.Hidden = False Selection.Copy Worksheets.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets(Array("TO Runrate", "Telephone Line Descriptions")).Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A4").Select I'm guessing that somewhere around here is where I want to declare variables. Much further down is the end of the code... File1000 Windows(" maint run rate.xls").Activate Range("A4").Select File1002 Windows(" maint run rate.xls").Activate Range("A4").Select File1005 Windows(" maint run rate.xls").Activate Range("A4").Select File1008 Windows(" maint run rate.xls").Activate Range("A4").Select File1022 Windows(" maint run rate.xls").Activate Range("A4").Select File1023 Windows(" maint run rate.xls").Activate Range("A4").Select Anyone's assistance would be appreciated. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return to Original Workbook
You don't _have_ to worry about specific cells.
But if you're selecting other worksheets or activating other workbooks, it's not too unusual to select other cells on the original worksheet. If you change the selection, then keeping track of where you started will help to go back there. Dim mySheet as worksheet set mySheet = activesheet 'your code here mysheet.parent.activate 'in case you switched workbooks mysheet.select But there isn't too much difference with this and returning to the activecell. And if you don't change workbooks, you don't even need this line: mysheet.parent.activate ajvasel wrote: For some reason, your detailed explanation (which is greatly appreciated) just isn't making sense to me. "Dave Peterson" wrote: dim myActCell as range set myActCell = Activecell What part of this am I modifying to meet the needs of my code? Am I inserting actual cell ranges in place of myActCell, another custom name, or something else? Again, I am still confused as to why I need to deal with specific cell ranges. All I want to do is to have the code look at my original workbook (only 1 worksheet), go to A4 and run the next sub. Here is the beginning of my code... Intro.Hide Selection.AutoFilter Selection.AutoFilter Cells.Select Selection.EntireColumn.Hidden = False Selection.Copy Worksheets.Add Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets(Array("TO Runrate", "Telephone Line Descriptions")).Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A4").Select I'm guessing that somewhere around here is where I want to declare variables. Much further down is the end of the code... File1000 Windows(" maint run rate.xls").Activate Range("A4").Select File1002 Windows(" maint run rate.xls").Activate Range("A4").Select File1005 Windows(" maint run rate.xls").Activate Range("A4").Select File1008 Windows(" maint run rate.xls").Activate Range("A4").Select File1022 Windows(" maint run rate.xls").Activate Range("A4").Select File1023 Windows(" maint run rate.xls").Activate Range("A4").Select Anyone's assistance would be appreciated. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to return to original cell | Excel Discussion (Misc queries) | |||
Hyperlink to web does not return to original Excel worksheet | Excel Discussion (Misc queries) | |||
Return WB to original state | Excel Programming | |||
HOW DO I RETURN TO A RECOVERED FILE AFTER CHOOSING ORIGINAL | Excel Discussion (Misc queries) | |||
Return to Original File | Excel Programming |