Macro button to conditionally cut rows to new worksheet

Excel Excel 2007
January 12, 2012 at 09:09:09
Specs: Windows 7
Hi guys,

I've browsed loads of previous questions to attempt to aid me in this but none have seemed to help.

I am a complete newbie with VB so bear with me.

I have a workbook with a worksheet called "Pipeline" in which users will type in to, I would like to assign a macro to a button that when pressed would look to column "O" and if the text says "45%" then cut that entire row and paste it into the next available row in worksheet "Prospect" (which is already created). I would also need to create similar conditions to cut rows into other worksheets based on the data in column "O" but i'm sure i could figure that out from the first question.

Any help is greatly appreciated, been stuck on this one for a few days now. (I am rubbish with excel)

Thanks a lot


See More: Macro button to conditionally cut rows to new worksheet

Report •


#1
Report •

#2
January 16, 2012 at 04:52:51
Hi,

Thanks for the quick reply.

I've tried using that code before and it creates new worksheets for the rows, but I wanted the rows to be pasted into already created worksheets based on the criteria in column "o". I have tried amending the formula to work as I wanted but my VB skills are far from good.

Any help is greatly appreciated


Report •

#3
January 16, 2012 at 08:13:37
Sub CopyRowOnO()
'Find Length of Pipeline Column O
  lastRow = Sheets("Pipeline").Range("O" & Rows.Count).End(xlUp).Row
'Loop through Column O, Copy if value = .45
    For nxt_CopyRw = 1 To lastRow
      If Sheets("Pipeline").Range("O" & nxt_CopyRw) = 0.45 Then
'Find next open row in Prospect sheet
          nxt_PasteRw = Sheets("Prospect").Range("O" & Rows.Count).End(xlUp).Row + 1
'Copy/Paste
               Sheets("Pipeline").Range("O" & nxt_CopyRw).EntireRow.Copy _
               Destination:=Sheets("Prospect").Range("A" & nxt_PasteRw)
      End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question