Excel Macro to extract rows, dependent on previous days value and at 30 items plus a complete category?

Question

I have an excel file, which initially imports stock data from our cloud based accounting program through .iqy web query.

The column headings are:

A1= Quantity B1= Item C1= Description D1= Bin Code

Now I have created a macro which;

1. Referesh's the data

    Range("A1").QueryTable.Refresh False
   

2. Delete's all zero stock items

    Dim intRow
    Dim intLastRow
    intLastRow = Range("A65536").End(xlUp).Row
    For intRow = intLastRow To 1 Step -1
    Rows(intRow).Select
    If Cells(intRow, 1).Value = 0 Or Cells(intRow, 1) = "" Then
    Cells(intRow, 1).Select
    Selection.EntireRow.Delete
    End If
    Next intRow
   

3. Auto Sort by Bin Code

    Range("A1:D1").Select
    Selection.AutoFilter
    Range("A2").Select
    Range("A1:D1668").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
   

4. Save the Master list

    Dim sFileName As String, sPath As String
    sPath = "C:\stock\ms\Master List " 
    sFileName = Format(Now(), "dd'mm'yy")
    ActiveWorkbook.SaveAs (sPath & sFileName)
   

Now this is the tricky bit,

At least 30 items a day need to be checked, however a bin can not be incomplete! So once 30 items have been selected the script needs to check to see if the next item is in the same bin as the 30th item, and include this in the extraction. So lets say item 30 is in bin 10A2, and also item 31, 32, 33, 34, so all in all 34 items (rows) need to be extracted into a new workbook and saved.

This process must start from the previous days sample, so the mechanics should go like this:

1. look in c\stock\sl\Sample List -1 dd'mm'yy sample list for -1 day, look at the last item bin number, say 10A1,

2. take the next rows bin number, 10A2,

3. from the first row which has 10A2, select 30 rows,

4. Continue till the bin number changes.

5. save that file as Sample List dd'mm'yy in c\stock\sl\

6. email Sample List dd'mm'yy to NNN@NNN.com

This should be able to repeat. Also on Saturday and Sunday the company is not open, so on mondays it should look back on friday, and so forth, also accounting for public holidays.

Any help with this would be a life saver? I don't mind if you want to change the file names so that this issue with holidays can be addressed. However, somewhere a time stamp needs to be placed for the files.

Answer 1

You might want to check out the Dictionary object, it would probably help in this task. If you have any questions along the way ask another question. Not sure if someone else would want to give you a more thorough answer to this question.

Your project might be big enough that you would want to work with classes too.

Answer 2

Please avoid every Select in your code.

For instance,

Range("A1:D1").Select
Selection.AutoFilter

can be replaced by:

Range("A1:D1").AutoFilter