VBA from Filtered Report into New Excel Doc

[J00m82]

Hello,

I am new to Macros and would greatly appreciate it if someone had VBA coding help. I am trying to build a Macro that pulls filtered data on the department/class level from a weekly excel file to a set location (column/row) in a new excel file. Instead of having to constantly filter from the weekly file and then copy/pasting into the new excel file location, would a Macro be possible?

Thank you

 

[Luke M]

Hello, and welcome to the forums!

As macros tend to need very exact in the instructions we give them, it would help to have a copy of your workbook, or at least a sample with same layout. Otherwise, it will make for more work as we will have to guess as to location of input/output ranges.

 

[J00m82]

Thanks Luke M! Here are the workbooks:

The first workbook "RIS Template" is usually populated and is filtered using the top headers "Dept." and "Class" and copy/pasted into the other attached workbook "Inventory $ and WOS" starting at the D7 (Department 7) Inventory $ tab to the D261 (Department 261) Inventory $ tab. The location in the "Inventory $ and WOS" workbook in each tab is where you see the column headers:
DPCI Barcode DEPT Class Product Description 201501 WK 3 201501 WK 4 201502 WK 1 201502 WK 2 Goal OOS Current WOS at Target WOS Above Presmin Presmin Final Calc Units EOH +OW WOS Shipped LW Units Shipped LW Last 4 Week's Average Sales Sales LW AVP LW AIR Forecast Next 4 Weeks % of Sales to Forecast J&J Units Cut Last Week (Pieces)

Some of the departments have multiple classes and thus you may need to scroll down the page.

Thanks so much!

 

[Nebu]

Hi:

As per my understanding you need to split the data in RIS tab between the the 7 departments based on afilter applied on Dept and Class headers . This is fairly easy to do, but in the uploaded file all the columns in RIS tab is showing 0 , could you please elaborate on this.

Thanks

 

[Luke M]

Ditto to Nebu's comment. Could you expand on what might be in RIS tab, col D and E. I assume it's based on this somehow that we know which worksheet in Inventory book to put things.

In template book, do any of the other worksheets matter?

Finally, to get us started, I'd take a look at the code here:
http://www.rondebruin.nl/win/s3/win006.htm

as it sounds somewhat close to what you want.

 

[J00m82]

Thanks Nebu! The reason they are 0's is because the worksheet is a template. I have uploaded an older populated file that has RIS data.

 

[J00m82]

Luke M. In the RIS Template book the only worksheet that matters is the RIS.

 

[Nebu]

Hi:

Please find the attached , the assumption is that you will be saving the both RIS and Inventory workbooks in the same folder.
Enjoy
Thanks

 

[J00m82]

Thank you so much for your work Nebu. Each week there is a new RIS which is updated from a data pull and uploaded to a Sharepoint. The Inventory $ WOS is a single file that is continuously updated from the RIS and is also on Sharepoint. With this in mind would the macro still work? There is always the option of manually downloading the Inventory $ WOS and uploading it.

 

[J00m82]

I also keep running into a Microsoft Visual Basic for Application error - 400. Maybe I'm running the macro wrong?

 

[Nebu]

Hi:

The macro won't work in share point, it picks up inventory file from the folder it is saved in. I am not sure about the error -400 could you please highlight the line which gives you the error. The macro ran at my end without any issue . If you can upload the files you are running the macro I can check for any bug.

Thanks

 

[J00m82]

Thanks Nebu! Here they are. I tried copying the VBA over to this report. Maybe something went wrong from there.

 

[Nebu]

Hi:

Please find the attached. It is running fine at my end. I guess I forgot to mention about the table which pick the references for the macro. I have included the table , sorry that I did not mentioned it earlier , thought you will figure it out.

THanks

 

[J00m82]

Thanks a million Nebu! It looks great.

 

[J00m82]

Hey Nebu, if I need to make adjustments to the macro, say for a new dept/class addition. How would I go about doing so?

 

[Nebu]

Hi:

Add it to the table we have created ,along with the sheet name and the cell reference.

Thanks

 

[J00m82]

Hey Nebu!

Hope all is well. We had some changes and class additions and I was wondering if you could guide me through adding them into the macro to meet the correct cells/tables on the Inventory $ and WOS. The correct cells/tables for pasting on the Inventory $ and WOS excel are denoted by a highlighted cell directly above each table on the respective department tab, this indicates the correct dept./class for the macro to carry over from the filtered RIS mar wk. 5.

Thanks you much!

 

[Nebu]

Hi:
Sorry for the delay. I have fixed the macro, the key here is to change the cell reference in the blue table.

Thanks