• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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.
^^^^^^^^^^^^^^^^^^^^^^^^^^^snipped^^^^^^^^^^^^^^^^^^^^^^^^^^^
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("a65536").End(xlUp).Offset(1, 0)
    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.
0
dsprenzel
Asked:
dsprenzel
  • 8
  • 5
1 Solution
 
byundtCommented:
1) Copy the VBA snippet
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
0
 
bruintjeCommented:
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("a65536").End(xlUp).Offset(1, 0)
   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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dsprenzelAuthor Commented:
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.
0
 
bruintjeCommented:
that's very weird have you looked at the sample?

and what about

Private Sub Worksheet_Change(ByVal Target As Range)

If InStr(1, Target.Address, "M") > 0 Then
  If Target.Value = "YES" Then
      Target.EntireRow.Copy _
          Sheets("YES").Range("a65536").End(xlUp).Offset(1, 0)
  End If
End If

End Sub
0
 
dsprenzelAuthor Commented:
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.
0
 
bruintjeCommented:
how do you enter the data?

have you looked under tools|options|calculation|and set it to automatic?
0
 
dsprenzelAuthor Commented:
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...
0
 
bruintjeCommented:
try

If Target.Column = 5 Then
  If Target.Value = "YES" Then
      Target.EntireRow.Copy _
          Sheets(3).Range("a65536").End(xlUp).Offset(1, 0)
  End If
End If

or

If Target.Column = 5 Then
  If Target.Value = "YES" Then
      Target.EntireRow.Copy _
          Sheets("CCD").Range("a65536").End(xlUp).Offset(1, 0)
  End If
End If

0
 
bruintjeCommented:
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("a65536").End(xlUp).Offset(1, 0)
  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(xlUp).Offset(1, 0)
  End If
End If

End Sub
0
 
bruintjeCommented:
well time for me to go ....

meant CCD instead of CCd
0
 
dsprenzelAuthor Commented:
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...
0
 
dsprenzelAuthor Commented:
Thanks - the macro security levels were set to HIGH - that's why it wasn't happening for me....
0
 
bruintjeCommented:
:) thanks for the grade .... strange then i would've expected at least a warning on opening xl sheets with macros
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now