Search results

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"...

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...

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...

Hi Vletm, I went back to work, your vba code ran with great perfection and very, very fast !!! How I used your VBA code: I imported the database here from the company, just the six columns that I use to calculate the weighted average for your Data file (with VBA) and rolled the code. As for...

Hi Vletm, Sorry for the delay in answering (health problems). You are a VBA expert, great, yeah !!! As soon as I get back to work I'm going to test the file with the 500 worksheets and give it a return. Thank you very much.

Hi Vletm, In Workbook Report are 500 worksheets (one for each product). The files I've attached are examples with dummy data, because I can not show my company data. The solution given here in the forum I reply to the 500 worksheets in my work without problems. Actually the columns that I...

Hi Vletm, So as I said before, in the Report workbook I have almost 500 worksheets, I think that's why it slows down. In the Data workbook are 600,000 rows with 55 columns, all filled in, it's a heavy file, so I ended up dividing the data into files per year (with about 40,000 rows and 55...

Hi Vletm, Yes, no one will change the old data, so I ended up deciding to split the data by year (each file was about 40,000 lines). As for speed, I had already tried to use the PivotTable, but it still slowed down, the screen faded, excel did not respond, so I started to use formula...

Hi Vletm, So I did not change the case, I actually just changed the name of the Data.xlsm file to DataBase.xlsx, sorry for the confusion. The DataBase file is in a directory and in that file I can not use formulas, it only serves to be the same database. This file that has data from 2002 to...

Hi Vletm, Sorry for the delay in answering. There are some limitations in Excel regarding links. There are certain functions that can not maintain the value when linked to workbooks that are closed (DataBase.xlsx). They are: SUMIF, SUMIFS, COUNTIF, COUTIFS, AVERAGEIF, AVERAGEIFS and INDIRECT...

Hi Vletm, Sorry for the delay in answering, my internet is in trouble I use excel 2007 e the computer I use has an Intel Core i3 processor and 4GB of RAM. I calculate through formula sumproduct, happens that is very slow, because in the report folder are almost 500 worksheets (one per product)...

Hi vletm, So, with said before, I already used PivotTables and today I do this calculation through formulas (sumproduct), both take a long time to calculate, because the data file is very large (from 400,000 to 600,000 lines), the computer screen arrives To be dimmed. My intention is to...

Hi vletm, So for example: For product "101" of store "A" in the month of January / 17 the weighted average should be the sum of (each quantity x each unit price) / sum of the quantity. In the Report folder the result for the product "101" in Jan / 17 should be equal to 5.52 and in Feb / 17...

Hi, I'm trying to calculate the weighted average with some criteria (period, store and product) of the Data file and for each average of each product of each store in a certain period, I wanted to paste it into another Workbook (Report) in the worksheets of the respective products. The problem...