Macro Hyperlink Issues

[J.R.]

I am having a problem using Hyperlink’s 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?

 

[ShaneDevenshire]

Hi J.R,

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

 

[J.R.]

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("A3131").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]

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?

 

[J.R.]

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]

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

 

[J.R.]

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.

Let’s 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]

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.

 

[J.R.]

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.

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

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.

Let’s 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.