Compare column data

[bobkap]

I'm thinking that this is probably simple but I'm stumped and would appreciate any help.

In a macro I am writing I have a long column of customer names that are already sorted and many of the names are repeated in various numbers of rows. (Example below) I need to separate and group each customer so that I can then copy the rows to their own worksheet, but I cannot think of the best way to write the code to do this.

ABC Co
ABC Co
ABC Co
XYZ Co
XYZ Co
QRS Co
QRS Co
QRS Co
QRS Co

So, I need to copy the row of data for each company to another worksheet, but FIRST I have to figure out how to go through each row of data and identify when the company name changes and THEN go back and capture the data for that one company.

 

[JoeMo]

One way would be to use an Advanced Filter on you sorted list and extract unique records only to another (non-contiguous) column. Then use autofilter on your customer list while looping through the unique records to use as criteria. Then copy specialcells(xlvisiblecells) from the filtered list and paste to a new sheet.

 

[My Aswer Is This]

So lets talk exact details:

You have a sheet name "Master"
And in this sheet you have a list of customer names in column "A"

And you want a script written to copy each row to the proper sheet.

So if "IBM" is in column "A" this row gets copied to a sheet named "IBM"
So if "Apple" is in column "A" this row gets copied to a sheet named "Apple"

Is that what you want?

And if so do you already have a sheet named 'IBM" and a Sheet named "Apple"?
Or do we need to create all the sheets and then copy the rows to the proper sheet?

 

[bobkap]

Yes. That is what I need to do. And no, I do not have the sheets named. I was planning to figure out how to do that with the first row of a "new" company name.

Thanks very much for asking!

 

[My Aswer Is This]

Ok Assuming your sheet name is "Master"
This script looks down column "A" Starting in row (2) of sheet named "Master"
And copies each row of data to the sheet name shown in column "A"

You will need to have already created these sheets in your current Workbook.

The script after it copies the row to the sheet it will put the value "Copy Done" in column (35) of that row in sheet named "Master". This is so the script knows this row has been copied over and will not copy this row a second time when you run the script a second time.

Now if you want a script to make you a new sheet when you add a new sheet to your workbook I could write you a different script which would do that.

Try this and see how it works.

Sub Test()
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Dim ans As String
Dim Lastrow As Long
Dim NN As String
NN = "Master" ' Change ""Master to proper name if needed
Lastrow = Sheets(NN).Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To Lastrow
        ans = Cells(i, 1).Value
    
        If Cells(i, 35).Value <> "Copy Done" Then
            Rows(i).Copy Sheets(ans).Rows(Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1)
            Sheets(NN).Cells(i, 35).Value = "Copy Done"
        End If
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "Sheet named  " & Sheets(NN).Cells(i, 1).Value & "  Does not exist"
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

This script will make you a new sheet.
1. In column "A" Of Sheet named Master enter your sheet name.
2. Then double click on the sheet name you just entered.
3. Your new sheet will be created an given the sheet name you double clicked on.

You will get a warning if you have already created a sheet with this name.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named "Master"
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 And Target.Value <> "" Then
Cancel = True
On Error GoTo M
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Target.Value
End If
Exit Sub
M:
MsgBox "Sheet named  " & Target.Value & "  already exit"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
End Sub

 

[bobkap]

Thanks very much!

 

[My Aswer Is This]

Thanks very much!

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.