Automatically populating cells down a row

[erstaples]

Hi all--

I have a workbook with sending cells A1:E1 on Sheet1.

What I would like to do is link these to Sheet2 and have A1:E1 populat
down the rows in Sheet2 each time the values in A1:E1 are changed o
updated in Sheet1... For instance, the first time cells A1:E1 are fille
in Sheet1 it populates A1:E1 on Sheet2, the second time these sendin
cells are filled it populates A2:E2 on Sheet2, to A3:E3, and so on.

I am quite new to Excel, but I have done an extensive bit of research o
the web for answers to this scenario, and I haven't had any succes
finding a solution.

Could anyone point me in the right direction or tell me what I need t
do to make this work?

Thanks, and apologies ahead of time if this question has been asked o
these boards before. I didn't see it.

Eri

 

[Gord Dibben]

I would suggest worksheet_change event code but need a couple of
details.............

How do Sheet1 A1:E1 cells get changed or updated?

Would you want the code to run when any of Sheet1 A1:E1 were changed
or only when all of them were changed?


Gord

 

[erstaples]

Hi Gord,

Thanks for the reply. I've been giving it some thought, and since I'
going to be passing this workbook along to users with little knowledg
in excel I want to make it as simple as possible.

I researched macro buttons and I think that would be the best way to g
about putting this into action. Anytime the macro button is pushed i
Sheet1 it will update everything into a new row in Sheet2.

My only worry is that the macro button will be pushed too often, postin
to sheet2 haphazardly, or submitting duplicate entries. Here is
simplified version of the information I want submitted:

Column 1: Job Number 2: Client 3: Description
4: Time to completion

Anytime the button is pushed with a new job number, i want data fro
Sheet1 to be entered on a new row. If the button is pushed with the sam
job number I want Sheet2 to simply update that information.

For instance, if this is on Sheet2

Column 1: Job# 44123 2:Microsoft 3:Business Cards 4:
hours

And the button is pressed with this information:

Column 1: Job#44123 2: Microsoft 3:Business Cards 4:
hours

I want to simply update the information on the existing row.

If it's an entirely new job number I want the information the next ope
row.

I hope this makes sense.

I'm new to VBA, but I think that what I'm looking for, in part, is an I
code. Something like

IF it's an existing job number in column 1, then update adjacent column
2,3,4,
Else enter data to new row...

I hope this makes sense.

Eric

;1602206']I would suggest worksheet_change event code but need a coupl
of
details.............

How do Sheet1 A1:E1 cells get changed or updated?

Would you want the code to run when any of Sheet1 A1:E1 were changed
or only when all of them were changed?


Gord

On Tue, 29 May 2012 16:13:28 +0000, erstaples

Hi all--

I have a workbook with sending cells A1:E1 on Sheet1.

What I would like to do is link these to Sheet2 and have A1:E populate
down the rows in Sheet2 each time the values in A1:E1 are changed or
updated in Sheet1... For instance, the first time cells A1:E1 ar filled
in Sheet1 it populates A1:E1 on Sheet2, the second time these sending
cells are filled it populates A2:E2 on Sheet2, to A3:E3, and so on.

I am quite new to Excel, but I have done an extensive bit of researc on
the web for answers to this scenario, and I haven't had any success
finding a solution.

Could anyone point me in the right direction or tell me what I need to
do to make this work?

Thanks, and apologies ahead of time if this question has been asked on
these boards before. I didn't see it.

Eric

 

[erstaples]

Gord,

Is it safe to say that you're a bit stumped on this one? Just wonderin
if I should keep checking back for an answer or not... I am continuin
to research VBA and will post if I come up with a solution on my own
Just reaching out to see if it's possible...

Thanks,

Eric

 

[Gord Dibben]

Sorry about that...........kinda just lost track.

I will have another look today. Yes I am a bit stumped by the added
conditions

1. If jobnum in Ax is unique, copy Axx over to Sheet2 after Sheet1
Axx are filled in.

2. If jobnum in Sheet1 Ax is a duplicate then search for that
duplicate jobnum on Sheet2 and copy just Bxx and overwrite Sheet2
Bxx.

So we first have to have jobnum entered in Sheet1 then B filled in.

When next time a jobnum is entered we have to check to see if it a
duplicate of an exisring jobnum. Then we have to check and see if any
data in B has been changed.
Then we have to search for that jobnum on Sheet2.
How am I doing so far?

How often could you be entering a duplicate jobnum.

Why the overwrite?

Couldn't you just keep everything on one sheet and simply filter.

Gord

 

[erstaples]

Gord

Thank you for taking the time to respond. I will get back to this in th
morning. I just saw your response but it's a little late and my brain i
dead from the work day. ;

 

[Gord Dibben]

Perhaps you could email me a sample workbook with some examples of
data and an explanation of what you're wanting to achieve.

gorddibbATshawDOTca change the obvious.


Gord

 

[erstaples]

I figured it out, Gord. It turns out that the solution was really reall
simple

I did a Do Until... O

Code
-------------------

trends_counter =
Do Until Trends.Sheets("sheet1").Cells(trends_counter, 1).Value = ThisWorkbook.Sheets("komori").Range("JobNumber").Value Or Trends.Sheets("sheet1").Cells(trends_counter, 1).Value = "
trends_counter = trends_counter +

Loo


ThisWorkbook.Sheets("komori").Range("JobNumber").Cop
Trends.Sheets("sheet1").Range("A" & trends_counter).PasteSpecial xlPasteAl
ThisWorkbook.Sheets("komori").Range("JobSpecs").Cop
Trends.Sheets("sheet1").Range("B" & trends_counter).PasteSpecial xlPasteAl


Trends.Sav
Trends.Clos


-------------------

What this does is the counter in the second book first looks for the jo
number. If the counter finds the job number it stops there and any ne
job specs from the first workbook will update down the row. If th
counter doesn't find the job number it goes to the first empty cell an
the new job & job specs are copy/pasted from the first workbook

I'm learning as I go here. If there's a simpler or more efficient way o
doing this I'd love to hear it, but I've tested this and it does what
need it to do..

 

[Gord Dibben]

Good deal...........

Gord