Posts by KjBox

    Your welcome.

    Note that if you go with the divider Column method the that column can be hidden in the data. You would need to add a bit of code right at the end to hide it again in the Changes sheet.

    1. .Columns(lCol).Resize(, UBound(Hdrs) + 1).ColumnWidth = 12
    2. .Columns(lCol - 1).Hidden = 1
    3. End With
    4. End Sub

    Also delete the "On Error Resume Next" that you added.

    Here is the code with comments added, I hope it will help you follow and understand what each part does!

    All you need do is add 1 to the value of i

    1. With Original
    2. For i = 2 To .UsedRange.Columns.Count
    3. If .Cells(2, i).Value = "xxxx" Then
    4. lCol = i + 1: Exit For
    5. End If
    6. Next
    7. End With

    Note I changed UsedRange.Rows to UsedRange.Columns.

    That was an error by me originally, .Rows just happened to work with the sample file but might not with your actual file.

    It was overwriting because of the protection of the new sheets, my fault, when the code adds a new sheet it gets protected, but I forgot to include UserInterfaceOnly = True. That is now fixed.

    I am attaching a workbook where I added 3 new sheets and added data to each. The Site Totals Table updates correctly, but, it does throw up a problem.

    You will see that I added data for 2 different Rooms for Science Block Level G, they have different Difficulty Factors (1 & 3). Since there is just 1 "Difficulty" entry for each row of the Site Totals Table, the code only records the last entered, or any data update, Difficulty Factor (in this case 3 for tiling the Chemistry Lab). Not sure of the best way to overcome this issue, maybe an average of all entered Difficulty Factors for the same Block/Area & Level? Alternatively a new row for every room on each level?

    I have also added code that will sort the Site Totals Table by the first column, so all Block/Areas are grouped no matter when they are entered. Also code to sort and group the Sheet Tabs.

    Here is an illustration of why Excel Built-in Tables (called ListObjects by VBA) are easier to work with. This code snippet

    1. If IsError(Application.Match(sShtNm, .ListColumns(1).Range, 0)) Then
    2. If .ListRows(1).Range.Columns(1) <> "" Then .ListRows.Add , 1
    3. With .ListRows(.ListRows.Count).Range
    4. .Columns(1) = sShtNm
    5. .Columns(2).Resize(, UBound(y)) = y
    6. End With
    7. Else
    8. lRow = Application.Match(sShtNm, .ListColumns(1).Range, 0) - 1
    9. .ListRows(lRow).Range.Columns(2).Resize(, UBound(y)) = y
    10. End If

    Checks if the Block/Area & Level are already in the Table, if not then a new Table Row is added (only if the Table already contains some data, if not the the first Table Row is used) and the data placed in that new Table Row, otherwise the correct Table Row gets identified and the data in that row is updated. No need to check for "the next empty row", also any formulas in the Table are automatically copied down to the newly added row.

    I am attaching 2 files, one empty with the modified code, the other with 3 extra sheets with data added so you can see the issue with Difficulty Factor.

    You have not noticed the parts of the formula that are in parentheses!

    ReDim z(1 To UBound(x, 1) - 1, 1 To UBound(x, 2) * 4 - ((lCol - 1) * 3))

    Ubound(x,1) = 20, UBound(x,2) = 17 and lCol (which is the Column where monitoring needs to start) = 8


    z(1 To 8 - 1, 1 To 17 * 4 - ((8 - 1) * 3)) equates to z(1 to 7, 1 to 68 - (7 * 3)) which, in turn, = z(1 To 7, 1 To 68 - 21) = z( 1 To 7, 1 To 47)

    Hdrs is the ReDimmed 1 to UBound(z, 2), which is 1 to 47

    Rename the file you are testing with and send it to me I can then see if something weird happened when Uploading and/or Downloading.

    Try the attached.

    Click the button on the Transaction sheet.

    Code assigned to the button is:


    You had an error in your sample file, the average Buy for Share 1 should be 7,752 not 7,749

    Any Short Sell will have the Inventory in Red text, I changed Share7 Sell to 4,000 to illustrate that.