dsprenzel
asked on
Excel 2002: Simple VB script...help!
Greetings,
I've got a spreadsheet which contains 3 worksheets. The main
worksheet is a list of records, and when i complete a task for a
record, i enter YES into a field for that record. After doing that,
i would like that record to automatically be copied to another
worksheet, which would contain all of the records that i have entered
a YES for.
Currently i am doing this manually, by seleceting the record, and
copying & pasting, but this is proving to be tedious, and i know
(think...hope) there is a way to automate this.
Someone provided me with this code, but seeing that i'm VB-illiterate, i don't know how to use it properly.
^^^^^^^^^^^^^^^^^^^^^^^^^^
You can use the Worksheet_Change event to copy a line when a certain range
is changed to YES. It might look like this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("YES").Range("a6553
End If
End If
End Sub
This example looks in column E. If you enter a yes, it copies the whole row
to a sheet named YES to the next empty cell in column A.
I've got a spreadsheet which contains 3 worksheets. The main
worksheet is a list of records, and when i complete a task for a
record, i enter YES into a field for that record. After doing that,
i would like that record to automatically be copied to another
worksheet, which would contain all of the records that i have entered
a YES for.
Currently i am doing this manually, by seleceting the record, and
copying & pasting, but this is proving to be tedious, and i know
(think...hope) there is a way to automate this.
Someone provided me with this code, but seeing that i'm VB-illiterate, i don't know how to use it properly.
^^^^^^^^^^^^^^^^^^^^^^^^^^
You can use the Worksheet_Change event to copy a line when a certain range
is changed to YES. It might look like this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("YES").Range("a6553
End If
End If
End Sub
This example looks in column E. If you enter a yes, it copies the whole row
to a sheet named YES to the next empty cell in column A.
Hello Dsprenzel,
what you can do is this
-open the vb editor with alt+f11
-then look in the upper left pane for a sheet1 or sheet2 object
-this object represents the first second etc... sheet in your workbook
-now double click the one that you want to put the yes on
-then paste the code from above
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("YES").Range("a6553
End If
End If
End Sub
-be sure to have a sheet named yes in your workbook
-save and close the editor
-then enter your YES
you can pick up a simple sample of this
http://www.bredlum.com/ee_temp/xlYESSheet.xls
HAGD:O)Bruintje
what you can do is this
-open the vb editor with alt+f11
-then look in the upper left pane for a sheet1 or sheet2 object
-this object represents the first second etc... sheet in your workbook
-now double click the one that you want to put the yes on
-then paste the code from above
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("YES").Range("a6553
End If
End If
End Sub
-be sure to have a sheet named yes in your workbook
-save and close the editor
-then enter your YES
you can pick up a simple sample of this
http://www.bredlum.com/ee_temp/xlYESSheet.xls
HAGD:O)Bruintje
ASKER
Hmm, it's still not happening for me.
My Target Column is the 'M' column - which would be 13, correct?
I have input the code for the 1st worksheet, and when i test by typing YES into any cell on column 13, nothing happens on the worksheet it is supposed to append to.
My Target Column is the 'M' column - which would be 13, correct?
I have input the code for the 1st worksheet, and when i test by typing YES into any cell on column 13, nothing happens on the worksheet it is supposed to append to.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried your sample XL sheet... and it doesn't seem to work. If i go to the next row down, and enter YES in column 5, it doesn't copy to the other worksheet.
how do you enter the data?
have you looked under tools|options|calculation|
have you looked under tools|options|calculation|
ASKER
Yes, it is set to automatic. Maybe more detail would help:
worksheet 1 is called Base Building RFI's.
worksheet 3 (the destination worksheet for the row/record to be copied from worksheet 1) is called CCD.
When YES is entered into column M of worksheet 1, that entire row should be copied to worksheet 3.
Thanks again for all your help...
worksheet 1 is called Base Building RFI's.
worksheet 3 (the destination worksheet for the row/record to be copied from worksheet 1) is called CCD.
When YES is entered into column M of worksheet 1, that entire row should be copied to worksheet 3.
Thanks again for all your help...
try
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets(3).Range("a65536").
End If
End If
or
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("CCD").Range("a6553
End If
End If
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets(3).Range("a65536").
End If
End If
or
If Target.Column = 5 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("CCD").Range("a6553
End If
End If
oops
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("CCd").Range("a6553
End If
End If
End Sub
or
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets(3).Range("a65536").
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets("CCd").Range("a6553
End If
End If
End Sub
or
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 13 Then
If Target.Value = "YES" Then
Target.EntireRow.Copy _
Sheets(3).Range("a65536").
End If
End If
End Sub
well time for me to go ....
meant CCD instead of CCd
meant CCD instead of CCd
ASKER
Yes, it is set to automatic. Maybe more detail would help:
worksheet 1 is called Base Building RFI's.
worksheet 3 (the destination worksheet for the row/record to be copied from worksheet 1) is called CCD.
When YES is entered into column M of worksheet 1, that entire row should be copied to worksheet 3.
Thanks again for all your help...
worksheet 1 is called Base Building RFI's.
worksheet 3 (the destination worksheet for the row/record to be copied from worksheet 1) is called CCD.
When YES is entered into column M of worksheet 1, that entire row should be copied to worksheet 3.
Thanks again for all your help...
ASKER
Thanks - the macro security levels were set to HIGH - that's why it wasn't happening for me....
:) thanks for the grade .... strange then i would've expected at least a warning on opening xl sheets with macros
2) Open up VBA by entering ALT + F11
3) Double-click on Sheet1 (if that one contains the YES)
4) Paste the snippet on the blank macro sheet that just appeared
5) ALT + F11 to get back to your spreadsheet
6) Test it to see if it works