VBA help with increasing row before macro runs again.

[brent3162]

I have a workbook that I am trying to build for my son. I'm not a VBA person and have been working my way through it though. I have an Invoice worksheet that I want to copy selected content to a "All Invoices" worksheet, InvoiceNumber, Date, Name, Total cost. So that he can look at this one all invoices worksheet and see all of his sales. The invoices primary purpose is to print an invoice.

I have one macro that I created that copies these fields and puts them in a cell I selected. But when I run it again the next row is not being used and it copies over itself every time. Can someone who is a better programmer than me (shouldn't be too hard) help me with incrementing the row value when pasting new invoices to the list.

Here is my current macro:

Sub Save_Invoice()
'
' Saving_invoice number
Range("G7").Select
Selection.Copy
Sheets("ALL_INVOICES").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Saving Invoice Date
Range("G8").Select
Selection.Copy
Sheets("ALL_INVOICES").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Saving Customer Name
Sheets("INVOICE").Select
Range("C7").Select
Selection.Copy
Sheets("ALL_INVOICES").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Saving Invoice Total
Sheets("INVOICE").Select
Range("G43").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("ALL_INVOICES").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[jolivanes]

See if this is better for you. Run it from either sheet.

Sub Transfer()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim nextRw As Long, i As Long
Dim invArr
Set sh1 = Worksheets("INVOICE")
Set sh2 = Worksheets("ALL_INVOICES")
nextRw = sh2.Cells(sh2.Rows.Count, 1).End(xlUp).Row + 1
invArr = Array("G7", "G8", "C7", "G43")
    For i = LBound(invArr) To UBound(invArr)
        With sh2.Cells(nextRw, 1)
            .Offset(, i).Value = sh1.Range(invArr(i)).Value
        End With
    Next i
End Sub

 

[brent3162]

@jolivanes thanks so much for the quick response and perfect answer to my question. You parsed the question correctly, which I was afraid was not asking clearly enough. Thanks so much. Been a member for sometime watching and reading but this was my first question and I'm sure glad I asked my question here instead of just google after google search. Now I'm on to my next question thread...LOL.

 

[jolivanes]

Glad to have been of help and good luck.
Thanks for letting us know.