I have a list of cells on the 1st sheet. Example
203.1
204.2
206.4
The following macro creates sheets copied from a template sheet so that the workbook will end up with identical sheets named 203.1, 204.2, 206.4. How do i edit the following macro so that each sheet created has the sheet title value in cell D8. By that i mean sheet 203.1 also has 203.1 in cell D8 once the macro is completed? I do not want an equation to be in D8 that will output 203.1, just the value 203.1. Your help is greatly appreciated, thanks!
****
Option Explicit
Private Sub CommandButton1_Click()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim strWsName As String
Dim strSrcName As String
On Error GoTo ErrHnd
'setup column letter and first row number containing names
'column
strCol = "B"
'row (number is in double quotes)
strRow = "14"
'turn off screen updating to stop flicker & increase speed
Application.ScreenUpdating = False
'save this worksheet's name, so we can go back to it later
strSrcName = ActiveSheet.Name
'set start of data in selected column
Set rngStart = ActiveSheet.Range(strCol & strRow)
'find end of data in selected column
Set rngEnd = ActiveSheet.Range(strCol & CStr(Application.Rows.Count)) _
.End(xlUp)
'loop through cells in used range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
'ignore empty cells in range
If rngCell.Text <> "" Then
'get worksheet name
strWsName = rngCell.Text
'test if worksheet exists
On Error Resume Next
If Worksheets(strWsName) Is Nothing Then
'worksheet does not exist
'reinstate error handling
On Error GoTo ErrHnd
'copy worksheet named "Template"
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
'name new sheet
Worksheets(Worksheets.Count).Name = strWsName
Else
'worksheet already exists
'reinstate error handling
On Error GoTo ErrHnd
End If
End If
Next rngCell
'go back to the source worksheet
Worksheets(strSrcName).Activate
'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'go back to the source worksheet
Worksheets(strSrcName).Activate
'reinstate screen updating
Application.ScreenUpdating = True
End Sub
****
Bookmarks