Macro Help in excel 03

Closed
Report
Posts
2
Registration date
Thursday September 4, 2008
Status
Member
Last seen
September 5, 2008
-
Posts
2
Registration date
Thursday September 4, 2008
Status
Member
Last seen
September 5, 2008
-
Hi guys,

I've created a log where users enter a code (column A), and this looks up various data from other sources (giving #N/A where no data is available). When all data is compiled, the user enters the date in the "data completed" cell (column R). This is then copied over to an archive sheet.

My macro currently copies the data from the "Outstanding Vehicles" sheet to "Completed Vehicles" using the copy/paste special-> values command. The problem I'm having is it copies the information row to row, so if A10 and A99 are full both are copied to the corresponding rows on the "Completed" sheet; I want them to just hit the first blank row (to prevent over-writing!)

Finally I need the macro to then delete data in the "Outstanding Vehicles" sheet that has been copied across. However only cells A and R need to be deleted so as not to remove any formulae.

I'm not too hot on this (only really been looking in the last 2 days) so any help is greatly appreciated!

code:

Sub movedata()
For i = 1 To 250

If Application.WorksheetFunction.IsNumber(Sheets("Outstanding Vehicles").Range("R1").Offset(i, 0)) = True Then

Sheets("Outstanding Vehicles").Rows(i + 1).Copy

Sheets("Completed Vehicles").Select

Range("a1").Offset(i, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i
End Sub

2 replies

Posts
34
Registration date
Monday August 25, 2008
Status
Member
Last seen
July 3, 2009
3
hi ben,

have you checked in the help files if you found something concerning the macro ?
-1
Posts
2
Registration date
Thursday September 4, 2008
Status
Member
Last seen
September 5, 2008

Hi,

I've had a look around but can't really find much, the excel helpfiles don't go into much depth as far as editing goes, and I'm struggling on Visual basic; it took a fair bit of searching to get as far as I did with writing it; I know what I need, but not how to do it I'm afraid!
-1