Auto insert a row with info populated from the row above?

[JDobbsy1987]

Hi All,

I am sorry if the title isn't the best but found it hard to think of somthing for this, i will edit if moderators want me too.

Basically what i want to be able to do is have a stock control sheet that tells me how much of 1 item we have in stock which is easy, the hard part comes when this stock is sold as this stock can be sold to a number of different customers e.g.

We have 10,000 X Pens

Customer 1 buys 1,000
Customer 2 buys 6,000
Customer 3 buys 3,000

i need a way of saying that we have 10,000 pens but if we sold 1,000 then we can click a button (macro maybe) and that will automatically see that we have 10,000 for that line and we have only sold 1,000 so we need a line inserting saying that we have 9,000 remaing and so on.... The sheet needs to record who these goes to though, that the reason i was thinking of adding a row

If this doesnt make sense then i am happy to answer your questions?

i can upload an example excel sheet if required if someone can explain how i do this?

Regards,
JDobbsy1987

[Palmetto]

i can upload an example excel sheet if required if someone can explain how i do this?

Yes, do so. It eliminates a lot of questions, shows us your structure and gets you a precise answer more quickly. Your workbook should manually show the desired outcome and not contain any sensitive data (representative dummy data OK).

Please explain your needs more clearly.

[JDobbsy1987]

Yes, do so. It eliminates a lot of questions, shows us your structure and gets you a precise answer more quickly. Your workbook should manually show the desired outcome and not contain any sensitive data (representative dummy data OK).

Please explain your needs more clearly.

Thank you

Here is the example file, i hope this will help.
I have also added additional info on the excel sheet.

[JDobbsy1987]

Hi,

I understand that other people have issues too but i was wondering if someone can tell me if this is possible, it would be ACE!! if someone can tell me how to do this...?

Regards,
JDobbsy1987

[Palmetto]

I have to ask, does the sample workbook actually reflect the structure of your real workbook?

If so . . .
FWIW, you are better served by maintaining two separate worksheets. One maintains stock received and the other maintaints stock sold. Then you can easily calculate the ongoing balance of items.

[JDobbsy1987]

I have to ask, does the sample workbook actually reflect the structure of your real workbook?

If so . . .
FWIW, you are better served by maintaining two separate worksheets. One maintains stock received and the other maintaints stock sold. Then you can easily calculate the ongoing balance of items.

This is a reflection of the workbook but on a smaller scale with dummy data, i can upload our full sheet with the data deleted but i was thinking that the main thing would be creating the button to do the job i am after (i was thinking that the extra data would just be added to the code....?)

i have looked at having 2 sheets but i just thought it would be nice to have it calculated on one sheet if someone knew how to do it.

Thanks for your reply

[Palmetto]

See if this is what you intended.
After entering a value into the "Qty Sold" cells and selecting the adjacent cell in column_F a command button will appear, using this code in the worksheet_SelectionChange event:

Option Explicit
Dim lrow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row

    If Not Intersect(Target, Range("F2:F" & lrow)) Is Nothing Then

            With Me
                .CommandButton1.Visible = True
                .CommandButton1.Top = Range("F" & Target.Row).Top
                .CommandButton1.Left = Range("F" & Target.Row).Left
            End With

    End If
End Sub

When the button is clicked, it runs this code, which inserts a line below the recent entry and places a formula into the Qty cell (column-B), which subtracts amount sold from stock on hand.

Private Sub CommandButton1_Click()

    If Not Intersect(ActiveCell, Range("F2:F" & lrow)) Is Nothing Then

        With Me
            .Range("A" & ActiveCell.Row).Offset(1, 0).EntireRow.Insert shift:=xlDown
            .Cells(ActiveCell.Row, 1).Offset(1, 0).Value = Cells(ActiveCell.Row, 1).Value
            .Cells(ActiveCell.Row, 2).Offset(1, 0).FormulaR1C1 = "=R[-1]C-R[-1]C[3]"
            .CommandButton1.Visible = False
        End With

    End If

End Sub

[JDobbsy1987]

See if this is what you intended.
After entering a value into the "Qty Sold" cells and selecting the adjacent cell in column_F a command button will appear, using this code in the worksheet_SelectionChange event:

Option Explicit
Dim lrow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row

    If Not Intersect(Target, Range("F2:F" & lrow)) Is Nothing Then

            With Me
                .CommandButton1.Visible = True
                .CommandButton1.Top = Range("F" & Target.Row).Top
                .CommandButton1.Left = Range("F" & Target.Row).Left
            End With

    End If
End Sub

When the button is clicked, it runs this code, which inserts a line below the recent entry and places a formula into the Qty cell (column-B), which subtracts amount sold from stock on hand.

Private Sub CommandButton1_Click()

    If Not Intersect(ActiveCell, Range("F2:F" & lrow)) Is Nothing Then

        With Me
            .Range("A" & ActiveCell.Row).Offset(1, 0).EntireRow.Insert shift:=xlDown
            .Cells(ActiveCell.Row, 1).Offset(1, 0).Value = Cells(ActiveCell.Row, 1).Value
            .Cells(ActiveCell.Row, 2).Offset(1, 0).FormulaR1C1 = "=R[-1]C-R[-1]C[3]"
            .CommandButton1.Visible = False
        End With

    End If

End Sub

Hi Palmetto,

This is amazing, it is exactly what i was after, although i thought i had set this up correctly by changing your code grid references to fit the fields on the actual sheet it turns out it doesnt work, so i am not doing it right.

I have attached the actual sheet and basically i need what you have already done on this sheet (a button appearing in the 'Generate column' to insert a row below to carry on the stock QTY as it is sold)

Can you please help, this is the code i tried to use... it might be something tiny that i am missing or i might be totally wrong?

Option Explicit

Dim lrow As Long

Private Sub CommandButton1_Click()

    If Not Intersect(ActiveCell, Range("M4:M" & lrow)) Is Nothing Then

        With Me
            .Range("D" & ActiveCell.Row).Offset(1, 0).EntireRow.Insert shift:=xlDown
            .Cells(ActiveCell.Row, 1).Offset(1, 0).Value = Cells(ActiveCell.Row, 1).Value
            .Cells(ActiveCell.Row, 2).Offset(1, 0).FormulaR1C1 = "=R[-1]C-R[-1]C[3]"
            .CommandButton1.Visible = False
        End With

    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row

    If Not Intersect(Target, Range("M2:M" & lrow)) Is Nothing Then

            With Me
                .CommandButton1.Visible = True
                .CommandButton1.Top = Range("M" & Target.Row).Top
                .CommandButton1.Left = Range("M" & Target.Row).Left
            End With

    End If

End Sub

Once again thank you for your help

[JDobbsy1987]

Im sorry about the sheet being different, i dont mean to waste your time, you have done me a great favour.

One thing i would like to know is how to tell the code to pull other info into the auto inserted rows?
as it stands it is set up to auto insert a row and pull the QTY IN, MAKE/MODEL and do the formula for the QTY LEFT TO BE SOLD. how do i cange the code so that it also pulls the BOUGHT FROM and the COST IN down with it?

Regards,
Jamie

[JDobbsy1987]

Sorry from what i can gather, there has to be a date for the generate button to work but because i dont want to pull the date (column A) down, how do i change that to something like column C?

Regards,
Jamie

[Palmetto]

how do i cange the code so that it also pulls the BOUGHT FROM and the COST IN down with it?

Study the pattern of this code. I've re-arranged the lines to make it the order more clear. Replace the relevant section of button code with this and add to / modify per your needs.

With Me
            .Range("D" & ActiveCell.Row).Offset(1, 0).EntireRow.Insert shift:=xlDown
            .Cells(ActiveCell.Row, 2).Offset(1, 0).Value = Cells(ActiveCell.Row, 10).Value
            .Cells(ActiveCell.Row, 3).Offset(1, 0).Value = Cells(ActiveCell.Row, 3).Value
            .Cells(ActiveCell.Row, 4).Offset(1, 0).Value = Cells(ActiveCell.Row, 4).Value
            .Cells(ActiveCell.Row, 5).Offset(1, 0).Value = Cells(ActiveCell.Row, 5).Value
            .Cells(ActiveCell.Row, 10).Offset(1, 0).FormulaR1C1 = "=RC[-8]-RC[-2]"
            .CommandButton1.Visible = False
End With

Sorry from what i can gather, there has to be a date for the generate button to work but because i dont want to pull the date (column A) down, how do i change that to something like column C?

This is not clear to me. Are you say there must be a valid date before running "generate"? Please clarify what you mean. At the moment, there is no check for a date entry, so code will run.

[JDobbsy1987]

This is not clear to me. Are you say there must be a valid date before running "generate"? Please clarify what you mean. At the moment, there is no check for a date entry, so code will run.

row 4 seens to work fine but when you go down the sheet you fill find that you can fill anything in apart from column A and either the generate button doesnt show or it does show but when clicked does nothing.

i have also found that when it does work (row 4) after i have clicked generate and a new row is inserted the line it generates doesnt let you carry on generating lines.

its a little hard to explain in detail so i have attached the sheet again with added info
~~ row 4 is good and generated line 5.... but line 5 still has stock left so i fill the info to sell some more but when i click generate nothing happens

~~row 6 was fine but i had to have the date in for it to work?... then i clicked generate and it inserted row 7 but when it came to clicking generate the generate button doesnt show (if i put a date in {i dont want to put a date in every line, just the first line for each batch of stock} then the generate button does show but does nothing when clicked).

i really hope the above and the attached sheet will make sense

Regards,
Jamie

p.s once again thaks for this

[Palmetto]

I assumed, mistakenly it seems, that column-A would always contain a date. The line of code below looks to column-A to determine the last used row. The value in red (column 1, which equates to "A", needs to be changed to a column that will always have data - possibly column 2 ("B"). If so, change the "1" to "2".

Change the value to what ever column you deem appropriate to use in determining the last used row in the table.

lrow = Cells(Rows.Count, 1).End(xlUp).Row

[JDobbsy1987]

I assumed, mistakenly it seems, that column-A would always contain a date. The line of code below looks to column-A to determine the last used row. The value in red (column 1, which equates to "A", needs to be changed to a column that will always have data - possibly column 2 ("B"). If so, change the "1" to "2".

Change the value to what ever column you deem appropriate to use in determining the last used row in the table.

lrow = Cells(Rows.Count, 1).End(xlUp).Row

I will have a look at changeing that code...

Thank you very much indeed, you are a legend

[JDobbsy1987]

Just to let you know, the sheet it perfect, it is exactly the way i wanted it

Thank you for creating it and posting the amended codes for the changes i wanted

Hopefully i can take this code, work with it and teach myself how to use it for other things
Can anyone tell what this code is called (such as VBA, HTML?) so that i can try to find a tutorial?

Regards,
Jamie

[Palmetto]

VBA for Excel. Google: Excel VBA tutorials

[JDobbsy1987]

once again... thank you Palmetto