Consulting

Results 1 to 9 of 9

Thread: Search multiple values in column A and create sheets based on value name

  1. #1

    Search multiple values in column A and create sheets based on value name

    Hello. I'm looking for an automated solution to find multiple values instead of using the "Contains" function in Excel where I have to search one by one. I would like to be able to search column A for, Jane Doe, John Doe, William Doe, etc. and cut all rows that contain the name to a new sheet that is named based on the names mentioned above. I may have to search 20+ names. The data sheet would only contain what is left.

  2. #2
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Option Explicit
    
    
    Sub CreateSheetsFromAList()
        Dim myCell As Range, myRange As Range
    
    
        Set myRange = Sheets("Sheet1").Range("A2")  'Names List begins at B5
        Set myRange = Range(myRange, myRange.End(xlDown))
    Application.ScreenUpdating = False
        For Each myCell In myRange
            'If Not Evaluate("=ISREF('" & myCell.Value & "'!A1)") Then
                Sheets.Add after:=Sheets(Sheets.Count)
                Sheets(Sheets.Count).Name = myCell.Value
                Sheets(Sheets.Count).Range("A1").Value = "Names"
                myCell.EntireRow.Copy Sheets(Sheets.Count).Range("A2")
                Sheets(Sheets.Count).Columns("A:A").AutoFit
                myCell.EntireRow.Clear
            'End If
        Next
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. #3
    Thanks for your help. The sheets are being created but the data is not coming with the sheet. I entered my names where you have "Names". I entered like this, "Jane", "john", etc. However the data associated with that name is not transfering. I'm getting a run time error. Also the data im searching starts in A2 all the way down for what could be hundreds of rows.

  4. #4
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    I cleaned up the macro code by removing the lines that were "commented out" and changed the one comment from "Names List begins at B5" to say "Names List begins at A2".
    Making those edits has no affect on the code ... they were only comments and not used by VBA during the execution of the macro code. Here is the edited code :

    Option Explicit
    
    
    Sub CreateSheetsFromAList()
        Dim myCell As Range, myRange As Range
    
    
        Set myRange = Sheets("Sheet1").Range("A2")  'Names List begins at A2
        Set myRange = Range(myRange, myRange.End(xlDown))
    Application.ScreenUpdating = False
        For Each myCell In myRange
                Sheets.Add after:=Sheets(Sheets.Count)
                Sheets(Sheets.Count).Name = myCell.Value
                Sheets(Sheets.Count).Range("A1").Value = "Names"
                myCell.EntireRow.Copy Sheets(Sheets.Count).Range("A2")
                Sheets(Sheets.Count).Columns("A:A").AutoFit
                myCell.EntireRow.Clear
        Next
    Application.ScreenUpdating = True
    End Sub
    #1 - When you receive the 'run time error' , what line is highlighted as causing the error ?

    # 2 - Have you tried the download, without making any changes, to see how it runs on your machine ? If you receive an error
    with the download file, what error is it and what line is being highlighted ?

  5. #5
    Not working. My Sheet1 column A is called 'Attn List (Client) ' This field may contain other text values. I'm only looking for Jane Doe, John Doe, William Doe etc. I want to be able to define my names in a string and call them all at once. The error message tells me I typed and invalid name for a sheet or chart at the Sheets (Sheets.Count) .Name = myCell.Value

  6. #6
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    Change the line highlighted in red:

    For Each myCell In myRange
                Sheets.Add after:=Sheets(Sheets.Count)
                Sheets(Sheets.Count).Name = myCell.Value
                Sheets(Sheets.Count).Range("A1").Value = "Names" '<<------ Edit this line to the following --->>  Sheets(Sheets.Count.Range("A1").Value = "Attn List (Client)"

  7. #7
    Tried changing to add my names. . Not working.

  8. #8
    VBAX Expert Logit's Avatar
    Joined
    Sep 2016
    Posts
    606
    Location
    .
    To cut to the chase, attach your workbook for review. Do not include any confidential information.

  9. #9
    To avoid typing that name in the VBA code, you need to make a list somewhere (perhaps in another sheet) with names that need to be copied if there are other texts in the A column that should not be copied.

    PS Logit's code work well if you need to create sheets and copied data from that specific name. For all names found n column A.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •