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 DagninIT Security AdministratorAsked:
Who is Participating?
 
Eitel DagninConnect With a Mentor IT Security AdministratorAuthor Commented:
Hi all,

Just an update of what I have managed to do...

I appreciate the answers in this thread, however, I was not able to figure out how to effectively/accurately make use of the method you suggested..

This is my process from start to finish (I know there are probably better ways of doing this, but this seems to work for me... for now):

1) 3 csv exports are extracted into
C:\Users\Desktop\Terminations with the following filenames:
2018-03-02_Daily_Terminations
2018-03-02_Daily_Terminations_NON_HR

2018-03-02_Daily_Terminations_TOOL

2) I run a script that:
2.1) Makes a copy of the 3 csv files into the following directory:
C:\Users\Desktop\Terminations\Sorted

2.2) Renames the 3 csv files to
Daily Terminations
Daily Terminations NON HR
Daily Terminations TOOL

2.3} Converts the csv files into xlsx files and delete's the csv copies

2.4) Opens the 3 xlsx files and the Terminations Template xlsx file
The Terminations Template file contains 4 sheets named:

Consolidated
Daily Terminations
Daily Terminations NON HR
Daily Terminations TOOL

3) I execute a macro that does the following:

3.1) Renames the sheets within the Daily Terminations; Daily Terminations NON HR; Daily Terminations TOOL xlsx files to the same name as the file itself

Activesheet.Name = Left$(Activeworkbook.Name, InStrRev(Activewoorkbok.Name,".")-1)

Open in new window


3.2) Copy the data from the Daily Terminations; Daily Terminations NON HR; Daily Terminations TOOL workbooks and place it in the corresponding sheets within the Terminations Template workbook

Sub copySheet()

Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations Non HR.csv")
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")

Set ws1 = x.Sheets("Daily Terminations Non HR")
Set ws2 = y.Sheets("Daily Terminations Non HR")

ws1.Cells.Copy ws2.Cells
y.Close True
x.Close False

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations TOOL.csv")
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")

Set ws1 = x.Sheets("Daily Terminations TOOL")
Set ws2 = y.Sheets("Daily Terminations TOOL")

ws1.Cells.Copy ws2.Cells
y.Close True
x.Close False

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations.csv")
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")

Set ws1 = x.Sheets("Daily Terminations")
Set ws2 = y.Sheets("Daily Terminations")

ws1.Cells.Copy ws2.Cells
y.Close True
x.Close False

End Sub

Open in new window


3.3) Filters each sheet by date to show every record prior to the current day

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

Open in new window



3.4) Delete the filtered records to display only the records for the current day

Sub DeleteVisibleRows()
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Application.ScreenUpdating = False
With WorkRng
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True
End Sub

Open in new window



3.5) Remove any columns in the worksheets not needed and adjust the order of the columns (this is to help ensure when the rows are copied that they are copied into the correct columns) - I recorded a macro for this step

3.5) Copy the now relevant/filtered rows to the Consolidated sheet

Sub Consolidate()

Dim ws As Worksheet, ws1 As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Daily Terminations Non HR")
Set ws1 = Worksheets("Consolidated")


    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last row in column C
    ws.Range("A1:M" & lastrow).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ws1.Activate
    
Set ws = Worksheets("Daily Terminations TOOL")
Set ws1 = Worksheets("Consolidated")


    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last row in column C
    ws.Range("A1:M" & lastrow).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ws1.Activate
  
Set ws = Worksheets("Daily Terminations")
Set ws1 = Worksheets("Consolidated")


    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row ' last row in column C
    ws.Range("A1:K" & lastrow).Copy
    ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    ws1.Activate


End Sub

Open in new window


I am always looking to make things more robust so if there are any suggestions to help improve this, please don't hesitate to share...

But for now, my original query has been SOLVED :)
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
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
0
 
jkpieterseCommented:
Which version of Excel are you using precisely? I suspect this is a task that is easily done using Get&Transform (AKA PowerQuery)
1
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi @jkpieterse, I am using Excel 2013. I am not familiar with "Power Query"
0
 
jkpieterseCommented:
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/
0
 
Matt NicholasBusiness AnalystCommented:
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
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Code provided above to merge 3 desired workbooks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.