0

Update:

Every time the tab is opened it automatically generates the an order list.

As far as I can tell, there is one bug in the code:

  1. The first line on "Full List" (in this case C8:P8) is always copied and inserted into the new array no matter what is there.

I made it copy a blank row, but the paste into the Order tab should only list order items. I tried pasting all items from the results array aside from the first row but to no success. Oh and thank you so much @David-Zemens for all the assistance on this. You know what you are doing and it shows :)

Private Sub Worksheet_Activate()
    Dim wsList As Worksheet, wsOrder As Worksheet
    Dim rng As Range

    Set wsList = Worksheets("Full List")
    Set wsOrder = Worksheets("Order")
    wsList.Unprotect

    'Clear Columns B, C, & D below row 3 in the Order sheet
    wsOrder.Range("B3:D" & Rows.Count).ClearContents

    'Set Range to include all columns from C to P starting with row 8
    Set rng = wsList.Range("C8:P" & GetLastRow(wsList, 3))
    wsList.AutoFilterMode = False

    'filter so that only numeric values in column P are showing
    rng.AutoFilter Field:=14, Criteria1:=">0", _
        Operator:=xlAnd

    'Copy column C (in wsList) to column B (in wsOrder)
    rng.Columns(1).SpecialCells(xlCellTypeVisible).Copy
    wsOrder.Range("B2").PasteSpecial xlPasteValues
    'Copy column D (in wsList) to column C (in wsOrder)
    rng.Columns(2).SpecialCells(xlCellTypeVisible).Copy
    wsOrder.Range("C2").PasteSpecial xlPasteValues
    'Copy column P (in wsList) to column D (in wsOrder)
    rng.Columns(14).SpecialCells(xlCellTypeVisible).Copy
    wsOrder.Range("D2").PasteSpecial xlPasteValues

    wsList.AutoFilterMode = False
    wsList.Protect

End Sub

Function GetLastRow(sh As Worksheet, column As Long)
'  Function will return the last effective cell in specified column on worksheet
'      sh:      Worskheet object i.e., Worksheets("Sheet1")
'      column:  long/integer value of column, i.e., column A = 1, column N = 12, etc.
'  Modified from :
'  http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba
'
With sh
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastRow = .Cells.Find(What:="*", _
                      After:=.Cells(1, column), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastRow = 1
    End If
End With

GetLastRow = lastRow
End Function

Original Post:

I was hoping that someone might be able to assist.

I created an excel program that allows me to see what the stock levels are and to compare it against counted or expected values. This allows me to see how many items we need to order.

What I was hoping to do was generate (on another tab) a separate list that only shows me the Product Code, Product Name, and the Quantity to order.

The main tab where everything is counted is named "Full List" aka Sheet06.

There are multiple rows, but the ones that I am interested in start at row 9 and are on columns C (Product Code), D (Product Name), & P (How many to Order) and there are over a thousand rows of product with some blank rows used to try to make it easy to read (if needed these lines can be filled with something or use column Q to have a TRUE/FALSE "=ISNUMBER(P9)" equation)

On Column P, if an order is not needed there is a blank space (="") or text

  |A|B|Product Code|Name     |E|F|G|H|I|J|K|L|M|N|O|Order
9 | | |00002       |something| | | | | | | | | | | |""
10| | |345663      |something| | | | | | | | | | | |3
11| | |6534214     |something| | | | | | | | | | | |15
12| | |            |         | | | |(Blank row)| | | 
13| | |24435224    |something| | | | | | | | | | | |DONT ORDER
14| | |8784347     |something| | | | | | | | | | | |8
15| | |357823      |something| | | | | | | | | | | |""

What I have been trying to create (and failing) is another tab called "Order" aka Sheet07

It is supposed to copy over the Product Code, Name, and how many to order if and only if there is a number on row P on the other tab. Also this would have to copy over the value generated for the order (18, 4, etc) not the formula (=E9-F9+G9....).

 |A|Product Code|Name     |Order
4| |345663      |something|3
5| |6534214     |something|15
6| |8784347     |something|8

I have been struggling with this for quite a while now and I have reached a mental roadblock.

Any help would be appreciated :)

user5656417
  • 11
  • 3
  • *I have been struggling with this for quite a while now* <-- What have you tried so far? – David Zemens Dec 08 '15 at 22:16
  • What have you tried and do you have any ideas about what could/might work that you haven't tried? – Taelsin Dec 08 '15 at 22:24
  • 1
    You could do this with an Advanced Filter. No vba required. – Scott Craner Dec 08 '15 at 22:32
  • I tried this. It works but is quite cumbersome and still has code that would need to be removed before sent. Also it needs to be simple for others to use when I am away. – user5656417 Dec 08 '15 at 23:32
  • You do not need any code to use Advanced Filter, but you do need a little know how, and it is not the most intuitive. – Scott Craner Dec 08 '15 at 23:34
  • I tried my own code and many other examples from this website and attempted to modify them to work. The code is now gone (excel crashed and the restore was corrupt). I can list the links to the 12 VBA scripts if that will help. – user5656417 Dec 08 '15 at 23:36
  • @ScottCraner I'm sorry I was saying that it shows everything correctly, but like you pointed out it isn't simple (which it needs to be for others) and if you try to copy and past it into something else, it still leave the formula =E9-F9+G9.... – user5656417 Dec 08 '15 at 23:43
  • With Advanced filter there is an option to copy to another page automatically and the formulas do not come across. – Scott Craner Dec 08 '15 at 23:48
  • @ScottCraner This is what I first attempted, I'm sorry to say I was unsuccessful in making it copy over only three columns. – user5656417 Dec 09 '15 at 01:55
  • See [this](http://excelsemipro.com/2011/03/copy-data-to-another-worksheet-with-advanced-filter/). It is a good reference. – Scott Craner Dec 09 '15 at 02:00
  • Thanks for that, Do you have an example that would allow you to only copy over specific columns if the value is true? – user5656417 Dec 09 '15 at 02:24
  • I revised my answer based on your updated code, and it should avoid the second bug. About the first bug, is C8:P8 a row that contains labels/headers? Or is it data? – David Zemens Dec 10 '15 at 14:42
  • Had to make a minor correction as the data is on column 14 'Copy column P to column D rng.Columns(14).SpecialCells(xlCellTypeVisible).Copy' Well ideally it should be C9:P9 as that is the beginning of the list. Currently that is a blank line to avoid confusion. – user5656417 Dec 11 '15 at 08:14

1 Answers1

0

There are several ways you can do this, but one would be using AutoFilter. Assuming both worksheets exist. I've made some revisions based on the additional details you've provided, and implement a custom function GetLastRow based on the answer here.

This should avoid the second bug, which is copying some additional blank rows.

Private Sub Worksheet_Activate()
    Dim wsFull As Worksheet, wsOrder As Worksheet
    Dim rng As Range

    Set wsFull = Worksheets("Full List")
    Set wsOrder = Worksheets("Order")

    'Clear Columns B, C, & D below row 3 in the Order sheet
    wsOrder.Range("B3:D" & Rows.Count).ClearContents

    'Set Range to include all columns from C to P starting with row 8
    Set rng = wsFull.Range("C8:P" & GetLastRow(wsFull, 3))
    wsFull.AutoFilterMode = False

    'filter so that only numeric values in column P are showing
    rng.AutoFilter Field:=14, Criteria1:=">0", _
        Operator:=xlAnd

    'Copy column C to column B
    rng.Columns(1).SpecialCells(xlCellTypeVisible).Copy
    wsOrder.Range("B2").PasteSpecial xlPasteValues
    'Copy column D to column C
    rng.Columns(2).SpecialCells(xlCellTypeVisible).Copy
    wsOrder.Range("C2").PasteSpecial xlPasteValues
    'Copy column P to column D
    rng.Columns(12).SpecialCells(xlCellTypeVisible).Copy
    wsOrder.Range("D2").PasteSpecial xlPasteValues

    wsFull.AutoFilterMode = False
End Sub`


Function GetLastRow(sh As Worksheet, column As Long)
'  Function will return the last effective cell in specified column on worksheet
'      sh:      Worskheet object i.e., Worksheets("Sheet1")
'      column:  long/integer value of column, i.e., column A = 1, column N = 12, etc.
'  Modified from :
'  https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba
'
With sh
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastRow = .Cells.Find(What:="*", _
                      After:=.Cells(1, column), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastRow = 1
    End If
End With

GetLastRow = lastRow
End Function
Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • I get error 424 trying to run this macro. Apparently there is an error with the line ws.AutoFilter = False – user5656417 Dec 08 '15 at 23:37
  • @user5656417 I made a revision to my answer, I think it should be `wsFull.AutoFilterMode = False` (`ws` isn't a valid object, and even if it was, my original answer had the wrong method name). Try that and see how it works. – David Zemens Dec 09 '15 at 01:16
  • Ok that is almost exactly it! I am unsure how to copy only those three cells C, D & P from each correct row. It also needs to start at row 9, currently it starts at the beginning and for some unknown reason those cells and text are also being copied. Thank you so much, each time I tried my code was a lot longer than this and unfortunately never worked. – user5656417 Dec 09 '15 at 01:53
  • Sorry you're looking for someone else to solve all your problems and deliver turnkey code, for free. That's really not the spirit of this site and I wouldn't really be helping anyone if I did that for you. Good luck. – David Zemens Dec 09 '15 at 02:02
  • Oops sorry first time using this site. If I came a across as ungrateful or needy I do apologize. I have been trying this for a while and shocked how quickly someone else came up with the solution, not sure with the code how to start from a specific row or how to only copy specific cells. I understand if you are hesitant to spell everything out. Do you have any other resources you could point me to? Thanks :) – user5656417 Dec 09 '15 at 02:20
  • As I mentioned in the OP, the Macro Recorder is a good enough place to start. That will show you the *literal* way to implement your mouse-clicks and keystrokes, in to code. Then it is up to you to make that more *flexible*. Learn how to use [debugging techniques](http://www.cpearson.com/excel/DebuggingVBA.aspx) (seriously I can't stress this enough), this will make it easier for you to do trial-and-error, and to *see* and understand what's happening in the code. – David Zemens Dec 09 '15 at 03:14
  • For this specific problem, use the macro recorder to applyt he autofilter as I have done. Then instead of copying the entire range, just copy column C, then go to the next sheet, right-click and past-special "Values". Repeate for Columns D and P. Those bits of code can then be integrated or modified along with the methods I used above. If you do that initial work and then update your Question with your new code from Macro Recorder, I'll give it another look and make some suggestions and explanations for you. – David Zemens Dec 09 '15 at 03:16
  • Cant post everything, But here is almost all the solution. Currently it records two cells before row 9 and I believe one after. Any ideas on how to start from row 9? Set rng = wsFull.Range("C:P") 'Range from Begining to End wsFull.AutoFilterMode = False 'filter so that only numeric values in column P are showing rng.AutoFilter Field:=14, Criteria1:=">0", _ Operator:=xlAnd rng.Columns("A:B").Copy wsOrder.Range("B1").PasteSpecial xlPasteValues rng.Columns("N:N").Copy wsOrder.Range("D1").PasteSpecial xlPasteValues wsFull.AutoFilterMode = False – user5656417 Dec 09 '15 at 04:23
  • You can *edit* your original question. There, the code is readable, as it can be formatted properly, and also not subject to character count limitations :) Try tinkering with `set rng = wsFull.Range("C9:P" & Rows.Count)` Also, when you're copying, us the `rng.Columns("N:N").SpecialCells(xlCellTypeVisible).Copy` to copy only the *visible* cells (those not excluded by the AutoFilter). – David Zemens Dec 09 '15 at 04:43
  • Ah thanks for the rows.count, that solved the issue. But I am still getting one error (well two really). As the code selects the very first row no matter what is there. Not sure why (the other error is minor as it copies all rows after the last value, not bad really). I can make it so it can copy a blank row if that is needed. But the paste should only list order items. I tried the trick you showed to try and paste "A2:B" but to no success. btw you have been so amazing to get me to this point and help me with this. Even if you don't know how to resolve this thanks so much for the assistance :) – user5656417 Dec 09 '15 at 13:23
  • Update your Question above, and I'll try to take another look when I'm in the office later. – David Zemens Dec 09 '15 at 13:31
  • I used up the text limit with the previous comment so I have placed the code below :) – user5656417 Dec 09 '15 at 23:12
  • Just replace the contents of your original question with the called that you're currently trying to use – David Zemens Dec 09 '15 at 23:14
  • Ah ok, sorry I misunderstood :) – user5656417 Dec 09 '15 at 23:43