Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create New Workbook on Hyperlink
Is there a way using a hyperlink I can click on the hyperlink which will
create a new workbook, copy data from the current workbook to the new one and then go to the new workbook. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create New Workbook on Hyperlink
Put a hyperlink in the workbook to some place in the document. Say we put it
in B9. Insert the following worksheet event macro: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) If Target.Parent.Address < "$B$9" Then Exit Sub End If Application.ScreenUpdating = False Dim wb1 As Workbook Dim sh As Worksheet Set wb1 = ActiveWorkbook Workbooks.Add Set wb2 = ActiveWorkbook For Each sh In wb1.Sheets sh.Copy Befo=wb2.Sheets(1) Next Application.ScreenUpdating = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200904 "James" wrote: Is there a way using a hyperlink I can click on the hyperlink which will create a new workbook, copy data from the current workbook to the new one and then go to the new workbook. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create New Workbook on Hyperlink
This worked great, thanks!
"Gary''s Student" wrote: Put a hyperlink in the workbook to some place in the document. Say we put it in B9. Insert the following worksheet event macro: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) If Target.Parent.Address < "$B$9" Then Exit Sub End If Application.ScreenUpdating = False Dim wb1 As Workbook Dim sh As Worksheet Set wb1 = ActiveWorkbook Workbooks.Add Set wb2 = ActiveWorkbook For Each sh In wb1.Sheets sh.Copy Befo=wb2.Sheets(1) Next Application.ScreenUpdating = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200904 "James" wrote: Is there a way using a hyperlink I can click on the hyperlink which will create a new workbook, copy data from the current workbook to the new one and then go to the new workbook. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create New Workbook on Hyperlink
You should consider using a button in the future.
-- Gary''s Student - gsnu200904 "James" wrote: This worked great, thanks! "Gary''s Student" wrote: Put a hyperlink in the workbook to some place in the document. Say we put it in B9. Insert the following worksheet event macro: Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) If Target.Parent.Address < "$B$9" Then Exit Sub End If Application.ScreenUpdating = False Dim wb1 As Workbook Dim sh As Worksheet Set wb1 = ActiveWorkbook Workbooks.Add Set wb2 = ActiveWorkbook For Each sh In wb1.Sheets sh.Copy Befo=wb2.Sheets(1) Next Application.ScreenUpdating = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu200904 "James" wrote: Is there a way using a hyperlink I can click on the hyperlink which will create a new workbook, copy data from the current workbook to the new one and then go to the new workbook. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Hyperlink to cell in another Workbook saved in Doc Manageme | Excel Discussion (Misc queries) | |||
how can I create a save file hyperlink or macro in a workbook? | Excel Programming | |||
How do I create a hyperlink to a cell with the hyperlink function | Excel Worksheet Functions | |||
Create Hyperlink | Excel Programming | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) |