|
Macro Hyperlink Issues
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? |
Macro Hyperlink Issues
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? |
Macro Hyperlink Issues
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? |
Macro Hyperlink Issues
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? |
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? |
Macro Hyperlink Issues
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? |
Macro Hyperlink Issues
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? |
Macro Hyperlink Issues
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? |
Macro Hyperlink Issues
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? |
| All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com