Entering Data to make a list?

[Bob Vance]

Is there a way that when I enter something into A1 &B1 it will fill down on
a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will
copy Apples and $12 , then if I type Pears & $5 it will copy to the next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
..
..
..
..

 

[Otto Moehrbach]

Bob
I'm assuming you have this list on another sheet. This little macro
will do the following:
When you enter something in B1 of the first sheet, it will copy A1:B1 to the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1, not A1, so
you should always fill in A1 first. The code can be changed so that it will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet module of
the first sheet. To access this module, right-click on the sheet tab of the
first sheet, select View Code. Paste this macro into the displayed module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

 

[Bob Vance]

Great, is there a way that the original data doesn't delete as I want to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
..
..
..
..

Bob
I'm assuming you have this list on another sheet. This little macro
will do the following:
When you enter something in B1 of the first sheet, it will copy A1:B1 to the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1, not A1, so
you should always fill in A1 first. The code can be changed so that it will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet module of
the first sheet. To access this module, right-click on the sheet tab of the
first sheet, select View Code. Paste this macro into the displayed module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will fill down on
a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will
copy Apples and $12 , then if I type Pears & $5 it will copy to the next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Otto Moehrbach]

Sure. Just delete the line that has ClearContents in it. Doing that will
negate the purpose of the line above it and the line below it (the
EnableEvents lines) so you should delete those also. HTH Otto

Great, is there a way that the original data doesn't delete as I want to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This little macro
will do the following:
When you enter something in B1 of the first sheet, it will copy A1:B1 to the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1, not A1, so
you should always fill in A1 first. The code can be changed so that it will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet module of
the first sheet. To access this module, right-click on the sheet tab of the
first sheet, select View Code. Paste this macro into the displayed module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will fill

down
on

a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will
copy Apples and $12 , then if I type Pears & $5 it will copy to the next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Bob Vance]

Thanks that worked. How do I change the script to make A150, C152 the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
..
..
..
..

Sure. Just delete the line that has ClearContents in it. Doing that will
negate the purpose of the line above it and the line below it (the
EnableEvents lines) so you should delete those also. HTH Otto

Great, is there a way that the original data doesn't delete as I want to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This little macro
will do the following:
When you enter something in B1 of the first sheet, it will copy A1:B1

to
the

bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1, not

A1,
so

you should always fill in A1 first. The code can be changed so that

it
will

operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet module of
the first sheet. To access this module, right-click on the sheet tab

of
the

first sheet, select View Code. Paste this macro into the displayed module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will fill down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain sheet

it
will

copy Apples and $12 , then if I type Pears & $5 it will copy to the next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Otto Moehrbach]

Bob
I don't know what you're asking. This is the first mention of Columns B
& C. What is it that you want to do? Is this related to your original
post? HTH Otto

Thanks that worked. How do I change the script to make A150, C152 the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it. Doing that will
negate the purpose of the line above it and the line below it (the
EnableEvents lines) so you should delete those also. HTH Otto

A1:B1

to module
of

tab

of

the
first sheet, select View Code. Paste this macro into the displayed
module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will

fill
down

on
a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it
will
copy Apples and $12 , then if I type Pears & $5 it will copy to

the
next

blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Bob Vance]

Yes it is, I used A & B as an example but in reality I need to copy the data
from A150, C152 to B,C, columns on another sheet, have tried adjusting your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
..
..
..
..

Bob
I don't know what you're asking. This is the first mention of Columns B
& C. What is it that you want to do? Is this related to your original
post? HTH Otto

Thanks that worked. How do I change the script to make A150, C152 the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

want

to

print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This little macro
will do the following:
When you enter something in B1 of the first sheet, it will copy

A1:B1

to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1,

not
A1,

so
you should always fill in A1 first. The code can be changed so

that
it

will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet module
of
the first sheet. To access this module, right-click on the sheet

tab

of
the
first sheet, select View Code. Paste this macro into the displayed
module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain

sheet
it

will
copy Apples and $12 , then if I type Pears & $5 it will copy to the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Otto Moehrbach]

Too late tonight. I'll get you something in the morning. Otto

Yes it is, I used A & B as an example but in reality I need to copy the data
from A150, C152 to B,C, columns on another sheet, have tried adjusting your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I don't know what you're asking. This is the first mention of

Columns

B
& C. What is it that you want to do? Is this related to your original
post? HTH Otto

Thanks that worked. How do I change the script to make A150, C152 the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it. Doing

that
will

negate the purpose of the line above it and the line below it (the
EnableEvents lines) so you should delete those also. HTH Otto
Great, is there a way that the original data doesn't delete as I

want

to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This little
macro
will do the following:
When you enter something in B1 of the first sheet, it will copy A1:B1
to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1, not
A1,
so
you should always fill in A1 first. The code can be changed so that
it
will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet module
of
the first sheet. To access this module, right-click on the

sheet
tab

of
the
first sheet, select View Code. Paste this macro into the displayed
module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it will copy

to
the

next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Bob Vance]

Thanx

--
Thanks in advance for your help....Bob Vance
..
..
..
..

Too late tonight. I'll get you something in the morning. Otto

Yes it is, I used A & B as an example but in reality I need to copy the data
from A150, C152 to B,C, columns on another sheet, have tried adjusting your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I don't know what you're asking. This is the first mention of

Columns

B
& C. What is it that you want to do? Is this related to your original
post? HTH Otto
Thanks that worked. How do I change the script to make A150,

C152
the

target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it. Doing that
will
negate the purpose of the line above it and the line below it (the
EnableEvents lines) so you should delete those also. HTH Otto
Great, is there a way that the original data doesn't delete as I want
to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This little
macro
will do the following:
When you enter something in B1 of the first sheet, it will copy
A1:B1
to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in

B1,
not

A1,
so
you should always fill in A1 first. The code can be changed

so
that

it
will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet
module
of
the first sheet. To access this module, right-click on the sheet
tab
of
the
first sheet, select View Code. Paste this macro into the displayed
module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will
fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it will copy to
the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[CrankyLemming]

Sorry to jump in, but this fits a problem I've got at the moment.

2 questions -

How would I get this macro to run on clicking a button, rather than automatically?

How could this be adapted so that the list stops at 10 entries?


Many thanks in advance,

Steve

 

[Otto Moehrbach]

Bob
Here is the macro with the cell changes you asked for. The code changed
a little because the two cells to copy are no longer contiguous. Remember
that this macro will be triggered when a change is made to the second cell,
now C152. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$152" Then
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End If
End Sub

Thanx

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Too late tonight. I'll get you something in the morning. Otto

Yes it is, I used A & B as an example but in reality I need to copy

the
data

from A150, C152 to B,C, columns on another sheet, have tried adjusting your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I don't know what you're asking. This is the first mention of Columns
B
& C. What is it that you want to do? Is this related to your original
post? HTH Otto
Thanks that worked. How do I change the script to make A150, C152
the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it. Doing that
will
negate the purpose of the line above it and the line below it (the
EnableEvents lines) so you should delete those also. HTH Otto
Great, is there a way that the original data doesn't delete as I
want
to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This little
macro
will do the following:
When you enter something in B1 of the first sheet, it will copy
A1:B1
to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first
sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry in B1,
not
A1,
so
you should always fill in A1 first. The code can be changed so
that
it
will
operate with an entry in either A1 or B1 if the other is occupied.
This macro is a sheet macro and must be placed in the sheet
module
of
the first sheet. To access this module, right-click on the sheet
tab
of
the
first sheet, select View Code. Paste this macro into the
displayed
module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it will
fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain
sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it will

copy
to

the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Otto Moehrbach]

Steve
Here is the macro as it is now with Bob's cell address changes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$152" Then
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End If
End Sub

To make this work with a button, the macro would look like this:
Sub SomeName()
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End Sub
Bob's macro has to be placed in the sheet module of the sheet in which he is
working at the time. Your macro has to go into a regular module.
To assign your macro to a button (I'll use a Forms button) do this:
1. Open your file to the sheet in which you want the button.
2. Click on View - Toolbars - Forms.
3. From the button bar that you see, click on the one that looks like a
button.
4. Move your mouse pointer to where you want the button and draw your
button.
5. When you do this, a window will pop up showing a listing of all the
macros in your file. Select the one you want to assign to this button and
click OK
6. That's it.

I'm sending you, direct via email, an otherwise blank file that has the
macro in the right place and a button with that macro assigned to it. Feel
free to post back with questions or contact me direct via email. Remove
"cobia97" from my email address. HTH Otto

Sorry to jump in, but this fits a problem I've got at the moment.

2 questions -

How would I get this macro to run on clicking a button, rather than automatically?

How could this be adapted so that the list stops at 10 entries?


Many thanks in advance,

Steve

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" & Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

 

[Bob Vance]

I got the yellow shading error through this line
Union([A150], [C152]).Copy .[A1]

--
Thanks in advance for your help....Bob Vance
..
..
..
..

Bob
Here is the macro with the cell changes you asked for. The code changed
a little because the two cells to copy are no longer contiguous. Remember
that this macro will be triggered when a change is made to the second cell,
now C152. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$152" Then
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End If
End Sub

Thanx

--
Thanks in advance for your help....Bob Vance
.
.
.
.

as

I

want
to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I'm assuming you have this list on another sheet. This
little
macro
will do the following:
When you enter something in B1 of the first sheet, it will copy
A1:B1
to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the first
sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry

in
B1,

not
A1,
so
you should always fill in A1 first. The code can be

changed
so

that
it
will
operate with an entry in either A1 or B1 if the other is
occupied.
This macro is a sheet macro and must be placed in the sheet
module
of
the first sheet. To access this module, right-click on the
sheet
tab
of
the
first sheet, select View Code. Paste this macro into the
displayed
module.
X-out of the module to get back to the spreadsheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" &
Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1

it
will

fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain
sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it will copy
to
the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Bob Vance]

Error says:
The command can not be used on multiple selections

--
Thanks in advance for your help....Bob Vance
..
..
..
..

I got the yellow shading error through this line
Union([A150], [C152]).Copy .[A1]

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
Here is the macro with the cell changes you asked for. The code changed
a little because the two cells to copy are no longer contiguous. Remember
that this macro will be triggered when a change is made to the second cell,
now C152. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$152" Then
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End If
End Sub

Thanx

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Too late tonight. I'll get you something in the morning. Otto
Yes it is, I used A & B as an example but in reality I need to

copy
the

data
from A150, C152 to B,C, columns on another sheet, have tried adjusting
your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I don't know what you're asking. This is the first mention of
Columns
B
& C. What is it that you want to do? Is this related to your
original
post? HTH Otto
Thanks that worked. How do I change the script to make A150,
C152
the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it. Doing
that
will
negate the purpose of the line above it and the line below

it
(the

EnableEvents lines) so you should delete those also. HTH Otto
Great, is there a way that the original data doesn't

delete

as

entry

in

B1,
not
A1,
so
you should always fill in A1 first. The code can be changed
so
that
it
will
operate with an entry in either A1 or B1 if the other is
occupied.
This macro is a sheet macro and must be placed in the
sheet
module
of
the first sheet. To access this module, right-click on the
sheet
tab
of
the
first sheet, select View Code. Paste this macro into the
displayed
module.
X-out of the module to get back to the spreadsheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" &
Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1 it
will
fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a certain
sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it

will
copy

to
the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Otto Moehrbach]

It worked fine with me. There has to be a space after "Copy".
What version of Excel do you have? Otto

Error says:
The command can not be used on multiple selections

--
Thanks in advance for your help....Bob Vance
.
.
.
.

I got the yellow shading error through this line
Union([A150], [C152]).Copy .[A1]

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
Here is the macro with the cell changes you asked for. The code changed
a little because the two cells to copy are no longer contiguous. Remember
that this macro will be triggered when a change is made to the second cell,
now C152. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$152" Then
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End If
End Sub
Thanx

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Too late tonight. I'll get you something in the morning. Otto
Yes it is, I used A & B as an example but in reality I need to copy
the
data
from A150, C152 to B,C, columns on another sheet, have tried adjusting
your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I don't know what you're asking. This is the first

mention

of

Columns
B
& C. What is it that you want to do? Is this related to your
original
post? HTH Otto
Thanks that worked. How do I change the script to make A150,
C152
the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it. Doing
that
will
negate the purpose of the line above it and the line below it
(the
EnableEvents lines) so you should delete those also. HTH Otto
Great, is there a way that the original data doesn't

delete

as
I
want
to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

message
Bob
I'm assuming you have this list on another sheet. This
little
macro
will do the following:
When you enter something in B1 of the first sheet, it will
copy
A1:B1
to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the
first
sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an

entry

in
B1,
not
A1,
so
you should always fill in A1 first. The code can be changed
so
that
it
will
operate with an entry in either A1 or B1 if the other is
occupied.
This macro is a sheet macro and must be placed in the
sheet
module
of
the first sheet. To access this module, right-click

on
the

sheet
tab
of
the
first sheet, select View Code. Paste this macro into the
displayed
module.
X-out of the module to get back to the spreadsheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" &
Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1

&B1
it

will
fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a
certain
sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it will
copy
to
the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[Bob Vance]

Just copied your script and posted it in to view code>right panel. Running
2002

--
Thanks in advance for your help....Bob Vance
..
..
..
..

It worked fine with me. There has to be a space after "Copy".
What version of Excel do you have? Otto

Error says:
The command can not be used on multiple selections

--
Thanks in advance for your help....Bob Vance
.
.
.
.

I got the yellow shading error through this line
Union([A150], [C152]).Copy .[A1]

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
Here is the macro with the cell changes you asked for. The code
changed
a little because the two cells to copy are no longer contiguous. Remember
that this macro will be triggered when a change is made to the second
cell,
now C152. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$152" Then
With Sheets("Two")
If .[A1] = "" Then
Union([A150], [C152]).Copy .[A1]
Else
Union([A150], [C152]).Copy .Range("A" & Rows.Count). _
End(xlUp)(2)
End If
[A1].Select
End With
End If
End Sub
Thanx

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Too late tonight. I'll get you something in the morning. Otto
Yes it is, I used A & B as an example but in reality I need to copy
the
data
from A150, C152 to B,C, columns on another sheet, have tried
adjusting
your
script but to many A & B's in the script

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Bob
I don't know what you're asking. This is the first

mention

of
Columns
B
& C. What is it that you want to do? Is this related to your
original
post? HTH Otto
Thanks that worked. How do I change the script to make A150,
C152
the
target cells to copy to Column B,C

--
Thanks in advance for your help....Bob Vance
.
.
.
.

Sure. Just delete the line that has ClearContents in it.
Doing
that
will
negate the purpose of the line above it and the line

below
it

(the
EnableEvents lines) so you should delete those also. HTH
Otto
Great, is there a way that the original data doesn't delete
as
I
want
to
print before it is deleted

--
Thanks in advance for your help....Bob Vance
.
.
.
.

message
Bob
I'm assuming you have this list on another sheet.
This
little
macro
will do the following:
When you enter something in B1 of the first sheet,

it
will

copy
A1:B1
to
the
bottom of the list in Columns A & B of the second sheet.
It will then clear (erase) the contents of A1:B1 of the
first
sheet.
Then it will select A1 of the first sheet.
Note that the macro is triggered to execute with an entry
in
B1,
not
A1,
so
you should always fill in A1 first. The code can be
changed
so
that
it
will
operate with an entry in either A1 or B1 if the

other

is

occupied.
This macro is a sheet macro and must be placed

in
the

sheet
module
of
the first sheet. To access this module, right-click on
the
sheet
tab
of
the
first sheet, select View Code. Paste this macro

into
the

displayed
module.
X-out of the module to get back to the spreadsheet. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
With Sheets("Two")
If .[A1] = "" Then
[A1:B1].Copy .[A1]
Else
[A1:B1].Copy .Range("A" &
Rows.Count).End(xlUp)(2)
End If
Application.EnableEvents = False
[A1:B1].ClearContents
Application.EnableEvents = True
[A1].Select
End With
End If
End Sub

Is there a way that when I enter something into A1 &B1
it
will
fill
down
on
a List? Like if I type apples in A1 & $12 in B1 on a
certain
sheet
it
will
copy Apples and $12 , then if I type Pears & $5 it will
copy
to
the
next
blank cell and so on
--
Thanks in advance for your help....Bob Vance
.
.
.
.

 

[CrankyLemming]

Steve
Here is the macro as it is now with Bob's cell address changes:

Thanks, Otto. This helped me get the sheet doing what I wanted.

Syeve