VBA or excel Formulas

[Andrewryan3]

I have been tasked with extracting data from SAP into an excel worksheet. I need to sort it by a material grouping(2 or more materials, as many as 5, summed together) and place it by week into another worksheet for the entire year. This will be done on a weekly basis and will constantly be changing.

Thoughts?

 

[Kamolga]

I would go for setting the data in column properly on a data sheet. There might be no work depending on how you get the data from SAP.

As an example, you might get dates from SAP without week numbers. By putting the data in a table and having a column on the right calculating the week number of the date, it will keep on calculating week number for the rows that you add in the future. So you can chose to add missing data or replace all data every week, the week number will be there.​

From there I would make a pivot table based on weeks and proper grouping.

I would then automate the refreshing of the pivottable, so people do not have to think about it: right click the sheet as if you want to rename it, click "view code" and paste

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll<strike></strike>

End Sub

Every time someone click on the sheet to view the report, it is up-to-date (especially if you run the extract on open workbook event, if possible)