CODEWITHSUNDEEP
vbaexcel
Matthew Marion
Matthew Marion

Reputation: 1

How can I create/copy a worksheet while naming it based on the value of a cell in a specific column but variable row?

Essentially I'm creating a tracking sheet which will have a cell on it that, when clicked, will create a new excel sheet in the same workbook. For testing purposes I'm currently just having it create a new sheet, but eventually I'll have a sheet that it'll copy. What I need help with is, how do I get VB to pull a cell value to use as the name of the new/copied sheet? Here's the scenario:

Each row will have a Client column (which is Column C) which I want to use for the names of the workbooks that will be created. I'm trying to have a cell (say column R in that row) that when clicked creates a new worksheet and pulls in the value of column C in that row as the worksheet's name.

So, say Row 5 has "Test Client" in C5. When R5 is clicked, I want it to create a sheet that is named "Test Client". I've seen solutions that use loops to go through the column and create a sheet for each, but that wouldn't really work for my scenario as I'd need them to be created on the fly and not always for each row.

I know how to create the sheets in vb but my issue is getting the name. Is there a way to get vba to pull the name from column C for the row in which it was activated? So if it was activated for Row 5, it pulls C5, if it was Row 10, it pulls C10 etc.

Any suggestions would be greatly appreciated, I'm currently using this to create the sheets:

Sub CreateSheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:= _
         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
End Sub

and this to call:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

If Target.Row > 5 And Target.Column = 18 And Target.Count = 1 Then Call CreateSheet

End Sub

Upvotes: 0

Views: 56

Answers (2)

user6432984
user6432984

Reputation:

Update: As Shai Rado pointed out I was missing an error handler.

You should test to see if the worksheet exists first. This pattern will make it easier to debug and add functionality to your code.

Worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ws As Worksheet
    Dim WorksheetName As String

    If Target.Row > 5 And Target.Column = 18 And Target.Count = 1 Then

        WorksheetName = Cells(Target.Row, "C").Value

        Set ws = getWorkSheet(WorksheetName)

        If Not ws Is Nothing Then Set ws = getNewWorkSheet(WorksheetName)           

    End If

End Sub

Standard Module

Function getWorkSheet(WorksheetName As String, Optional WorkbookName As String) As Worksheet
    If Len(WorkbookName) = 0 Then WorkbookName = ThisWorkbook.Name

    With Workbooks(WorkbookName)
        On Error Resume Next
        Set getWorkSheet = .Worksheets(WorksheetName)
        On Error GoTo 0
    End With

End Function

Function getNewWorkSheet(WorksheetName As String, Optional WorkbookName As String) As Worksheet
    Dim ws As Worksheet

    If Len(WorkbookName) = 0 Then WorkbookName = ThisWorkbook.Name

    With Workbooks(WorkbookName)
        Set ws = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
        On Error Resume Next
        ws.Name = WorksheetName

        If Err.Number = 0 Then
            Set getNewWorkSheet = ws
        Else
            ws.Delete
        End If
        On Error GoTo 0

    End With
End Function

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

The code below reads the value in Column C for the relevant row, and then passes it as a String to your Function:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row > 5 And Target.Column = 18 And Target.Count = 1 Then
    Dim ShtName         As String

    ShtName = Cells(Target.Row, "C").Value
    Call CreateSheet(ShtName)
End If

End Sub

This is your function, I've added a String that is passed representing the worksheet name:

Public Sub CreateSheet(ws_Name As String)

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:= _
         ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

ws.Name = ws_Name

End Sub

Upvotes: 2

Related Questions