Macro to copy and paste in new line.

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58
Hi, I have recorded the below macro, to copy some cells and paste them in a different sheet,
unfortunately every time I run the macro, it keeps copying in the same spot and overwrites what was there before instead of searching for the next available row.

Can anyone help?
Thanks
Nic
Code:
Sub enterdata()
'
' enterdata Macro
' Macro recorded 9/22/2006 by Cisco Systems, Inc.
'

'
    Sheets("Form").Select
    Range("A7:D7").Select
    Selection.Copy
    Sheets("Database").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(Database!RC[-2],Information!C[-4]:C[-3],2,0)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]+RC[-4]"
    Range("F3").Select
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Code:
    Worksheets("Form").Range("A7:D7").Copy
    Sheets("Database").Activate
    LR = ActiveCell.SpecialCells(xlLastCell).Address
    Range(LR).PasteSpecial Paste:=xlAll, Operation:=xlNone
    Range("E2").FormulaR1C1 = _
        "=VLOOKUP(Database!RC[-2],Information!C[-4]:C[-3],2,0)"
    Range("F2").FormulaR1C1 = "=RC[-5]+RC[-4]"

probably will still need some tweeking
 

seriousdamage

Board Regular
Joined
Aug 14, 2005
Messages
58
Unfortunately it does not work 100%, now it copies the data from the last cell with data, instead of from the cell A1 of the next free row.

Any more ideas?

Thanks
Nic
 

Watch MrExcel Video

Forum statistics

Threads
1,113,849
Messages
5,544,647
Members
410,627
Latest member
georgealice
Top