cparks
Member
Ok folks, I have a bit of a problem.
What I want is when a cell is populated, I want to immediately create a Sheet. That sheet's name will be based on the text inside that cell.
ALSO, I want it to create a hyperlink to that sheet.
My Current Sheet is "Crew" and the starting cell for this is in D5.
Below is what I have already and I placed it in my "Crew" sheet. The problem I'm having is once a cell is populated, I have to Run the macro for it to work. And sometimes, I'll get an error message and a sheet would be populated, but it would be "Sheet1", "Sheet2",....not what is currently in that cell.
Any help will be greatly appreciated! Thanks much!
__________________________________________________________________
What I want is when a cell is populated, I want to immediately create a Sheet. That sheet's name will be based on the text inside that cell.
ALSO, I want it to create a hyperlink to that sheet.
My Current Sheet is "Crew" and the starting cell for this is in D5.
Below is what I have already and I placed it in my "Crew" sheet. The problem I'm having is once a cell is populated, I have to Run the macro for it to work. And sometimes, I'll get an error message and a sheet would be populated, but it would be "Sheet1", "Sheet2",....not what is currently in that cell.
Any help will be greatly appreciated! Thanks much!
Code:
Private Sub CreateSheetsFromAListTEST()
Dim MyCell As Range, MyRange As Range
Set MyRange = Range(Sheets("Crew").[d5], Sheets("Crew").Cells(Rows.Count, "D").End(xlUp))
For Each MyCell In MyRange
If Len(MyCell.Text) > 0 Then
Sheets.Add after:=Sheets(Sheets.Count) 'creates a new workbook
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new workbook
End If
Next MyCell
End Sub
Mod edit : thread moved to appropriate forum !