Need help with Calculations after copying over price and quantity

[noodle48]

This is a tough one I can't get my head around.

I have two worksheets, one has the data the second place needs to be copied. What I need to have happen is this:

Out of all the data on Sheet1 I need to copy over the heading in column A, price in B and quantity in C over to sheet2, but the hard part is I only need to data that has a quantity filled in. Once over on sheet2 I will then multiple price*quantity + 5% sale tax and place in column D.

Anyone got a good idea this?

Thanks in advance.

[Domski]

Do we assume you no longer require answer(s) to this thread?

http://www.excelforum.com/excel-prog...ng-in-vba.html

Dom

[stnkynts]

I think i may understand what you want but could you please attach an example workbook with before and after sheets so I get a clearer picture.

[noodle48]

Here is the spreed sheet I will be working out of.

[stnkynts]

Here you go:

Sub CopyOverQuantity()
Dim ws1 As Worksheet:   Set ws1 = Sheets("AllProducts")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Invoice")
Dim lastrow As Long, icell As Long

lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row

For icell = 4 To lastrow
    If Not IsEmpty(ws1.Range("C" & icell)) Then
        ws1.Range("A" & icell).Resize(1, 3).Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next icell

End Sub

If you need help on setting up a formula for column D on the invoice sheet let me know. Should be simple though.

[noodle48]

Can you help me with this as well?

[arthurbr]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[stnkynts]

This should do everything that you asked for. Let me know.

Sub CopyOverQuantity()
Dim ws1 As Worksheet:   Set ws1 = Sheets("AllProducts")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Invoice")
Dim lastrow As Long, icell As Long, lastrow2 As Long

lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row

For icell = 4 To lastrow
    If Not IsEmpty(ws1.Range("C" & icell)) Then
        ws1.Range("A" & icell).Resize(1, 3).Copy Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next icell

ws2.Range("D5").Formula = "=$B5*$C5*1.05"
lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row

With ws2.Range("D5:D" & lastrow2)
    .NumberFormat = "$#,##0.00_);($#,##0.00)"
    .FillDown
End With

End Sub

[noodle48]

Thank you very much for the help. Code works the way I need it.