asked on

Merge 3 workbooks into new workbook

Hi All,

I have what seems to be a huge task and as I am not at all skilled in VBA it seems even more daunting..

This is the scenario:

1) Everyday there are 3 csv files exported; Each of them come from the 3 tools that manage employee records

2) These 3 csv files contain the TERMINATIONS of users who's access need to be revoked on that day

3) Each csv is named Date_Terminations_ToolName; I have already got the script that copies these files into a new folder and renames them without the date - Terminations_ToolName

4) Each csv file contains the records for employees who's access needed to be revoked for the past 6 months; The first thing I need to do is sort/filter the current days date to the top of the worksheet

5) Then I need to copy the rows that contain the current date into the Terminations_Template file

6) One issue is that the name of the sheet in each csv file is named the same as the original file name - Date_Terminations_ToolName

In a nutshell, I require code that will identify the columns in each workbook by the current day and copy those adjacent rows to the Terminations_Template workbook.

Eitel Dagnin

ASKER

I have managed to get half of the questioned answered (Which I shared below) however, the second part (copying the rows to a new workbook) I am still working on and sill looking for an answer.

This will filter the sheet according to the correct column by the current date.

As far as I can tell, the line that includes

Field:=9

Open in new window

determines the column to filter on.

Where the number 9 is, indicates which column. For example, replace 9 with 3 and you'll get column C, or replace it with 1 and you'll filter on column A.

Sub dateFilter()
x = CLng(Date)
ActiveSheet.UsedRange.AutoFilter Field:=9, Criteria1:=">=" & x, Operator:=xlAnd, Criteria2:="<" & x + 1

End Sub

Open in new window

I found the above code snippet from: http://www.vbaexpress.com/forum/showthread.php?46703-VBA-to-autofilter-on-current-date

Jan Karel Pieterse

Which version of Excel are you using precisely? I suspect this is a task that is easily done using Get&Transform (AKA PowerQuery)

Eitel Dagnin

ASKER

Hi @jkpieterse, I am using Excel 2013. I am not familiar with "Power Query"

Jan Karel Pieterse

Powerquery is an add-in which you can download for free if you have Excel 2013. See this site for instructions: https://www.excelcampus.com/install-power-query/

Matt Nicholas

As recommended by jkpieterse, Power Query will be your lifesaver here (no code solution)

Download this for free from the official Microsoft website and you are on the first step to having a 'no code' easy to maintain and future proof solution - as it appears that your data is recurring in nature.

Power query allows you to select folders and as you add files, the query simply refreshes adding and inlcluding new datasets. This tool will also allow you to merge the datasets and only have the columns you need. Each week for you this will be a simple 'refresh' from within Excel - imagine that!

Steps 4, 5 and 6 will become a click of the mouse - definitely recommend you do some reading/Youtubing

ASKER CERTIFIED SOLUTION

Eitel Dagnin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eitel Dagnin

ASKER

Code provided above to merge 3 desired workbooks.