Excel Macro to copy and paste data between sheets

[Kingstonlad]

Hi,


In desparate need of help. I need to write a macro to paste data from one excel sheet ( which is designed as a form) into another excel sheet.


My issue is that how do I get the data to go to the next line. My current macro is pasting over the existing data wherea as I need it to go to the next line and so on any time the macro is run


pls help I am at my wits end

 

[Luke M]

You just need to figure out where the last line of data is. Here's an example.

[pre]

Sub GetLastRow()
Dim LastRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox "Last row with data is " & LastRow
End Sub

[/pre]
You should be able to either use bits of this code or use it as an example to modify your current code. Note that you'll want to add +1 to "LastRow" so that you're pasting into next blank row. =)

 

[Kingstonlad]

The code is telling the last line of data but its posting it over the same line where as i would like the new data to be represented on the new row just below the old information on the previous line.


pls refer to my code below..


Sub transferdata()


Range("D4:D23").Select

Selection.Copy

Sheets("dataworksheet").Select

Range("A1").Select

ActiveCell.Offset(1, 0).Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=True

Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

MsgBox "Last row with data is " & LastRow

Sheets("Annual Pricing Appeal Doc").Select

Application.CutCopyMode = False


End Sub

 

[Luke M]

How's this?

[pre]

Sub transferdata()
Dim LastRow As Long
With Worksheets("dataworksheet")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Range("D4:D23").Copy
.Cells(LastRow + 1, "A").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
Sheets("Annual Pricing Appeal Doc").Select

End Sub

[/pre]
Note that we first find the last row in dataworksheet, and then when we paste we put it in the cell that is 1 row below (the +1 part) and in col A.

 

[Kingstonlad]

thanks so much. it finally works. I just had to amend slightly to add the transpose but its working !


you are a star !

 

[Luke M]

Oops, sorry I missed that bit. Glad I could help!