On Fri, 27 Jul 2007 12:30:00 -0700, Pflugs
<(E-Mail Removed)> wrote:
>It will make a difference that you changed the name. Worksheet_Change is a
>special type of macro called an event that runs every time a cell's contents
>are changed. There are other Worksheet events, too, like Activate and
>SelectionChange. To view these, go to the Sheet's code and click on the
>right hand drop down box. You should see all available events.
>
>You can add the code I sent you to the existing Change event. Just remove
>the first and last lines and copy it to the existing one just before the end.
> The change macro should perform both actions.
>
>Sorry it took a while to get back to you. Let me know if this works.
>
>Pflugs
>
Well a big DOH!! on my part. I made the change and all is well.
Thanks for your help.
>"SkippyPB" wrote:
>
>> On Fri, 27 Jul 2007 10:14:03 -0700, Pflugs
>> <(E-Mail Removed)> wrote:
>>
>> >I tested this code a little further, and I found that it didn't work if I
>> >copied multiple values. As such, here is a slight improvement (with
>> >comments):
>> >
>> >Private Sub Worksheet_Change(ByVal Target As Range)
>> > ' In case something goes wrong...
>> > On Error GoTo errorhandler
>> >
>> > ' Check each cell that was changed
>> > For Each c In Target
>> > ' If cell is in column B and not row 1...
>> > If c.Column = 2 And c.Row > 1 Then
>> > ' ...copy the cell to the next sheet
>> > Sheets("EOR 2").Cells(c.Row, 1) = c
>> > End If
>> > Next c
>> >
>> > Exit Sub
>> >
>> >errorhandler:
>> > ' ...notify the user
>> > temp = MsgBox("I couldn't copy all the values for some reason. Please
>> >doublecheck my work!", vbCritical)
>> >End Sub
>> >
>> >Also, I found that changes by this code are undoable, something I haven't
>> >seen before. Anyway, hope THIS helps.
>> >
>> >Pflugs
>> >
>> >"Pflugs" wrote:
>> >
>> >> Hey SkippyPB,
>> >>
>> >> Copy this into the "EOR 1" sheet's code. If you don't know how to do this,
>> >> let me know.
>> >>
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >> If Target.Column = 2 And Target.Row > 1 Then
>> >> Sheets("EOR 2").Cells(Target.Row, 1) = Target
>> >> End If
>> >> End Sub
>> >>
>> >> This will run every time you enter or change a value in sheet "EOR 1". It
>> >> checks to make sure the change was in column b, and then it copies the value
>> >> into the corresponding row in column A of sheet "EOR 2".
>> >>
>> >> Hope this helps,
>> >> Pflugs
>> >>
>> >> "SkippyPB" wrote:
>> >>
>> >> > Sorry if this has been answered before, but I just found this
>> >> > newsgroup and searched through it for about an hour but could not find
>> >> > anything that matched my needs.
>> >> >
>> >> > Here is what I have.
>> >> >
>> >> > I have a workbook with two worksheets named EOR 1 and EOR 2.
>> >> > Whenever data is entered into EOR 1 Column B, I want it copied to the
>> >> > same row column A in EOR 2.
>> >> >
>> >> > Currently I have the following IF function in EOR 2 Column A Row 2:
>> >> > =IF('EOR 1'!B2="","",'EOR 1'!B2)
>> >> >
>> >> > Note: the first row of both worksheets contains headings so I don't
>> >> > want those copied.
>> >> >
>> >> > The IF function has been propagated in EOR 2 from Column A Row 2 to
>> >> > Column A Row 65536. It works great but it also increases the size of
>> >> > the workbook since there is something in all those rows in EOR 2. This
>> >> > is a problem when a CSV file is created from each worksheet using the
>> >> > SAVE AS feature of Excel.
>> >> >
>> >> > In a nutsheel I'd like to replace the IF with a VBA subroutine that
>> >> > does the same thing.
>> >> >
>> >> > Thanks in advance for any help.
>> >> > Steve
>> >> >
>>
>> Thanks for the effort but neither one worked. That is, nothing was
>> copied to column A of EOR 2 when something was entered in column B of
>> EOR 1. The only change I made was to the name of the macro. I
>> already have a Worksheet_Change macro (which changes all lower case
>> letters to upper case) so I named it Worksheet_Copy. Other than that,
>> I didn't change anything else.
>>
>> If it makes a difference, my VB is VB 3 and my Excel is Office Excel
>> 2003 SP2.
>>
>> Steve
>>
Regards,
////
(o o)
-oOO--(_)--OOo-
** Norm's Greetings on US TV Show "Cheers" **
SAM: "What'll you have Normie?"
NORM: "Well, I'm in a gambling mood Sammy. I'll take a glass of whatever
comes out of that tap."
SAM: "Looks like beer, Norm."
NORM: "Call me Mister Lucky."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Remove nospam to email me.
Steve
|
Similar Threads
