Hi Shane,

Your Static suggestion worked.

Static myWB
myWB = Range("N3")
Windows(myWB).Activate

I am now able to pull up anyone of my €œUS-MAS€� workbooks containing the
Macro we were working on and move data back and forth from the €œMaster
Sequence€� workbook.

Your solution has made a tremendous difference in our office productivity
and accuracy, and I just wanted to thank you again for your patience and help.


"ShaneDevenshire" wrote:

Hi,

The Windows(thefile).Activate command can refer to a cell address where the
file name is located:

Windows(Range(N3)).Activate

the Windows command won't open a closed workbook but you can do the same
thing with the

Workbooks.Open [E3]

command but E3 would need to contain the entire path and file name.
--
Cheers,
Shane Devenshire


"J.R." wrote:

Thanks for your response. I will try it and let you know. Also, I was about
to post the following when I saw your last post so I figured I would go ahead
and post it anyways.

Lets try this from a different angle - I trimmed down a different Macro and
added description to explain the function of each Range.

Sub Button474_Click()
'
' Button474_Click Macro
' Macro recorded 1/16/2008 by J.R. Putman
'

'
Range("F1").Select
' "F1" contains the name of the working file
Selection.Copy
Range("S34:Y35").Select
' "S34:Y35" contains the Hyperlink to the "Master Sequence" workbook
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets("QT").Select
Range("N1").Select
' "N1" is a reference cell for the returning Hyperlink formula
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A7").Select
' "A7" contains the next sequential Quote number
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
' "N3" contains the formula HYPERLINK(link_location,friendly_name)
Windows("US-MAS-003-08 Z-man Corp. LSC Repair.xls").Activate
Range("R41:R42").Select
' "R41:R42" is the destination for the sequential Quote number
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
End Sub

You asked: €œHow do you change this formula in the workbook? Do you manually
modify it by retyping?€�
The following is a cell-by-cell description of what I use to change the
Hyperlink formula that resides in the €œMaster Sequence€� workbook:

Cell N1 = US-MAS-003-08 Z-man Corp. LSC Repair (this is the variable I copy
and paste, via the Macro, from Range(€œF1€�) €“ see above)

Cell N2 = N4&N1&€œ.xls€� (this formula creates the Address for the Hyperlink
formula in cell N3)

Cell N3 = HYPERLINK(N2,N1)

Cell N4 = Z:\ServiceDept\Excel-Master 2008\ (this is a path to the file
containing all master workbooks, and is a fixed value)

You asked: €œhow do you decide which of the open files to navigate to?€�

The decision to go from one workbook to another is predetermined by my
starting point. All Master workbooks from €œUS-MAS-001-08€� thru
€œUS-MAS-999-08€� (created as required) will access the €œMaster Sequence€�
workbook. However, the return from the €œMaster Sequence€� workbook is
determined by cell €œF1€� (contains the name of the workbook I am currently
using) of the €œUS-MAS-€� workbook I am currently working within.

Thank you for your patience.


"ShaneDevenshire" wrote:

Hi JR,

Well I'm not sure what the hyperlink has to do with this. You problem seems
to be how to get your code to return to the workbook you were in when you run
your first macro?

If that is the case store the file name in a variable before you actually
move to the other workbook:

Static myWB
myWB = activeworkbook.name

...
...

Windows(myWB).Activate

declaring the variable myWB as static may not be necessary, it just depends
on whether you are using more than one macro or not. You could also declare
it globally instead, at the top of the module you could use
Public myWB
--
Cheers,
Shane Devenshire


"J.R." wrote:

Hi Shane,

Thank you very much for your help, I will try to keep this brief.

If you understand a little about the process it might help, so let me paint
you a quick picture.

Three customers call and request an RMA #, so I generate the first
Excel-Master Workbook as follows: €œUS-MAS-001-08 XYZ Inc€�, then within the
Excel-Master Workbook I create an RMA Worksheet: €œUS-RMA-001-08 XYZ Inc€�. The
2nd =s €œUS-MAS-002-08 ABC Co.€�; with €œUS-RMA-002-08 ABC Co.€�; and the 3rd
=s €œUS-MAS-003-08 Z-man Corp.€�, with US-RMA-003-08 Z-man Corp.€�.

Now, RMA-003 is the first to arrive so we analyze the unit and determine the
repair costs so we need to issue a quote; this is the first quote so within
€œUS-MAS-003-08 Z-man Corp.€� I generate a quote sheet, €œUS-QT-001-08 Z-man
Corp.€�.

The next RMA to arrive is €œUS-RMA-001-08 XYZ Inc€�, so I follow the same
procedure and within €œUS-MAS-001-08 XYZ Inc€� I generate a quote sheet,
€œUS-QT-002-08 XYZ Inc€�. And so on€¦

I will issue all of the following types of documents to any given customer
at any given time:
1. US-MAS = Master (workbook)
2. US-RMA = Return Material Authorization (worksheet)
3. US-QT = Quote (worksheet)
4. US-ACK = Acknowledgement (worksheet)
5. US-PK = Packing slip (worksheet)
6. US-PO = Purchase Order (worksheet)
7. US-INV = Invoice (worksheet)
(All of the above are worksheets within ALL US-MAS workbooks)

I need to be able to pull up any US-MAS workbook and request the next number
in sequence for any one of the items above, at any given time, then return
directly back to the US-MAS I have just opened and place the sequential
number into the US-MAS I am currently working with.

The home office does not want gaps in our numbering sequence for all of the
above items, so in order to prevent gaps, and duplicate numbers I need all of
the US-MAS file to go to one €œMaster Sequence File€� to retrieve numbers, and
then return back to €œitself€� for processing.

Sorry for the lengthy explanation.


"ShaneDevenshire" wrote:

Hi,

The question then is what is being returned by the formula below and exactly
what are the values of the arguments for the situation where you have a link
to US-MAS-000-08 Excel-Master.xls?

HYPERLINK(link_location,friendly_name)

You say: "I can change this formula to any link I want while it resides
within a
Workbook, but I do not know how to move it to a Macro and get it to work."

How do you change this formula in the workbook? Do you manually modify it
by retyping?

If I understand you have a number of workbooks open and you want to navigate
to some or all of these, how do you decide which of the open files to
navigate to?

--
Thanks,
Shane Devenshire


"J.R." wrote:

Here is my code, I will explain below.

Sub Sequence_MAS_Click()
'
' Sequence_MAS_Click Macro
' Macro recorded 1/2/2008 by J.R. Putman
'

'
Range("C190:I191").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets("MAS").Select
Rows("500:500").Select
Selection.Cut
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Range("A7").Select
Selection.Copy
Windows("US-MAS-000-08 Excel-Master.xls").Activate
Range("R37:R38").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("D-Links").Select
Range("A31:D31").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Helitune Inc. Master Sequence Log 2008.xls").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A6").Select
Windows("US-MAS-000-08 Excel-Master.xls").Activate
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Setup").Select
Range("A100").Select
Range("A37:C38").Select
End Sub

As you can see from line 1 in my code I have a hyperlink in cells
("C190:I191"). And in line 2 you can see that my macro follows that
hyperlink. Now, after I have performed several functions with the newly
opened workbook, my macro follows a hyperlink I created within my €œMaster
Sequence€� workbook, and it gives me the following in line 10, and again in
line 24:

Windows("US-MAS-000-08 Excel-Master.xls").Activate

The hyperlink is derived from the formula:
HYPERLINK(link_location,friendly_name)

I can change this formula to any link I want while it resides within a
Workbook, but I do not know how to move it to a Macro and get it to work.


"ShaneDevenshire" wrote:

Hi J.R,

Why don't you post the code that you already have that relates to this
problem.

--
Thanks,
Shane Devenshire


"J.R." wrote:

I am having a problem using Hyperlinks within a €œPre Scripted€� Macro.

I have successfully placed a €œFixed€� Hyperlink in a Macro, but I need that
Hyperlink to be a variable; let me explain:

I have created an Excel spreadsheet as a master with the following name:
€œUS-MAS-000-08 Excel-Master€�

With each new customer order I need to open the €œExcel-Master€�, and through
a Macro I open a separate workbook named €œMaster Sequence File 2008€� and grab
the next number in sequence. After I have that number I rename the
€œExcel-Master€� so as to reference the customer. Here are some examples:

€œUS-MAS-001-08 Logistics Kit 10€�
€œUS-MAS-002-08 Elizabeth City Kit 2€�
€œUS-MAS-003-08 Elizabeth City Kit 66€�
€œUS-MAS-004-08 SE OPU-HP€�

As you can see, I have a numerical sequence from 001 thru 004 with the
customers name at the end. Now, after the new €œUS-MAS€� file has been created
I will need to retrieve additional sequence numbers from the €œMaster Sequence
File 2008€� and place them within the €œUS-MAS€� from which I am working.

Here is my problem. I need to open €œUS-MAS-001-08 Logistics Kit 10€�, click
on a Macro Button that takes me to the file €œMaster Sequence File 2008€�,
grads the next €œInvoice #€� (for example) in sequence, then returns me to
€œUS-MAS-001-08 Logistics Kit 10€� so that I can use the new #. Each time I try
to run the Macro it takes me back to the €œUS-MAS-000-08 Excel-Master€� file
because that is the file I used when I created the Macro. I do not know
enough about VBA code to resolve my issue.

Can someone help?