Select/cut/insert

Select/cut/insert

• droujav

  What i usually do is search through the sheet via a FOR ... NEXT x or a WHILE ... WEND untill i'm at the end of my sheet. During that search, i verify if the Cell(x,6) (where 6 would stand for Column "F") contains the required value. If that's the case, i cut/copy the entire Row(x) and paste it in the designated Workbook or Sheet.

  The question is, you mention that you need to paste it on row "4". What if you find more then one Cell in column "F" that is not Blank Will you need more then one extra Sheet where you need to past the designated Row into Row 4 Or do you need to follow up on that same sheet and continue to add to the existing list (in short continuing to row 5, 6, etc)

  
  

• Luis Esteban Valencia Muñoz

  Thanks alot for your time, Shlizar, I got code from someone here.

  Here's the code. It works great. Thanks again!

  Public Sub CutToDone()
  With Worksheets("Jobs")
  iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
  For i = iLastRow To 1 Step -1
  If .Cells(i, "F").Value <> "" Then
  Worksheets("Done").Rows(4).Insert
  .Rows(i).Copy .Parent.Worksheets("Done").Range("A4")
  .Rows(i).Delete
  End If
  Next i
  End With
  End Sub

  
  

• OldmanMarcin

  Shlizar, you wrote: The question is, you mention that you need to paste it on row "4". What if you find more then one Cell in column "F" that is not Blank
  
  This is why I want to do an "Insert" instead of a "paste". With an "Insert" a row is added, so if I insert more than 1 row the previous insertions will be shifted downward. This way the most recent data is always at the top of the list, in row 4. Rows 1 through 3 contain a title and column headers, which need to stay there.
  I have 2 sheets in the same workbook, sheet "Jobs" and sheet "Done". I use these sheets to list outstanding jobs and completed jobs. The actual jobs information starts at row 4 on both sheets. I want to be able to go down my "Jobs" list and type completion dates in each of the rows where that particular job has been completed. The date will be typed into column "F" in each row that is completed. Once I finish going down the "Jobs" list I want to be able to click 1 macro button and have all those completed jobs cut from "Jobs" and inserted into "Done" at row 4. The job must be deleted from " Jobs" so that all the remaining outstanding jobs will shift upward toward the top of the list.I'm not real familiar with coding macros. I can usually just record a macro, but in this case the macro must examine cell "F" in all the rows and look for any that have a date in them, completed jobs. I've tried writing "IF...THEN...ELSEIF statements to accomplish this task but can't get any of my attempts to work. I got one to move the rows one at a time, which means I have to continue clicking the macro button until the whole list has been checked. I would like to be able ckick the button once and move all the completed jobs at once.
  
  

• GunaChinna

  Well, in that case you can do the following:

  You can either check row by row if you reached the last row containing the "Job" information. You can do that via a While Cells(x,1).value <> "". During the same same check you can verify if the Cell in Column F is blank or not.

  What you also can do is create a selection (for instance via Range(Selection, Selection.End(xlDown)).Select) and check each cell within the selection by using something like FOR EACH cell IN Selection.Cells ... NEXT. Use the .ROW property from cell to define in which row you would like to chech the cell of Column F. For instance: If Sheets("Sheet1").Cells(cell.Row, 6).Value <> "" Then. After that, copy the Row and Insert it on to the desired sheet. Here's an example:

  If Sheets("Sheet1").Cells(cell.Row, 6).Value <> "" Then
  Sheets("Sheet1").Rows(cell.Row).Copy
  Sheets("Sheet2").Rows(4).Insert Shift:=xlDown
  End If
  

  
  
• Select/cut/insert