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

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

Originally Posted by Gord Dibben[_2_]

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

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

Originally Posted by erstaples

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

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



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.

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

    trends_counter = 7
    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 + 1
    
    Loop

    
    ThisWorkbook.Sheets("komori").Range("JobNumber").Copy
    Trends.Sheets("sheet1").Range("A" & trends_counter).PasteSpecial xlPasteAll
       ThisWorkbook.Sheets("komori").Range("JobSpecs").Copy
    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...

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...