Macro Help in excel 03

Closed
ben1986 Posts 2 Registration date Thursday September 4, 2008 Status Member Last seen September 5, 2008 - Sep 5, 2008 at 05:30 AM
ben1986 Posts 2 Registration date Thursday September 4, 2008 Status Member Last seen September 5, 2008 - Sep 5, 2008 at 08:37 AM
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
Related:

2 responses

canthave Posts 34 Registration date Monday August 25, 2008 Status Member Last seen July 3, 2009 2
Sep 5, 2008 at 08:34 AM
hi ben,

have you checked in the help files if you found something concerning the macro ?
-1
ben1986 Posts 2 Registration date Thursday September 4, 2008 Status Member Last seen September 5, 2008
Sep 5, 2008 at 08:37 AM
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