• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Weighted Average with Criteria Using VBA

Hi Vletm,

You asked:

1. Why have to have ~500 sheets?

Answer: Because there are 500 products (one worksheet for each product).

2. How many sheets can You see in one time? (...If one sheet, then why 500 sheets? )

Answer: I wanted all the products in one spreadsheet only, but my boss ordered it to be a spreadsheet for each product.

3. If You import that database - no! - why?What would happen next year? Every year own 500-sheets ... I hope no!

Answer: Because in the company data file, the columns I use are in different positions of your file (with vba). As your code probably (I say this because it has a password, so I did not see the code), it references the columns in the Date (in VBA) file of your file (A, B, C, D, E, F, G), so I import the seven columns that I need it for your file and zip the vba code.But that's no problem, the macro is fine.

Thank you for your attention.
 
500: ... hummm ... the boss can see 500 sheets in one time ...
or the boss has some system how to use those sheets ...
maybe, there would be code for 'timestamp' then someone has opened any of those sheets?
So there should ... have to be one sheet that could see ALL from ONE time!
... there is ... but now the code deletes it!
More Years:
Just now it works only with that layout = writes only those needed values even to empty workbook (it makes missing sheets!) but not 'left side dates ... headers ... borders' ... means not good for use! but maybe as the boss wants!
Import:
Why You couldn't/cannot give 'real layout of that sheet'?
If You have 800'000 rows to copy to other workbook ... that's extra work!
Do company has always same layout? If so, then it can use - no need to copy!

How long time it take to run 'normal' data?
 
Hi Vletm,

First of all, thank you very much for the code you wrote.

But as I said before, I'm a beginner in VBA, so without seeing the code I have no chance to learn, study it line by line, understand how the loops were made, the calculations, thereby understanding the logic behind the execution of the macro. If you can pass me the password, I'm grateful.

Thank you for your attention.
 
Hi Vletm,

Thank you for providing the password.

You love a pivottable rsrs.

This passage of code (sample 1) is very impressive, did not imagine the strength of VBA.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C1:R" & y_max & "C6", Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=v_tab & "!R2C2", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14

With Sheets(v_tab)

.Select

With .PivotTables("PivotTable1")

With .PivotFields("Periodo")

.Orientation = xlRowField

.Position = 1

End With

With .PivotFields("Store")

.Orientation = xlColumnField

.Position = 1

End With

With .PivotFields("Product")

.Orientation = xlRowField

.Position = 2

End With

.CalculatedFields.Add "wo", "= 'Amount x Unit Price'/'Amount'"

.PivotFields("wo").Orientation = xlDataField


This passage of the code (sample 2), used only somases, very good.

.Cells(y, 15) = WorksheetFunction.SumIfs(Sheets("Data").Range("D:D"), Sheets("Data").Range("A:A"), .Cells(y, 12), Sheets("Data").Range("B:B"), .Cells(y, 13), Sheets("Data").Range("C:C"), .Cells(y, 14))

.Cells(y, 16) = WorksheetFunction.SumIfs(Sheets("Data").Range("F:F"), Sheets("Data").Range("A:A"), .Cells(y, 12), Sheets("Data").Range("B:B"), .Cells(y, 13), Sheets("Data").Range("C:C"), .Cells(y, 14))

I made some adjustments here and it was perfect.

Once again I thank you for contributing to my learning.

Again, very grateful for the attention.
 
You must log in or register to reply here.
Back
Top