Excel 2007 "If....Then select Row and move&quSolved/Closed

Question

Hi all!

I have what would appear to be a basic question, but can't seem to put my brain around the solution.

I have a spreadsheet on Sheet 1.  One of the cells in each row is a validation cell for me, which (based on whether any data is in another cell), spits out a "1" if the job is complete.

I'd like to know how I can tell Excel that if as "1" appears in cell S2, to select the entire row, and move to another sheet, which will compile all our completed jobs.

If anybody has suggestions, they are greatly appreciated!

Thanks!

rizvisa1 · Answer

Does it have to be auto job or could you live with manually firing it?

cujo · Answer

I wouldn't have a problem with manually firing it off.  Just being able to clear completed lines so that other "high-priority jobs" could be moved to the top is what I'm shooting for.


Thanks much!

Matt

rizvisa1 · Answer

Well see if this works for you  

Assumption  
Main Sheet is called WIP  
Archive Sheet is called Done  
Presumption is that there are no more than 26 column in the WIP and Done Sheets  

Steps:
1. Press ALT + F11 to launch VBE
2. Click on insert an Add a new module
3. Copy the code below and paste on the new module
4. To run the code, (click F5)

Sub cleanup()  

Dim iMaxCol As Integer  

    iMaxCol = 26  
      
    Sheets("WIP").Select  
      
    If ActiveSheet.AutoFilterMode Then  
        Cells.Select  
        Selection.AutoFilter  
    End If  
      
    Cells.Select  
    If ActiveSheet.AutoFilterMode = False Then  
        Selection.AutoFilter  
    End If  
      
    Selection.AutoFilter Field:=19, Criteria1:="=" & 1, Operator:=xlAnd  
      
    ilastrow = Cells(Rows.Count, 1).End(xlUp).Row  
      
    For iCol = 2 To iMaxCol  
        tMaxRow = Cells(Rows.Count, iCol).End(xlUp).Row  
        If (tMaxRow > ilastrow) Then ilastrow = tMaxRow  
          
    Next  
    
    If (ilastrow = 1) Then  
        MsgBox ("Get back to work, nothing is done yet!!!")  
      
        If ActiveSheet.AutoFilterMode Then  
            Cells.Select  
            Selection.AutoFilter  
        End If  
          
        Exit Sub  
    End If  
      
    If Sheets("Done").AutoFilterMode Then  
        Sheets("Done").Cells.Select  
        Sheets("Done").Selection.AutoFilter  
    End If  
          
    iMaxDone = Sheets("Done").Cells(Rows.Count, iCol).End(xlUp).Row  
    For iCol = 2 To iMaxCol  
        tMaxRow = Sheets("Done").Cells(Rows.Count, iCol).End(xlUp).Row  
        If (tMaxRow > iMaxDone) Then iMaxDone = tMaxRow  
          
    Next  
      
    If iMaxDone < 2 Then iMaxDone = 1  
    iMaxDone = iMaxDone + 1  
      
    'Rows("2:Range(Cells(2, 1), Cells(ilastrow, iMaxCol)).Select  
    Rows("2:" & ilastrow).Select  
    Selection.Copy  
      
    Sheets("Done").Select  
    Cells(iMaxDone, 1).Select  
    Selection.PasteSpecial  
      
    Sheets("WIP").Select  
    Rows("2:" & ilastrow).Delete  
      
    If ActiveSheet.AutoFilterMode Then  
        Cells.Select  
        Selection.AutoFilter  
    End If  
End Sub

cujo · Answer

It appears my limited knowledge on the high end programming for Excel 2007 can't compare with most of you.  Per your recommendation, I have renamed the sheets accordingly, and copied the code.  I assumed this went in the visual basic screen and copied into "This Workbook".  When that didn't work on a test, I then added the code to both schets (WIP and Done).

Again, I have a feeling because I've never gotten this granular before in Excel, it's a problem on my part.

If anyone wants to look at the file and give me your thoughts, here's a link to download:

https://www.sendspace.com/file/uag0g6


Thanks again to all of you.  Now I realize why I stick doing my job, and never attempted to go into programming!  ;)

Matt

cujo · Answer

For whatever reason my previous posts didn't attach to the thread.

I attempted to attach the file to the thread as well, but I'm having problems with that as well.

I have a feeling that because of my limited knowledge at this level of programming, I've done something wrong.  Per your suggestion, I have renamed the sheets, and added the code in the visual basic editor.  When it didn't propertly work on a test line, I then added the code not only in the "This Workbook" along with the "WIP" sheet and "Done" sheet.

If any of you have a suggestion how I can attach the file to this thread, I'd be more than happy to post it for eveyone's thoughts.

Thanks again!

Matt

aquarelle · Answer

Hello,

I restore the above message thus now you can see the link to download the excel file.

Good continuation

cujo · Answer

Ha!  I even surprise myself.  A small little IT10T error on my part caused the problem.

Thanks greatly..This does exactly what I need to have done.  Kudos to you!

Matt

Excel 2007 "If....Then select Row and move&qu

7 replies

Subscribe To Our Newsletter!