Command Button Macro to Auto Filter, Copy from one file to another

**abdulbarimd** · 08-27-2013, 08:56 AM

Hi All,

Request your help in defining a macro for a command button which helps me to Autofilter and Copy from a SourceData file (w/o opening) which is stored at a location in d-drive and while pasting into another sheet (master file will have the sheets which are already created for the filtered values) it should replace the existing data in the defined range (A2:Z10).

Requirement description is as below:-

1. I have a file named as "RBD - " stored at a particular location in D-Drive
2. In the master file, when I select a cell (say B2) and enter the date (in dd-mmm-yy format and is same as that of the sourcedata file name in point 1) and click the command button (Refresh) to refresh the data, macro should reach out to the stored file (sourcedata file in D-Drive) which is in the defined date format and read the contents, auto filter on column D (with filter value as Project Name) and copy the contents/rows related to that project (which are already created for those filter values) into the master file.
3. In this process, it should also replace the existing data in the rows of the sheet in master file.
4. If any New Filter Value is added in that SourceData sheet, then in that case, it should automatically create a new sheet with that Filtered value and copy the data as above.
5. Instead of point 2 where the macro should reach the file automatically having the date format, it would also work if it prompts me to browse and select the sourcedata file in my computer. Once the file is browsed and selected, it should not open, but instead, it should automatically filter, copy and paste the contents as mentioned above.

Please help me here in this regards....

**abdulbarimd** · 09-03-2013, 07:44 AM

Can any one help here please?

**arlu1201** · 09-03-2013, 08:03 AM

Please attach a sample file .

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**abdulbarimd** · 09-03-2013, 08:47 AM

Here are the sample files.

WB1 is RBD is my sourcefile which I download from the central server everyday (it is stored at D:\Test\Test-1\)
WB2 is Master - Dashboard is my Dashboard which has the CommandButtons and which I will be using it daily for my MIS (it is stored at D:\Test\Test-2\)

WB 2 has multiple sheets for the projects which are readily listed in WB1.

What I need is, when I click the CommandButton1 of WB2, it should automatically read (not open) WB1 and autofilter and search for project name and copy the entire contents/rows pertaining to that project (eg: CommandButton1 is for Project NVP and when I click that button it should copy the contents of NVP project into the sheet in WB2 and subsequent sheets.

For first instance it will copy, but, from second instance, it should overwrite from Row 2 everytime this data will be refreshed (as it is my daily dashboard file, I keep on refreshing this data).

Once it is done, my second requirement is, I will place another button beside Button1 for sending mail.

This should automatically move or copy this sheet (NVP project sheet), save it in location (D:\Test\Test-3\) in the format as "NVP - RBD - <Current Date (dd-mmm-yy format)>", overwrite if required for the latest save.
Attach this file and send the mail to the intended stakeholder(s) as defined.
<< To, Cc, BCc and Sub lines to be defined and contents to be edited before sending the mail >>.

Idea is to automate the process of sharing project specific data to the concerned owner(s).

Similar process also needs to be automated for PnL Reports as well.

There are much more automation tasks in pipeline related and linked with this.