Solved Excel Macro to create worksheets from list with hyperlinks

March 15, 2013 at 07:47:31
Specs: Windows Vista
Hi
I am using this code below to create Excel sheets based off of a range of cells from a main sheet.

I would like to do everything below but in addition when it creates the sheet and copies the row info, I would like it to make each row or cell on the main sheet link to each indivual sheet. Also within each individual sheet (that contains the one record info from the main sheet) to contain the link back to the main sheet. In other words, would like the hyperlinks to navigate back and forth when I have 75 sheets to work with. Any thoughts? thanks! Brahio

Sub AddMonthlySheets()
Dim mMonth As Range
For Each mMonth In Sheets(1).Range("A1:A12")
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = mMonth
Sheets(1).Range(mMonth.Address).EntireRow.Copy Destination:=ActiveSheet.Range("A1")
Next
End Sub


See More: Excel Macro to create worksheets from list with hyperlinks

Report •


#1
March 15, 2013 at 11:05:17
✔ Best Answer
First, a posting tip: Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. Thanks!

As for your question, something seems a bit strange. You said:

"...would like the hyperlinks to navigate back and forth when I have 75 sheets to work with."

Yet your code only creates 12 sheets:

For Each mMonth In Sheets(1).Range("A1:A12")

Assuming that's just an example, I'll assume you can modify the code below to create as many sheets as you need.

You didn't provide the Name of the main sheet, so I am going to be creative and use the name Main in my code below. Again, I assume that you can modify the code to meet your needs.

This code will create a new sheet for each value in Main!A1:A12. It will also create links in Main!A1:A12 to the respective sheets. Finally, it will create a link in A1 of each new sheet back to Main!A1.

Sub AddMonthlySheets()
Dim srcMonth, dstMonth As Range
'Loop Through A1:A12
 For rw = 1 To 12
'Create Worksheets and Copy Row
  Set srcMonth = Sheets("Main").Range("A" & rw)
     ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
     ActiveSheet.Name = srcMonth
       srcMonth.EntireRow.Copy Destination:=ActiveSheet.Range("A1")
'Create Hyperlink to new sheet
         srcMonth.Hyperlinks.Add Anchor:=srcMonth, _
             Address:="", SubAddress:=srcMonth & "!A1", _
             TextToDisplay:=srcMonth.Value
'Create HyperLink back to Main sheet
  Set dstMonth = ActiveSheet.Range("A1")
         dstMonth.Hyperlinks.Add Anchor:=dstMonth, _
             Address:="", SubAddress:="Main!A1", _
             TextToDisplay:=dstMonth.Value
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 15, 2013 at 15:59:30
Thank you so much! This worked perfectly!!!

Report •

#3
Report •

Related Solutions


Ask Question