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

bemcbride

New Member
Joined
May 21, 2012
Messages
47
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 :)
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.:
Code:
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:
Code:
Sheets("Invoice").Range("B1,E1,E2,B3,F20").Value = ""
Just adjust the cells to match your needs once again.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top