Paste data to another sheet and do sum and Multiply.

[parscon]

I need a VBA code that can sum all data on column E (QTY) and write the total in the latest blank cell of that column. and also Multiply of column E * F = G that write in column G and the total sum of column G will be write on latest blank cell of that column. Also make a border in final on all data and align to center for all data on cells .

Just important note , the data of sheet1 will be change that mean the row will be different.

I attached an example that I need .

Thank you very much for your big help.

[patel]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("E" & LR + 1) = WorksheetFunction.Sum(Range("E1:E" & LR))
For r = 1 To LR
Range("G" & r).Value = Range("E" & r) * Range("F" & r)
Next
Range("G" & LR + 1) = WorksheetFunction.Sum(Range("G1:G" & LR))

End Sub

[parscon]

Thank you.

[stanleydgrom]

parscon,

Worksheets Sheet1 and Sales Invoice already exist (based on your attached workbook).

With your raw data in worksheet Sheet1, and, the results should be in worksheet Sales Invoice (with the correct worksheet formatting) (based on your attached workbook).

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).




Option Explicit
Sub CreateSalesInvoice()
' stanleydgrom, 09/06/2013
' http://www.vbaexpress.com/forum/showthread.php?47417-Paste-data-to-another-sheet-and-do-sum-and-Multiply
Dim a As Variant, lr As Long
Application.ScreenUpdating = False
a = Sheets("Sheet1").Cells(1).CurrentRegion.Value
With Sheets("Sales Invoice")
.Activate
lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
If lr > 1 Then .Range("A2:G" & lr).Clear
.Cells(2, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
lr = .Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A2:G" & lr)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
End With
With .Range("G2:G" & lr)
.FormulaR1C1 = "=RC[-2]*RC[-1]"
.NumberFormat = "#,##0.00"
End With
.Range("D" & lr + 1).Value = "Total QTY:"
.Range("E" & lr + 1).Formula = "=SUM(E2:E" & lr & ")"
.Range("F" & lr + 1).Value = "Sub Total"
With .Range("G" & lr + 1)
.Formula = "=SUM(G2:G" & lr & ")"
.NumberFormat = "#,##0.00"
End With
With .Range("D" & lr + 1 & ":G" & lr + 1)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
End With
.Columns.AutoFit
End With
End Sub



Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CreateSalesInvoice macro.

[parscon]

Thank you very much , it is work , just I have a question .

I want Total QTY: and Sub Total: with the data be bold . could you please help me on this also.
and the unit price have this format .00

[stanleydgrom]

parscon,

Thanks for the two Private Messages.


Thank you very much , it is work

You are very welcome. Glad I could help.



I want Total QTY: and Sub Total: with the data be bold . could you please help me on this also.
and the unit price have this format .00

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).




Option Explicit
Sub CreateSalesInvoiceV2()
' stanleydgrom, 09/07/2013
' http://www.vbaexpress.com/forum/showthread.php?47417-Paste-data-to-another-sheet-and-do-sum-and-Multiply
Dim a As Variant, lr As Long
Application.ScreenUpdating = False
a = Sheets("Sheet1").Cells(1).CurrentRegion.Value
With Sheets("Sales Invoice")
.Activate
lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
If lr > 1 Then .Range("A2:G" & lr).Clear
.Cells(2, 1).Resize(UBound(a, 1), UBound(a, 2)) = a
lr = .Cells(Rows.Count, 1).End(xlUp).Row
With .Range("A2:G" & lr)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
End With
.Range("F2:F" & lr).NumberFormat = ".00"
With .Range("G2:G" & lr)
.FormulaR1C1 = "=RC[-2]*RC[-1]"
.NumberFormat = "#,##0.00"
End With
.Range("D" & lr + 1).Value = "Total QTY:"
.Range("E" & lr + 1).Formula = "=SUM(E2:E" & lr & ")"
.Range("F" & lr + 1).Value = "Sub Total"
With .Range("G" & lr + 1)
.Formula = "=SUM(G2:G" & lr & ")"
.NumberFormat = "#,##0.00"
End With
With .Range("D" & lr + 1 & ":G" & lr + 1)
.Font.Bold = True
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
End With
.Columns.AutoFit
End With
End Sub



Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CreateSalesInvoiceV2 macro.

[parscon]

Dear Stanleydgrom ,
You know this Job very well , really you are talent in VBA.
Thank you very much for your big help.

[stanleydgrom]

parscon,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.