Macro Hyperlink Issues
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?
|