Vonsteiner
New Member
- Joined
- Apr 14, 2014
- Messages
- 45
- Office Version
- 365
- Platform
- Windows
Hello,
I am having difficulty getting the syntax right for this VBA code I am working on. Basically I have a table where the sales person will add new rows as they begin a new action. I am trying to have Excel create a new worksheet based off a template and name the new worksheet with the cell value. The table name is MasterActionList. The table column is column 4 or "Action Name". I have a worksheet within the workbook named "Template". I have tried the code with the range set to the table column and as it is with just using the column range.
Any help would be most appreciated. Thank you.
I am having difficulty getting the syntax right for this VBA code I am working on. Basically I have a table where the sales person will add new rows as they begin a new action. I am trying to have Excel create a new worksheet based off a template and name the new worksheet with the cell value. The table name is MasterActionList. The table column is column 4 or "Action Name". I have a worksheet within the workbook named "Template". I have tried the code with the range set to the table column and as it is with just using the column range.
VBA Code:
Private Sub CreateAndNameWorksheets()
Dim c As Range
Dim ac As Range
Application.ScreenUpdating = False
'ac = ActiveSheet.ListObjects("MasterActionList").ListColumns(4).Range.Select
Sheets("All Actions").Select
For Each c In Range("D2:D201")
c.Select
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = c.Value
Sheets("Template").Cells.Copy
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Sheets("All Actions").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=c.Value & "!A1", TextToDisplay:=c.Value
Next c
Application.ScreenUpdating = True
End Sub
Any help would be most appreciated. Thank you.