Hi All,
Very new to this forum and VBA. I have been looking at VBA and (try) to combine for some time now, with different rates of success. I still have one persisting issue though on which I could really use some help to make my professional life a little easier.
I receive weekly 12 different sales reports. different file names, same layout (A2..K??), all stored in same Directory by weekname. Colums always same, lines vary.
I currently copy/paste all the data, file by file, into my MASTER.XLSX DATA_worksheet. Based on this sheet a pivot table in a different worksheet gets updated.
SO ... I am looking for VBA (stored in the MASTERbook) that for each file found in a specific named directory (c:\sales\week3\*.xlsx) determines the data range it need to copy (as lines vary by week) and adds these lines to the MASTER.xlsx DATA_worksheet. Preferably paste as values.
Part 2 is that once the MASTER.xlsx is updated, the 12 sales regions each need to get their own pivottable WITHOUT access to data from the other salesregions.
So here I am looking for VBA that based on the MASTER.XLSX DATA_worksheet generates 12 new files (names based on Regionfield (colum C), or even better: fixed tekst combined with pivottable filter.
Each file should contain a formatted pivottable with colum A as a filter, colum B on row, colum D on line, and a sum on colum F (formatted as currency).
All help, hints and suggestions are more then welcome
Very new to this forum and VBA. I have been looking at VBA and (try) to combine for some time now, with different rates of success. I still have one persisting issue though on which I could really use some help to make my professional life a little easier.
I receive weekly 12 different sales reports. different file names, same layout (A2..K??), all stored in same Directory by weekname. Colums always same, lines vary.
I currently copy/paste all the data, file by file, into my MASTER.XLSX DATA_worksheet. Based on this sheet a pivot table in a different worksheet gets updated.
SO ... I am looking for VBA (stored in the MASTERbook) that for each file found in a specific named directory (c:\sales\week3\*.xlsx) determines the data range it need to copy (as lines vary by week) and adds these lines to the MASTER.xlsx DATA_worksheet. Preferably paste as values.
Part 2 is that once the MASTER.xlsx is updated, the 12 sales regions each need to get their own pivottable WITHOUT access to data from the other salesregions.
So here I am looking for VBA that based on the MASTER.XLSX DATA_worksheet generates 12 new files (names based on Regionfield (colum C), or even better: fixed tekst combined with pivottable filter.
Each file should contain a formatted pivottable with colum A as a filter, colum B on row, colum D on line, and a sum on colum F (formatted as currency).
All help, hints and suggestions are more then welcome
