automated copy & paste

[Guest]

hello again, i'm trying to get this to work:-
A1 = date ( changeable )
A2 = £ ( changeable )
B1 to B100 ( if B1 has £ in it move to next cell down without over righting
previous cell) B100 could be greater , just depends on how much data i
collect.
up to now i can only get data by copy and pasting information from A2 and
placing it in the next empty available cell in B.
i've tryed using the date but all cells then change to the current contents
of A2 and really want previous contants to remain untouched.

Thanks inadvance

 

[Otto Moehrbach]

What you wrote is very difficult to follow. You must understand that of all
of us who read and write in these newsgroups, you are the only one who
understands what you have and what you are wanting to do. Explain it to us
as you would explain it to someone who just walked in off the street and
knows nothing. HTH Otto

 

[Sandy Mann]

If I follow you correctly and you want to add the data in A2 to the bottom
of a list of data in Column B then right click on the sheet tab and select
View Code and paste this Worksheet code into the module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False

LastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

Cells(LastRow, 2).Value = Cells(2, 1).Value

Application.EnableEvents = True

End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

thanks for your help Sandy Mann,
i forgot to add another cell
Colum C = date
so is there any way you can only get it to put information in the next
available cell if the 2 date match.
other wise it works just like i want it to.
Is there an easy way for newbies to enter a formula rather than entering a
code wich means nothing to me.
sorry to be a pain

 

[Sandy Mann]

The problem with trying to do what you want with only formulas is that
formulas are volatile inasmuch as that they will recalculate when the
dependent cells change. For example in B2:
=IF(C2=$A$1,$A$2,"")
will return the contents of A2 when the date in A1 is the same as the date
in C2. However, as soon as you change either A1 or A2 then B2 will change
either to an empty string if you change A1 or the new value if you change
A2.

The way to stop this is to copy the value in that cell and paste it back
using Paste Special but that is more trouble then simple entering the value
manually. You could get an Event Macro to do it automatically when you make
a change to A1 or A2 but that is a poor solution which brings other
problems.

I think that the best thing would be, like Otto said, if you were to try to
explain to us as fully as you can, what it is that you are trying to do.


--
Regards

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

mmm ok i'll try to explain abit more, but thanks anyway for your responces so
far.
the problem i have is i'm trying to keep track of how much extra £'s i make
in a given week, fortnight, month therefore
A1 = current date
A2 = extra £'s i've made so far,
Column C = date of next pay starting with 1st empty cell in C then adding 1
cell down at a time.
so if
A1 = 1 day less than the date in Column C or then put in 1st empty cell in
Column B and so on
so lets say C1 = the 1st date and A1 = 1 day less than C1 then put contents
of A2 in B1
then repeat all over again

hope this helps.


thopught this was gonna be hard to do as every attempt i've don it changes
every cell value in colum C to the same value

 

[Sandy Mann]

Hi Dark Night,

A further couple of questions - If the date in C1, (or can it be further
down Column C?), is the same as the date in A1 and there are three entries
down Column B, where do you want to have the data in A2 to go to?

If it helps you to explain what you want then by all means send me a sample
sheet. Just replace the part form the @ as it says in my signatute

--
Rrgards

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

cheers Sandy Mann
sending you a copy now.

Hi Dark Night,

A further couple of questions - If the date in C1, (or can it be further
down Column C?), is the same as the date in A1 and there are three entries
down Column B, where do you want to have the data in A2 to go to?

If it helps you to explain what you want then by all means send me a sample
sheet. Just replace the part form the @ as it says in my signatute

--
Rrgards

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Nothing has arrived yet Dark Night - would you like to try again and I will
have a look tomorrow

--
Regards

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks for the code Sandy Mann everything works fine,
just 1 little question, can this code be added to take more than 1 cell
(26,9) be copyed to column P.
if so other cells to include would be:-
cell(26,10) up to cell (26,12) and tranasfer information to cell (x,16) up
to (x,19) ?

think this is the bit i'm refering to...

for x = 4 to endrow
if cells (2,1).value< cells(x+1,15).value2 and_
cells(2(1).value2 >= cells(x,15).value2 then
cells(x,16).value = cells(26,9).value
goto getout

 

[Sandy Mann]

Hi DarkNight,

Assuming that you are becoming confused by my use of x and you mean to copy
your 'Difference' cell and the three cells to the right of it to the three
cells to the 'Extra £ Made' and the three cells to the right replace the
line:

cells(x,16).value = cells(26,9).value

with:

Range(Cells(x, 16), Cells(x, 19)).Value = _
Range(Cells(26, 9), Cells(26, 12)).Value

Note that this is all one line with a line break after the Value= created
by a space follows by an underscore.

Probably the easiest thing to do is to copy that line from this post,
highlight the line to be replace in the code and paste the new line in.

If I've got you requirements wrong then paste back.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Cheers Sandy Mann , i've coped that but dont seem to work might be becaouse i
changed the original sheet about. would it be ok to send you it via email
again?

 

[Sandy Mann]

Hi,

Yes by all means send you sheet and I will take a look at it.

--
Regards


Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk