Copy a Cell from worksheet to worksheet

[SkippyPB]

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.

////
(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

 

[Pflugs]

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

 

[Pflugs]

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

 

[SkippyPB]

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

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.

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

 

[Pflugs]

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

 

[SkippyPB]

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.


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