VBA to get last instance of a value, the contents of another cell, and make a new worksheet

[GMC111568]

I have an excel sheet similar to the following:

ColumnA ColumnB ColumnC
George 1 14
Edith 1 16
Archie 1 26
Betty 2 32
Fred 2 34
Wilma 3 38
Pebbles 4 40
Louise 4 42
Helen 4 44
Florence 4 46

I'd like to create a macro, that when run will create a new sheet, and it will be a list of the last instance of each value in column b, along with the value of column c from that same row.

So, the resulting worksheet would have the following data in it after the macro is run:

ColumnA ColumnB ColumnC
Archie 1 26
Fred 2 34
Wilma 3 38
Florence 4 46

Any suggestions on how to proceed?

Thanks in advance!

 

[dUBBINS]

Can you explain why the data would change in your example? In that, what tells the macro those are the items you want copied? And do you simply want it to take a snapshot of the current data, so that when you change it, there is still a record of what it was? If yes, would the previous data always be overwritten, or just create an infinite log?

 

[JLGWhiz]

this will give you the last instance of column B values, but in reverse order of how they are listed.

Sub t()
Dim i As Long, newSh As Worksheet, sh As Worksheet
Set sh = ActiveSheet
Set newSh = Sheets.Add(After:=ActiveSheet)
    With sh
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
            If .Cells(i, 2) <> .Cells(i + 1, 2) And .Cells(i, 2) = .Cells(i - 1, 2) Then
                .Cells(i, 1).Resize(1, 3).Copy newSh.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        Next
    End With
End Sub

 

[GMC111568]

Can you explain why the data would change in your example? In that, what tells the macro those are the items you want copied? And do you simply want it to take a snapshot of the current data, so that when you change it, there is still a record of what it was? If yes, would the previous data always be overwritten, or just create an infinite log?

The data in column B is a group number.

I need to get a spreadsheet that contains information from the last row of each group (the values from Column B - the group itself - as well as Column C).

Again, I only need it for the last row of each group.

In this instance, the data in the spreadsheet itself is generated by another program, and it will always be in this specific order (by group number).

Thanks.

 

[JLGWhiz]

The data in column B is a group number.

I need to get a spreadsheet that contains information from the last row of each group (the values from Column B - the group itself - as well as Column C).

Again, I only need it for the last row of each group.

In this instance, the data in the spreadsheet itself is generated by another program, and it will always be in this specific order (by group number).

Thanks.

Try the code in post #3. It does what you want. You can do a quick sort to put it in the original order if needed. I did not include the sort in the code because there was no information in the original post of how the data would be needed on the output.

 

[JLGWhiz]

This will copy it over in the same order.

Sub t()
Dim i As Long, newSh As Worksheet, sh As Worksheet
Set sh = ActiveSheet
Set newSh = Sheets.Add(After:=ActiveSheet)
    With sh
        For i = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
            If .Cells(i, 2) <> .Cells(i + 1, 2) And .Cells(i, 2) = .Cells(i - 1, 2) Then
                .Rows(i).Copy
                newSh.Cells(2, 1).EntireRow.Insert
            End If
        Next
    End With
End Sub

 

[GMC111568]

Perfect!

Thank you!

 

[JLGWhiz]

Perfect!

Thank you!

You're welcome,
Regards, JLG