Copy Paste In excel

[MBAA]

Hi All,

I am trying to create a macro in reation to the following:

There are Job titles in COLUMN A.

There are a list of dates in COLUMNS G, I & K

There is information in all the rows between A, G, I & K

There is a list in COLUMN C which has either "completed" OR "not completed"

IF COLUMN C has "Completed" :


I am trying to write a macro does the following:


*Copies the data in the ROWs OF COLUMN A & COLUMNS G, I & K


*Pastes the data in a new row (20 ROWs down from the orginal ROW)
ONLY the data in columns A, G, I & K.


*Looks at the next row and does the same thing if the COLUMN C Cell for that row has "completed" - There are a total of 10 ROWS.

******** type=text/javascript> sas_pageid='10991/82108'; // Page : commentcamarche/ope_spe sas_formatid=5145; // Format : 468x180 Big Box 468x180 sas_target=''; // Targeting SmartAdServer(sas_pageid,sas_formatid,sas_target); *********>******** id=SmartScr5145 src="http://www3.smartadserver.com/call/pubj/10991/82108/5145/S/6009365665/?">*********><NOSCRIPT> </NOSCRIPT>

******** type=text/javascript><!-- google_ad_client = "pub-3295671961621260"; google_language = "en"; var ccm_language = "en"; google_ad_type = "text"; google_ad_channel =""; var google_adnum = 0; google_ad_output = "js"; google_max_num_ads = "4"; google_feedback = 'on'; google_skip = google_adnum; //--> *********>******** src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type=text/javascript> *********>

 

[VoG]

Try

Sub Cpy()
Dim i As Long
For i = 2 To 11
    If LCase(Range("C" & i).Value) = "completed" Then
        Range("A" & i + 20).Value = Range("A" & i).Value
        Range("G" & i + 20).Value = Range("G" & i).Value
        Range("I" & i + 20).Value = Range("I" & i).Value
        Range("K" & i + 20).Value = Range("K" & i).Value
    End If
Next i
End Sub

 

[MBAA]

You are THE MAN!!!!

just to make it look tidy, is there any way of pasting the job titles and dates right next to eachother in the rows below?

I.e without the spaces of the other columns?