Automatically insert new record field when previous one is used.

[Marvlin]

Hi guys

I have been looking into this since 7.00am this morning and besides learning a little more about VBA and macros haven't really solved it.

I've tried searching your forums but the page just kept crashing LOL.

I then tried reading through the pages manually and lost the will to live at about page 8.

Hopefully you guys can help.

I've created a basic example of what I am trying to achieve in the attached.

A standard macro seems to work fine.

I click on the same cell each time (relative references switched on) on the bottom left of the last record and activate the macro.

The macro simply copies the record immediatley above the selected cell and pastes it alongside the selected cell.

However, I want this to be automatic.

I am open to suggestions on how to do this as I am really struggling, having watched you tube videos and read through countless forums and examples I've tried to adapt.

I do now know how to open VBA and edit to a basic level.

I also now know how to create and edit buttons etc.

I just need to be sent down the right path really becuase I am probably going about this totally the wrong way.

I really want to get my head around visual basic so this could be the first rung of the ladder for me.

I think if you look at the attachment is shows better what I am after.

Thanks for any help on this

Martyn

Example.xls

[oeldere]

Why not just copy / past the data (it seems to me, a workable solution).

You have merged cells in your file.

My advice is, do not use merged cells, you get in trouble with it sooner or later.

[sktneer]

Try this......

Please Login or Register  to view this content.

To apply this code --> Right Click on the Sheet Tab --> View Code --> Paste the code given above in the code window --> Close the VBA editor --> Save your workbook as Excel Macro-Enabled Workbook --> Done.

Please find the attached sheet to see if this works as per your requirement. Input a Item No. in B7 to execute the code.

[Marvlin]

removed and reposted

[Marvlin]

Try this......

Please Login or Register  to view this content.

To apply this code --> Right Click on the Sheet Tab --> View Code --> Paste the code given above in the code window --> Close the VBA editor --> Save your workbook as Excel Macro-Enabled Workbook --> Done.

Please find the attached sheet to see if this works as per your requirement. Input a Item No. in B7 to execute the code.

Thanks sktneer. It works perfectly

This just goes to show how little I actually know about Excel.

I understand if not but I really could do with knowing how the code relates to the spreadsheet so I can better understand it and modify it for other uses.

Would you mind breaking down each line for me and explaining how it works?

Something along the lines of:

If Target.Count > 1 Then Exit Sub
(This refers to that and........)

If Target.Column = 2 Then
(This refers to that and........)

Application.EnableEvents = False
(This refers to that and........)

If Target <> "" And Left(Target.Offset(-2, 0), 7) = "PROJECT" Then
(This refers to that and........)

lr = Cells(Rows.Count, 2).End(xlUp).Row
(This refers to that and........)

etc
etc

I understand if not as I don't want to take over your life but I really quite keen to learn the more advanced aspects of Excel now.

[sktneer]

You have given me a very difficult task. It is just like writing a book on excel VBA and let me tell you that I am not very good in writing.
But I will try my best to explain. If it is still unclear to you, better you go through some online tutorials on excel VBA. Just Google each line of code and you will get plenty of material to read.

1. If Target.Count > 1 Then Exit Sub : This line makes sure that if more than one cells are changed at once, the code will not execute.

2. If Target.Column = 2 Then: The code for worksheet change event will execute only if the value of any cell in col. B is changed. 2 is the column index for col. B.

3. Application.EnableEvents = False: During the execution of the code, it may happen that the value of some cells in col. B are changed and as a result the code will re-execute since as per the code when value of any cell in col. B is changed, the code will be triggered. So to stop the re-execution of the code due to change in values in the target range during the code.

4. If Target <> "" And Left(Target.Offset(-2, 0), 7) = "PROJECT" Then: So lets assume that the value of a cell in col. B gets changed, which triggers the code, this line of code checks if value of the target cell is not blank (this also stops code execution if you delete the cell content in the target range) and checks if the first 7 characters form the string in two cell above the target cell is "PROJECT". Take an example. If you change the value of B7, the code will execute and it will first make sure that you have entered a value in B7 and then checks B5 (two cells above the target cell B7) and if it finds that the left 7 characters in B5 are Project then the control is passed to the next line of code. Suppose you change the value of B8, then code will check left 7 characters of B6 and it doesn't find Project as left 7 characters of B6 so the code doesn't do anything.

5. lr = Cells(Rows.Count, 2).End(xlUp).Row: This line of code finds the last used row in col. B. You have to memorize this line of code as it is required in most of the VBA code. For more detail, Google Cells(Rows.Count, 2).End(xlUp).Row to find more material on this.

Sorry If I have not explained it well.

[Marvlin]

You have given me a very difficult task. It is just like writing a book on excel VBA and let me tell you that I am not very good in writing.
But I will try my best to explain. If it is still unclear to you, better you go through some online tutorials on excel VBA. Just Google each line of code and you will get plenty of material to read.

1. If Target.Count > 1 Then Exit Sub : This line makes sure that if more than one cells are changed at once, the code will not execute.

2. If Target.Column = 2 Then: The code for worksheet change event will execute only if the value of any cell in col. B is changed. 2 is the column index for col. B.

3. Application.EnableEvents = False: During the execution of the code, it may happen that the value of some cells in col. B are changed and as a result the code will re-execute since as per the code when value of any cell in col. B is changed, the code will be triggered. So to stop the re-execution of the code due to change in values in the target range during the code.

4. If Target <> "" And Left(Target.Offset(-2, 0), 7) = "PROJECT" Then: So lets assume that the value of a cell in col. B gets changed, which triggers the code, this line of code checks if value of the target cell is not blank (this also stops code execution if you delete the cell content in the target range) and checks if the first 7 characters form the string in two cell above the target cell is "PROJECT". Take an example. If you change the value of B7, the code will execute and it will first make sure that you have entered a value in B7 and then checks B5 (two cells above the target cell B7) and if it finds that the left 7 characters in B5 are Project then the control is passed to the next line of code. Suppose you change the value of B8, then code will check left 7 characters of B6 and it doesn't find Project as left 7 characters of B6 so the code doesn't do anything.

5. lr = Cells(Rows.Count, 2).End(xlUp).Row: This line of code finds the last used row in col. B. You have to memorize this line of code as it is required in most of the VBA code. For more detail, Google Cells(Rows.Count, 2).End(xlUp).Row to find more material on this.

Sorry If I have not explained it well.

On the contrary. You explained that very well. It is certainly enough to get me started.

Many thanks for taking the time to help

Regards

Martyn

[sktneer]

You're welcome. Glad I could help.
You may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks.