Trying to create an order sheet

Solved/Closed
JesterGrafix Posts 2 Registration date Saturday August 27, 2016 Status Member Last seen August 28, 2016 - Aug 27, 2016 at 12:07 PM
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Aug 29, 2016 at 04:03 AM
Hello,
I am trying to create an order sheet in Excel. I have an inventory list with various information. One of the cells is where I input how may of that product I will need for this order. I would like, if possible, that when I enter an amount, all of the cells, including the price, for that line be moved to another sheet that will have the same header row as my inventory sheet. Each item/row will need to copy to the next available row. I have attached an example of what my sheet looks like. My order sheet will need to look identical except that it will have something in the amount needed column and will only show things with the amount to order in that cell. Hopefully, this is possible :) Thanks so much for taking the time to read this!!





2 responses

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 27, 2016 at 07:52 PM
Hello JesterGrafix,

Your post is oddly similar to the following:-

https://ccm.net/forum/affich-919767-creating-an-order

so it may be worth having a look at it although the other Poster has multiple sheets.

Anyway, a Worksheet_Change event may do the trick for you also , so, the following code placed in the Inventory sheet module should work :-

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

        If Target.Value <> "" Then
        Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        End If
  
Sheet2.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Now you can work away in the Inventory sheet and each time that you place a value in any cell in Column G (make sure that this is your last entry per row) and move to the next cell, the relevant row of data will be transferred to the Order sheet.

To implement the code, right click on the Inventory sheet tab and from the menu that appears, select "view code". In the big white field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.
1
JesterGrafix Posts 2 Registration date Saturday August 27, 2016 Status Member Last seen August 28, 2016
Aug 28, 2016 at 02:57 PM
Thank you so much! This solved my issue with ease and made my job so much easier!!!!
0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Aug 29, 2016 at 04:03 AM
Hello JesterGrafix,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.
0