Sum not correct

[ayazgreat]

Hi

I have delveloped some codes to copy data with certain criterias but i am not getting correct sum in mentioned below codes Kindly see my attached sheet with complete codes if you people think that it needs improvement then please mention and correct it where necessory.



LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & LastRow + 1, "O" & LastRow + 1).Merge
Range("J" & LastRow + 1) = Header2
Range("J" & LastRow + 1, "Q" & LastRow + 1).Interior.Color = 0
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.Bold = True
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.ColorIndex = 2
Range("J" & LastRow + 1, "O" & LastRow + 1).HorizontalAlignment = xlCenter
Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P" & LastRow))


thanks in advance

[Bob Phillips]

Maybe it should be



LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & LastRow + 1, "O" & LastRow + 1).Merge
Range("J" & LastRow + 1) = Header2
Range("J" & LastRow + 1, "Q" & LastRow + 1).Interior.Color = 0
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.Bold = True
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.ColorIndex = 2
Range("J" & LastRow + 1, "O" & LastRow + 1).HorizontalAlignment = xlCenter
Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P1:P" & LastRow))

[Simon Lloyd]

Here's Ayazgreats full code from http://www.mrexcel.com/forum/showthread.php?t=323607

Sub ayazgreat()
Dim Header
Dim Header2
Dim LastRow As Long
Dim Rng As Range
Header = Array("Region", "Item Issued", "Invoice Date", "Cheque #", "Amount", "Chq Date")
Header2 = "Total"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Sheets("Payment").Copy
ActiveSheet.Shapes("Button 1").Delete
ActiveSheet.Shapes("Button 2").Delete
Rows("1:2").Delete Shift:=xlUp
ActiveSheet.Range("C3:H3").Value = Header
ActiveSheet.Columns("E:H").AutoFit

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A3:H" & Range("A" & Rows.Count).End(xlUp).Row)

Range("A" & LastRow, "H" & LastRow).Delete
Rng.Sort Key1:=Range("C4"), Order1:=xlAscending
Rng.AutoFilter Field:=3, Criteria1:="S-II"
Rng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Payment").Range("J3")
Rng.AutoFilter

LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & LastRow + 1, "O" & LastRow + 1).Merge
Range("J" & LastRow + 1) = Header2
Range("J" & LastRow + 1, "Q" & LastRow + 1).Interior.Color = 0
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.Bold = True
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.ColorIndex = 2
Range("J" & LastRow + 1, "O" & LastRow + 1).HorizontalAlignment = xlCenter
Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P4:P" & LastRow))



Rng.AutoFilter Field:=3, Criteria1:="S-III"
Rng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Payment").Range("S3")
Rng.AutoFilter

LastRow = Range("S" & Rows.Count).End(xlUp).Row
Range("S" & LastRow + 1, "X" & LastRow + 1).Merge
Range("S" & LastRow + 1) = Header2
Range("S" & LastRow + 1, "Z" & LastRow + 1).Interior.Color = 0
Range("S" & LastRow + 1, "Z" & LastRow + 1).Font.Bold = True
Range("S" & LastRow + 1, "Z" & LastRow + 1).Font.ColorIndex = 2
Range("S" & LastRow + 1, "X" & LastRow + 1).HorizontalAlignment = xlCenter
Range("Y" & LastRow + 1).Value = WorksheetFunction.Sum(Range("Y4:Y" & LastRow))
Range("S3:Z2000").Copy
Cells(Rows.Count, "J").End(xlUp).Offset(3).PasteSpecial xlAll
Columns("S:Z").Delete
Columns("A:I").Delete

Range("A4").Select
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

[ayazgreat]

Simon please first see my mentioned blow codes and the these codes someshow are different at this point please check and compare codes your provided codes


Sub TEST()
Dim Header2
Dim LastRow As Long
Dim Rng As Range
With Application
.ScreenUpdating = False

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A3:H" & Range("A" & Rows.Count).End(xlUp).Row)


Rng.Sort Key1:=Range("C4"), Order1:=xlAscending

Rng.AutoFilter Field:=3, Criteria1:="S-II"
Rng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("DATA").Range("J3")
Rng.AutoFilter

LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & LastRow + 1, "O" & LastRow + 1).Merge
Range("J" & LastRow + 1) = Header2
Range("J" & LastRow + 1, "Q" & LastRow + 1).Interior.Color = 0
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.Bold = True
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.ColorIndex = 2
Range("J" & LastRow + 1, "O" & LastRow + 1).HorizontalAlignment = xlCenter
Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P4:P" & LastRow))



Rng.AutoFilter Field:=3, Criteria1:="S-III"
Rng.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("DATA").Range("J" & LastRow).Offset(4, 0)
Rng.AutoFilter


LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & LastRow + 1, "O" & LastRow + 1).Merge
Range("J" & LastRow + 1) = Header2
Range("J" & LastRow + 1, "Q" & LastRow + 1).Interior.Color = 0
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.Bold = True
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.ColorIndex = 2
Range("J" & LastRow + 1, "O" & LastRow + 1).HorizontalAlignment = xlCenter
Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P" & LastRow))
Range("A4").Select


.ScreenUpdating = True
.DisplayAlerts = True

End With
End Sub

[Simon Lloyd]

Yes they are but posters often change their code when posting, it always helps to give the full picture!. When posting code please highlight your code and then click the green VBA button to add the code tags.

[ayazgreat]

Thanks Simon for your advice and what about the correction I want in mentioned below codes with the help of my complete provided codes please?

LastRow = Range("J" & Rows.Count).End(xlUp).Row
Range("J" & LastRow + 1, "O" & LastRow + 1).Merge
Range("J" & LastRow + 1) = Header2
Range("J" & LastRow + 1, "Q" & LastRow + 1).Interior.Color = 0
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.Bold = True
Range("J" & LastRow + 1, "Q" & LastRow + 1).Font.ColorIndex = 2
Range("J" & LastRow + 1, "O" & LastRow + 1).HorizontalAlignment = xlCenter
Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P" & LastRow))

[Simon Lloyd]

You have had the answer form xld! your code only sums one cell:

Range("P" & LastRow + 1).Value = WorksheetFunction.Sum(Range("P" & LastRow))
is the same as typing in A2 on the worksheet =A1 it will only ever show the value of A1 similarly if you entered =Sum(A1) which effectively thats what your doing then it will only ever show the value of A1

[Simon Lloyd]

Actually to be honest you haven't developed these codes yourself nor have you bothered to read the code you have as the answer is conatined in the full code you posted and the full code of yours that i posted from MrExcel.

Thread Closed be being annoying!