VBA/ Macro Code that Copies area and Pastes in new sheet on next available row

[bemcbride]

I'm making a program for a Photography company to help them keep track of theres invoices. On the Invoice sheet, after an invoice is completed, I want to select certain cells and have them posted in the Records sheet. I tried recording a basic macro but every time I run it, it replaces the information in the same row instead of the following empty row.

I really need to get this done, it's one of that last parts I have. Here's the code now:

Sub record()
'
' record Macro
'

'
Selection.Copy
Range("B1").Select
Sheets("Records").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Invoice").Select
Range("E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Invoice").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("D3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Invoice").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Invoice").Select
ActiveWindow.SmallScroll Down:=7
Range("F20").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Records").Select
Range("E3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


thanks

 

[Misca]

Welcome to Msg Board!

Try this and match it to better suite your needs. Tried to explain what each part of the code does so it should be really easy to modify.:

Sub ValuesToNewLine()

Dim i As Integer
Dim c As Range

'This one finds / sets the new line in the Records sheet:
With Sheets("Records")
    Set c = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    'The .cells(.rows.count,1) means the last cell in column A
    'The .End(xlUp) shoots up from there and finds the first cell that's not blank (=the last cell in column A that's not empty)
    'The .Offset(1) steps one row down from there.
End With

With Sheets("Invoice")
    For i = 1 To 5  'Loops 5 times. Change the 5 to match the number of your cells to copy
        c.Offset(, i - 1).Value = .Range(Choose(i, "B1", "E1", "E2", "B3", "F20")).Value
        'c is the first empty cell in column A found earlier in the code.
        'The .Offset(,i-1) tells how many cells left from the c.
        'The .value = .value part tells that instad of copying the values it just writes them: Easier to code and faster to run
        'The Choose(i,"B1"...) part means the value to be written to i-1 columns to the left from c is taken from the i:th cell in that list.
        'Fill in the list all the cells from the sheet Invoice that you want to copy to the other sheet.
    Next i
End With

End Sub

Once the code has copied the values from Invoice to the new row in Records should the macro empty the copied cells in Invoice? This can be done with something like:

Sheets("Invoice").Range("B1,E1,E2,B3,F20").Value = ""

Just adjust the cells to match your needs once again.

 

[bemcbride]

I worked beautifully thank you