Macro Help in excel 03

ben1986, on Friday September 5, 2008 10:30:53 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

Configuration: Windows XP
Internet Explorer 6.0

Reply to ben1986 Report this message to moderators Go to last message

This message seems useful, vote!
Report this message to moderators

canthave, on Friday September 5, 2008 01:34:32 PM

hi ben,

have you checked in the help files if you found something concerning the macro ?

Reply to canthave

This message seems useful, vote!
Report this message to moderators

ben1986, on Friday September 5, 2008 01:37:41 PM

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!

Reply to ben1986

Ask your question Reply

Results for

Excel Macro Help Please (Solved) Hello Everyone, I am sking for some help with an Excel macro please. I have a 1,900 hundred static HTML pages that I have converted to text for the purpose of importing to a DB via an Excel CSV file. I have everything sitting in an excel file now with... en.kioskea.net/forum/affich-11146-excel-macro-help-please

Excel active cell macro Hello, I'm trying to write a macro in excel which would copy and paste a range from WITHIN THE SAME ROW in the same sheet as the active cell is selected. For example let's say i select a cell within range A6:P36 then i would like the macro... en.kioskea.net/forum/affich-25813-excel-active-cell-macro

Excel Macro for deleting rows IF cells not... Hello, I need help to create a Excel macro. Basicly what I have is a sheet full with information about different departments and what I want to do is delete every row EXCEPT the rows that contain some specified values (wich i would like to enter on... en.kioskea.net/forum/affich-12620-excel-macro-for-deleting-rows-if-cells-not

1 2 3 Next

Results for

Excel tips : How to insert date In an Excel cellExcel tips : How to insert date In an Excel cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press “CTRL”+ “;” in the chosen cell. To insert current... en.kioskea.net/faq/sujet-313-excel-tips-how-to-insert-date-in-an-excel-cell

1 2 3 Next

Results for

Excel: Macro HelpHello, I ned some help setting up a macro for Excel. I have a lost of IDs/SKUs on Sheet 1, and a partial list on Sheet2. What I would like to do is cross-check the two sheets and delete all the rows on Sheet1 that's already listed on sheet2. The... en.kioskea.net/forum/affich-5884-excel-macro-help

Instal XP (Solved)Bonjour, Bonjour, J'ai un portable HP Compaq 6720 S qui tourne sous Vista; j'ai voulu remplacer Vista par XP: Format du disque Sata puis install XP et au d�but de l'install il me dit qu'il n'y a pas de disque. J'ai... en.kioskea.net/forum/affich-6855262-instal-xp

Send Excel Email by Row MacroHello, Write a Macro to Send individual emails to 300+ using Outlook? I have the book, Excel 2007 w/VBA, and it shows a macro on page 687,8 for what I want; however, it has been a while since I wrote a macro via Lotus, and I have not used the new... en.kioskea.net/forum/affich-27785-send-excel-email-by-row-macro

1 2 3 Next

Results for

Spreadsheets - Cell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. An entire line can be chosen by clicking directly on the line header: It is also possible to choose... en.kioskea.net/tableur/tabcellselect-1.php3

Spreadsheets - The Excel Interface Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel document is called a file... en.kioskea.net/tableur/tabinterface.php3

1 2 3 Next