Skip to content
The Chat Archive
gpt-3.5-turbo-0613· OpenAI WildChat-4.8M (AI2)

I have some columns in a sheet 'List' that contain information related to Contractors in…

  1. User

    I have some columns in a sheet 'List' that contain information related to Contractors in column A. The details of the Contractor are entered in the row. I want to double click on the contractor in a Row of column A and for the following to happen; Duplicate sheet TEMPLATE Change the name of the duplicated sheet to the value found in column B of the same row And copy the value found in column A of the same Row to B1 of the new sheet Copy the value found in column B of the same Row to K1 of the new sheet Copy the value found in Column C of the same row to C1 of the new sheet Copy the value found in Column D of the same row to H1 of the new sheet Copy the value found in Cloumn E of the same row to I1 of the new sheet

  2. Assistant

    Here's a VBA code that you can use to achieve the desired functionality:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim contractorName As String
        Dim newSheet As Worksheet
        
        ' Check if the double-clicked cell is in column A
        If Target.Column = 1 Then
            contractorName = Target.Value
            
            ' Check if a contractor name is entered
            If contractorName <> "" Then
                ' Duplicate TEMPLATE sheet
                ThisWorkbook.Sheets("TEMPLATE").Copy After:=Sheets(Sheets.Count)
                
                ' Rename the duplicated sheet to the value found in column B
                Set newSheet = ActiveSheet
                newSheet.Name = Target.Offset(0, 1).Value
                
                ' Copy values to new sheet
                newSheet.Range("B1").Value = contractorName
                newSheet.Range("K1").Value = Target.Offset(0, 1).Value
                newSheet.Range("C1").Value = Target.Offset(0, 2).Value
                newSheet.Range("H1").Value = Target.Offset(0, 3).Value
                newSheet.Range("I1").Value = Target.Offset(0, 4).Value
                
                ' Cancel the default double-click action
                Cancel = True
            End If
        End If
    End Sub
    

    To implement this code, follow these steps:

    1. Press Alt+F11 to open the Visual Basic for Applications (VBA) editor.
    2. In the VBA editor, find the worksheet module for the 'List' sheet in the Project Explorer (usually in the left-hand side pane).
    3. If the project explorer is not visible, you can enable it by pressing Ctrl+R.
    4. Double-click on the worksheet module for the 'List' sheet to open the code window.
    5. Copy and paste the provided code into the code window.
    6. Save the workbook.

    Now, whenever you double-click a cell in column A of the 'List' sheet, it will duplicate the 'TEMPLATE' sheet, rename it, and copy the corresponding values from the 'List' sheet to the new duplicated sheet.

  3. User

    I want to manually enter values into column B How can I make sure that the value is; always in capital letters does not contain numbers or symbols does not exceed more than 3 letters and cannot be duplicated in the column

  4. Assistant

    To validate the values manually entered into column B and enforce the rules you specified, you can use Excel's Data Validation feature. Follow these steps:

    1. Select the range of cells in column B where you want to apply the validation (e.g., B2:B100).
    2. Go to the "Data" tab in the Excel ribbon.
    3. Click on "Data Validation" in the "Data Tools" group.
    4. In the "Data Validation" dialog box, select the "Settings" tab.
    5. In the "Allow" dropdown menu, choose "Custom".
    6. In the "Formula" input box, enter the following formula:

    Formula: =AND(LEN(B2)<=3, B2=UPPER(B2), NOT(ISNUMBER(SUMPRODUCT(--ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),"0123456789@#$%&"))))), COUNTIF($B$2:B2, B2)<=1)

    This formula uses the following functions and logic:

    • LEN(B2)<=3: Checks if the length of the value in B2 is less than or equal to 3.
    • B2=UPPER(B2): Checks if the value in B2 is in all capital letters.
    • NOT(ISNUMBER(SUMPRODUCT(--ISNUMBER(SEARCH(MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1),"0123456789@#$%&"))))): Checks if the value in B2 does not contain any numbers or symbols.
    • COUNTIF($B$2:B2, B2)<=1: Checks if the value in B2 is not duplicated in the column B.
    1. In the "Error Alert" tab, you can customize the error message that appears when an invalid value is entered.
    2. Click "OK" to apply the data validation.

    Now, when you enter a value manually into column B, the validation will check if it satisfies the rules you specified. If the value is invalid, an error message will be displayed, and the value will not be accepted.