Vba macro to multiply and re arrrange data

HI all

the attached file


contains 2 tabs

1.R_Jetwash
2.R_Jetwash_table


1.R_Jetwash contains two areas where the user populates the data A to N and Q TO AB ( (please take into account that the number of rows can increase substantially)

A TO N are volumes(sales) and Q to AB are prices

what the macro has to do is to calculate the revenue (volume *price) in each site per month

For example Revenue in Jan 18 in site 32 =

4000 ( tab=R_Jetwash cell= c12)*0.83 ( tab=R_Jetwash cell= Q12) =332


2. R_Jetwash_table

this tab will show the result of each site in each month




(the macro below is very similar, the difference is that has a tab called volumes, that tab was another factor to multiply in the above calculation. that tab is no longer in use in the above example
perhaps you can use it as a reference. if it is confusing you please ignore it)

Sub MakeTable()
    Dim vol     As Variant
    Dim con     As Variant
    Dim tbl     As Variant
    Dim i       As Long
    Dim j       As Long
    Dim iRow    As Long
    Dim dic     As Object


    With ThisWorkbook
        With .Worksheets("VOLUMES")
            vol = .Range("A2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row, 5)
        End With
        With .Worksheets("R_Jetwash")
            con = .Range("A1").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row, 28)
        End With
    End With
    
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(vol)
        dic(vol(i, 2) & "|" & vol(i, 4)) = vol(i, 5)
    Next
    
    ReDim tbl(1 To UBound(con) * 12, 1 To 4)
    For i = 1 To UBound(con) - 1
        For j = 3 To 14
            iRow = (i - 1) * 12 + j - 2
            tbl(iRow, 1) = con(i + 1, 1)
            tbl(iRow, 2) = con(1, j)
            tbl(iRow, 3) = con(i + 1, 2)
            tbl(iRow, 4) = dic(con(i + 1, 1) & "|" & con(1, j)) * con(i + 1, j) * con(i + 1, j + 14)
        Next
    Next


    Application.ScreenUpdating = False
    With ThisWorkbook
        With .Worksheets("R_Jetwash_table")
            .Cells.Clear
            .Range("A1:D1") = Array("Site", "Month", "Jetwash", "Amount")
            .Range("A2").Resize(UBound(tbl, 1), UBound(tbl, 2)) = tbl
            .Columns("B:B").NumberFormat = "mmm-yy"
            .Columns("D:D").NumberFormat = "_-* #,##0_  -;-* #,##0_  -;_-* ""-""?_-;_-@_-"
            .Columns("A:D").EntireColumn.AutoFit
        End With
    End With
End Sub

Attach your workbook directly here, please.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

i tried to attach a file of 2.70 mb xlsb it keeps failing. i have corrected the link it should work ok now

Cut the workbook down to just what we need to see to help you, then attach it again. A small, sample dataset will do. Many members will not access files via file-sharing sites.

Try saving as .xlsx or .xlsm and attach.

It's working fine for me - it's either something your end or you are not following the instructions for upload correctly.

Hi,

The workbook you have linked to on your first post has 39 worksheets and many 21 modules. The sheets are hidden. This is why the file is so big.

Hi Antonio,

It looks to me like you want a Pivot Table, that I've done on your second sheet, to sum the dollars. No VBA needed! Time to learn about Pivot Tables? See if my work makes sense to you and search the new for Pivot Table examples if needed.

Car Wash Pivot Date Columns.xlsm

Hi Antonia,

You are correct. I completely misunderstood your question. I think that giving the code from Jindon(?) with Scripting.Dictionary and a tab that wasn't there completely threw me off. See if what I've done in the attached is the steps you would go through to get your answer. If we can see the manual steps to do a problem, we can normally write a macro that does those manual steps. I've done two manual steps in the attached. I created a third table on the first sheet with a simple formula in yellow. Then I copies this third table and copied it using Values and Transpose. Will these manual steps answer your question?

Antonio Jetwash product of tables.xlsm

I'm so glad you found an answer for this one. It had me completely stumped. I really didn't want to read the code in your first post and figure it out after it really didn't work on your workbook. Being "close" to what you wanted was a real problem.

If you have an answer to this problem, could you edit the first post and mark the thread as "Solved".

Thanks