Macro - Find Last Duplicate And Copy

[karldugan]

Hi Excellers,

Need a little help - In column G I have duplicate text, what I want to do, is find the last occurrence of the word "Bal" and then copy everything below that row into a new sheet, called P&L.

The macro I have so far is, with the part I am stuck on highlighted in bold and italics;

Dim newsheet
Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
newsheet.Name = "P&L"
Sheets("Data").Range("I:J,L:M,O:Z").EntireColumn.Delete
Worksheets("Data").Range("1:1").Copy Worksheets("P&L").Range("1:1")
Worksheets("Data").Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("G:G"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("A:Z")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With ActiveWorkbook.ActiveSheet
Dim Last_B_RowNumber As Long
Last_B_RowNumber = .Range("G:G").Find(What:="BAL", After:=[G1], _
SearchDirection:=xlPrevious).Row
End With
Rows(ActiveCell.Row & ":" & Rows.Count).Cut Worksheets("P&L").Range("2:2")
Worksheets("Data").Select
ActiveSheet.Name = "Bal"

End Sub


Thanks,

Karl

 

[gallen]

what problem are you having? That code looks OK.

 

[footoo]

.

 

[karldugan]

Hi Gallen,

When I step through the macro, it is selecting the whole sheet and not the range.

Thanks,

Karl

 

[Fluff]

Try

Rows(Last_B_RowNumber & ":" & Rows.count).Cut Worksheets("P&L").Range("A2")

 

[karldugan]

Hi Fluff - That does 99.9% of it thanks :D - How do I get it to drop down one row though? So at the moment it copies the last row with Bal in, I want the first row after that.

Again, thanks for sorting this for me

Karl.

 

[Fluff]

Simply add one to the first row

Last_B_RowNumber +1 &...

 

[karldugan]

So simple! Thanks so much for the help, works perfectly.

Karl

 

[gallen]

I should have read further. The bold code was fine :D

Good to see it's fixed