If Statement..i think

[zak]

Hi

I'm wondering if anyone can help me..

I have a worksheet with a drop down list in column H2:H100 that has two
options; Complete or Pending.

I want to write some sort of macro to say if pending, then leave the row on
the sheet (rows a1:g1). But if 'Complete' is selected then cut the row and
paste it in the 'History' worksheet.

Is this possible?

Thank you

 

[JLGWhiz]

Drop down?

 

[zak]

Yes a drop down list with two options; Complete or Pending..as in the one you
can create via Data - Data Validation - Allow: List

 

[aamerrasheed]

Hi,
You can use this...

Sub Macro()
'Sheet3 .....all data..
'Sheet2......contains filtered data
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 8) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet2.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

Yes a drop down list with two options; Complete or Pending..as in the one you
can create via Data - Data Validation - Allow: List

Drop down?

[quoted text clipped - 12 lines]

 

[zak]

Hello

This is great, the code you have given me works!! I was wondering if you
can help me add 2 more things to it:

1) How can i get the row in sheet one to get deleted once it has been copied
over to sheet 7?

1) In sheet 7, id like row 1 to have a heading, so rows copied over from
sheet 1, should then go into row 2 onwards?

Here is the code which I have:

Sub Macro()
'Sheet1 .....all data..
'Sheet7......contains filtered data
Dim i, j, n As Integer
j = 1
Sheet1.Activate
For i = 1 To Sheet1.UsedRange.Rows.Count
If (Cells(i, 8) = "Complete") Then
For n = 1 To Sheet1.UsedRange.Columns.Count
Sheet7.Cells(j, n) = Sheet1.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

Thanks for your help.

Hi,
You can use this...

Sub Macro()
'Sheet3 .....all data..
'Sheet2......contains filtered data
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 8) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet2.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

Yes a drop down list with two options; Complete or Pending..as in the one you
can create via Data - Data Validation - Allow: List

Drop down?

[quoted text clipped - 12 lines]

Thank you

 

[zak]

Hello

I don't think I have had a response to my question to, so I'd like to kindly
ask you again.

I have used your code which works great, but id like to take it a little bit
further and was wondering if you can help me?

I want to:

1) How can i get the row in sheet one to get deleted once it has been copied
over to sheet 7?

2) Any new rows that are populated in the sheet 1, id like for it to be
added to a new row in sheet 7, an not over write the rows already in sheet 7.

Please let me know if you can help me with either of my queries.

Thanking you in advance.

Here is the code which I have:

Sub Macro()
'Sheet1 .....all data..
'Sheet7......contains filtered data
Dim i, j, n As Integer
j = 1
Sheet1.Activate
For i = 1 To Sheet1.UsedRange.Rows.Count
If (Cells(i, 8) = "Complete") Then
For n = 1 To Sheet1.UsedRange.Columns.Count
Sheet7.Cells(j, n) = Sheet1.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

Hi,
You can use this...

Sub Macro()
'Sheet3 .....all data..
'Sheet2......contains filtered data
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 8) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet2.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

Yes a drop down list with two options; Complete or Pending..as in the one you
can create via Data - Data Validation - Allow: List

Drop down?

[quoted text clipped - 12 lines]

Thank you