Automatically populating cells down a row
 

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 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 are 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 research 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

Automatically populating cells down a row
 

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

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


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 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 are 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 research 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

Hi Gord,

Thanks for the reply. I've been giving it some thought, and since I'm going to be passing this workbook along to users with little knowledge 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 go about putting this into action. Anytime the macro button is pushed in Sheet1 it will update everything into a new row in Sheet2.

My only worry is that the macro button will be pushed too often, posting to sheet2 haphazardly, or submitting duplicate entries. Here is a 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 from Sheet1 to be entered on a new row. If the button is pushed with the same 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: 8 hours

And the button is pressed with this information:

Column 1: Job#44123 2: Microsoft 3:Business Cards 4: 2 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 open row.

I hope this makes sense.

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

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

I hope this makes sense.

Eric

Gord,

Is it safe to say that you're a bit stumped on this one? Just wondering if I should keep checking back for an answer or not... I am continuing 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

Automatically populating cells down a row
 

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 Ax:Dx over to Sheet2 after Sheet1
Ax:Dx are filled in.

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

So we first have to have jobnum entered in Sheet1 then B:D 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:D 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



On Thu, 31 May 2012 23:16:50 +0000, erstaples
wrote:


Gord,

Is it safe to say that you're a bit stumped on this one? Just wondering
if I should keep checking back for an answer or not... I am continuing
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

erstaples;1602234 Wrote:
Hi Gord,

Thanks for the reply. I've been giving it some thought, and since I'm
going to be passing this workbook along to users with little knowledge
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 go
about putting this into action. Anytime the macro button is pushed in
Sheet1 it will update everything into a new row in Sheet2.

My only worry is that the macro button will be pushed too often, posting
to sheet2 haphazardly, or submitting duplicate entries. Here is a
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 from
Sheet1 to be entered on a new row. If the button is pushed with the same
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: 8
hours

And the button is pressed with this information:

Column 1: Job#44123 2: Microsoft 3:Business Cards 4: 2
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 open
row.

I hope this makes sense.

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

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

I hope this makes sense.

Eric

Gord,

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

Automatically populating cells down a row
 

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

On Sat, 2 Jun 2012 06:24:04 +0000, erstaples
wrote:


Gord,

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

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

I did a Do Until... Or

What this does is the counter in the second book first looks for the job number. If the counter finds the job number it stops there and any new job specs from the first workbook will update down the row. If the counter doesn't find the job number it goes to the first empty cell and 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 of doing this I'd love to hear it, but I've tested this and it does what I need it to do...

Automatically populating cells down a row
 

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

Gord

On Sun, 3 Jun 2012 05:26:18 +0000, erstaples
wrote:


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

I did a Do Until... Or


Code:
--------------------

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

Loop


ThisWorkbook.Sheets("komori").Range("JobNumber").C opy
Trends.Sheets("sheet1").Range("A" & trends_counter).PasteSpecial xlPasteAll
ThisWorkbook.Sheets("komori").Range("JobSpecs").Co py
Trends.Sheets("sheet1").Range("B" & trends_counter).PasteSpecial xlPasteAll


Trends.Save
Trends.Close


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


What this does is the counter in the second book first looks for the job
number. If the counter finds the job number it stops there and any new
job specs from the first workbook will update down the row. If the
counter doesn't find the job number it goes to the first empty cell and
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 of
doing this I'd love to hear it, but I've tested this and it does what I
need it to do...