PC Review


Reply
Thread Tools Rate Thread

Automatically insert code into worksheet via macro

 
 
Tim879
Guest
Posts: n/a
 
      12th Jun 2008
I'd like to be able to insert the code below into the selected
worksheet when the user runs a macro. Is it possible to run a macro
that will insert this pre-written code into a worksheet?



'*******************
'paste this code into a tab. whenever it's activated it will create an
index of each
'sheet in the workbook dynamically and then also put a return link on
each tab
'*******************

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
M = M + 1
With wSheet
.Range("H1").Name = "Start" & wSheet.index
.Hyperlinks.Add Anchor:=.Range("H1"), Address:="",
SubAddress:="Index", TextToDisplay:="Back to Index"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="",
SubAddress:="Start" & wSheet.index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub






 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to Insert Worksheet with Worksheet Name the Current Date Steve Microsoft Excel Programming 1 27th Feb 2010 10:33 PM
Insert Code with Worksheet iamnu Microsoft Excel Discussion 3 8th May 2008 04:22 PM
Macro to Insert New Worksheet =?Utf-8?B?QW1iZXI=?= Microsoft Excel Programming 2 23rd Aug 2007 06:52 PM
VBA code to insert one worksheet into another mcnewsxp Microsoft Excel Misc 2 8th May 2007 12:21 AM
Automatically insert signature in worksheet =?Utf-8?B?Sm9obiBEYXZpZXM=?= Microsoft Excel Programming 4 9th Sep 2005 12:00 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:16 PM.