?
Solved

Excel 2002:  Simple VB script...help!

Posted on 2003-03-31
14
Medium Priority
?
197 Views
Last Modified: 2008-03-17
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
Comment
Question by:dsprenzel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 8240355
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8240358
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:dsprenzel
ID: 8241620
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
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
ID: 8241648
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
 

Author Comment

by:dsprenzel
ID: 8241690
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8241710
how do you enter the data?

have you looked under tools|options|calculation|and set it to automatic?
0
 

Author Comment

by:dsprenzel
ID: 8241757
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8241774
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8241781
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
 
LVL 44

Expert Comment

by:bruintje
ID: 8241787
well time for me to go ....

meant CCD instead of CCd
0
 

Author Comment

by:dsprenzel
ID: 8241797
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
 

Author Comment

by:dsprenzel
ID: 8247162
Thanks - the macro security levels were set to HIGH - that's why it wasn't happening for me....
0
 
LVL 44

Expert Comment

by:bruintje
ID: 8247956
:) thanks for the grade .... strange then i would've expected at least a warning on opening xl sheets with macros
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question