Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

The VBA event below captures specific information from ecah worksheet 'xWs' in the…

  1. User

    The VBA event below captures specific information from ecah worksheet 'xWs' in the ActiveWorkbook. The information captured is then written to worksheet 'SrvSch' columns A:K Can the sheet name for the specific information found be written to column L

    Public Sub CopyServiceSchedules() Application.EnableEvents = False Dim xWs As Worksheet Dim xCWs As Worksheet Dim Xrg As Range Dim xStrName As String Dim xRStr As String Dim xRRg As Range Dim xC As Integer Dim wsName As String On Error Resume Next Worksheets("SrvSrch").Visible = True Worksheets("SrvSrch").Range("A1:K201").ClearContents Application.DisplayAlerts = False xStr = "SrvSrch" xRStr = "Service" Set xCWs = ActiveWorkbook.Worksheets.Item(xStr) If xCWs Is Nothing Then Set xCWs = ActiveWorkbook.Worksheets.Add xCWs.Name = xStr End If xC = 1 For Each xWs In ActiveWorkbook.Worksheets wsName = xWs.Name If Len(wsName) = 3 Then Set Xrg = xWs.Range("B:B") Set Xrg = Intersect(Xrg, xWs.UsedRange) For Each xRRg In Xrg If xRRg.Value = xRStr Then xWs.Range(xRRg.Offset(0, -1), xRRg.Offset(0, 9)).Copy xCWs.Cells(xC, 1).PasteSpecial xlPasteValuesAndNumberFormats xC = xC + 1 End If Next xRRg End If Next xWs Application.CutCopyMode = False Application.DisplayAlerts = True Worksheets("SrvSrch").Visible = False Application.CutCopyMode = True Application.EnableEvents = True End Sub

  2. Assistant

    Yes, the sheet name for the specific information found can be written to column L. Here's how you can modify the code to achieve this:

    1. Declare a new variable at the beginning of the code to hold the sheet name:

      Dim wsName As String
      
    2. Inside the loop that iterates through each worksheet, assign the current worksheet's name to the wsName variable:

      wsName = xWs.Name
      
    3. Update the line that pastes the captured information to include the sheet name in column L:

      xCWs.Cells(xC, 1).PasteSpecial xlPasteValuesAndNumberFormats
      xCWs.Cells(xC, 12).Value = wsName ' Write sheet name to column L
      

    That's it! With these modifications, the code will now write the sheet name of the specific information found to column L of the 'SrvSch' worksheet.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.