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...

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...

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...

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

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

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.

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

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.

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

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

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

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.

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