|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 12th, 2008, 11:09 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Hyperlink Issues
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? 
|
|
#2
January 13th, 2008, 06:14 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 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?
|
|
#3
January 15th, 2008, 02:12 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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  31").SelectApplication.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 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?
|
|
#4
January 15th, 2008, 08:46 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 31").SelectApplication.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 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?
|
|
#5
January 15th, 2008, 10:33 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 31").SelectApplication.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 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?
|
|
#6
January 16th, 2008, 03:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 31").SelectApplication.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 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?
|
|
#7
January 16th, 2008, 05:16 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. 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" 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 31").SelectApplication.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 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?
|
|
#8
January 16th, 2008, 05:40 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. 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" 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 31").SelectApplication.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 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?
|
|
#9
January 19th, 2008, 01:02 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. 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" 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 31").SelectApplication.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 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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
