Merge 3 workbooks into new workbook

[Eitel13]

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.

 

[SkipVought]

Hi,

This will not be as daunting a task as it appears to you. After all, you are a programmer.

I have already got the script that copies these files into a new folder and renames them without the date - Terminations_ToolName

??? Three files in the same folder with the same filename?

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.

What do you mean by “each workbook” and “the columns”?

I suppose it would be good if you were to describe the structure of the source data.

How do you get these three files?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Eitel13]

Hi Skip,

Thank you for the reply, I gathered my initial post would seem rather confusing so I will try answer all your questions....

I managed to find an answer to half of the question - sorting a column in the worksheet by Date.

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

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

I found the above code snippet from:

http://www.vbaexpress.com/forum/showthread.php?46703-VBA-to-autofilter-on-current-date

As for your question regarding the file names, how it works is that the name of the files are as follows: Date_Terminations_Tool1, Date_Terminations_Tool2, Date_Terminations_Tool3 and the script renames the files to: Terminations Tool1, Terminations Tool2 and Terminations Tool3 respectively. So in essence, they all have different file names.

I still need to figure out how I can copy the filtered rows to a different worksheet. I tried uploading a sample excel sheet but the browser keeps freezing when I do, so for now I will just paste the HEADINGS of each spreadsheet (Terminations Tool 1, Terminations Tool 2, Terminations Tool 3):

I apologize, this is a lengthy post - The 3 extracts do not unfortunately have the same headings, so I will need to further sort that out to only the relevant ones required for the exercise ...

Tool 1 Extract:

employeeNumber
ID_NUMBER
sn
givenName
ts_employee_start_date
ts_employee_end_date
ts_notified_termination_date
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE
SERVICE_LAST_UPDATE_DATE
ORIGINAL_HIRE_DATE
PERSON_LAST_UPDATE_DATE ASSIGNMENT_LAST_UPDATE_DATE
ts_segment
ts_business_unit
ts_organization BUSINESS_GROUP
LEGAL_ENTITY
location_description
mail
NATIONALITY
role_title
SUPERVISOR_LAST_UPDATE_DATE
ts_supervisor_last_name
ts_supervisor_first_name
ts_supervisor_employee_number
location_country
ts_system_employeetype
id_5_digits
roleName
mutual_branch_user
personType
ts_supervisor_mail
location_postal_code
personId

Tool 2 Extract:

employeenumber
givenname
displayname
ts_employee_start_date
ts_employee_end_date
ts_business_unit
mail
ts_supervisor_firstname
ts_supervisor_displayname
generate_mailbox
ts_supervisor_telephone
company
sn
ts_supervisor_surname
telephonenumber
ts_mutual_branch_user
cn
ts_supervisor_employee_number
status
ts_supervisor_mail
ts_supervisor_mobile
mobile
employeetype
ts_jobtitle
create_date
ts_supervisor_businessunit
ts_cost_centre
manager

Tool 3 Extract:

employeenumber
givenname
displayname
ts_employee_start_date
ts_employee_end_date
ts_role_title
modified_by
modify_date
ts_business_unit
mail
ts_supervisor_firstname
ts_supervisor_displayname
generate_mailbox
ts_supervisor_telephone
ts_role_size
company created_by
sn
ts_supervisor_surname
nationality
ts_mutual_branch_user
ts_passport_number
ts_idnumber
cn
ts_role_family
date_of_birth
ts_supervisor_employee_number
status
ts_gender
ts_budget_entity
ts_supervisor_mail
title
ts_supervisor_mobile
mobile
ts_segment
ts_businessgroup
zip_code
employeetype
ts_race create_date
ts_supervisor_businessunit
ts_cost_centre
address_3
address_2
manager address_1
ts_assignment_category

 

[SkipVought]

Thank you for your explanation. It cleared up some of my questions and I have a better picture of your task.

First let me recommend that you do not open these .csv text files, or any other text files for that matter, directly with Excel. It is risky business, since Excel can and does change data. faq68-7375.

Rather IMPORT your data via Data > Get External Data> From Text... and drill down to your .csv file(s).

This will give you control over each column of data: numeric, date or text.

I would set up three sheets for your three .csv files. This would be a ONE TIME task of going through Data > Get External Data> From Text.... I would not even worry about the DATE part of the filenames, as that can be easily handled in your code in the Terminations_Template workbook, where all this will take place.

Once you have done this, you will have a QueryTable embedded in each of these three sheets that can simply be Refreshed with the ability to select a new file, which can all be performed in your code.

That would be the first bit of business. Then comes the filter, copy and paste step.

BTW, if you are only interested in certain columns from each of your files you can, in the IMPORT process, just import those columns to your Terminations_Template workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Eitel13]

Hi Skip,

Sorry for taking long to reply, I have made some progress since your last response.

With regards to your previous response:

1) Doing the import from csv to an xlsx file seems to be a bit difficult, because the way each spreadsheet is structured is different and then the import doesn't work properly. Would saving the csv file as an xlsx file not be a more simpler solution?

Here is some code that could do that:

Found here:

http://vbcity.com/forums/t/151467.aspx

Const xlDelimited                =  1
Const xlTextQualifierDoubleQuote =  1
Const xlOpenXMLWorkbook          = 51

Set xl = CreateObject("Excel.Application")

xl.Workbooks.OpenText "Y:\Personal Folders\XXXX\TestFile1.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, False, False, True, False, False, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "Y:\Personal Folders\XXXX\x.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Quit

2) Is it necessary to do the change from csv to xlsx? I only ask because up until you mentioned it in this post, I have not yet had any issues manipulating data in vba with csv files..

This is my current process:

1) The headings required are as follows:

Tool 1 Extract:

employeeNumber
givenName
ts_employee_end_date
ts_business_unit
mail
ts_supervisor_last_name
ts_supervisor_first_name
ts_supervisor_employee_number
branch_user
ts_supervisor_mail

Tool 2 Extract:

employeenumber
displayname
ts_employee_end_date
ts_business_unit
mail
ts_supervisor_displayname
ts_supervisor_telephone
branch_user
ts_supervisor_employee_number
status
ts_supervisor_mail
ts_cost_centre

Tool 3 Extract:

employeenumber
displayname
ts_employee_end_date
ts_business_unit
mail
ts_supervisor_displayname
ts_supervisor_telephone
branch_user
ts_supervisor_employee_number
status
ts_supervisor_mail
ts_cost_centre

2) Tool 2 and Tool 3 use the exact same columns, however, Tool 1 has different headings and I fear this will make the process of merging the 3 workbooks together a lot more complicated

3) I Run 1 Script that executes the following actions:

3.1) Scans the directory for the latest csv files - these are the 3 extracts for each tool - there are hundreds of files in this directory so its finds it based on file name which contains the current date​

3.2) Copy the latest file extracts to a folder within this directory​

3.3) Rename the files from Date_Filename_Tool1 to Filename Tool1 and so on​

3.4) Convert the csv file to an xlsx file and delete the csv copy​

3.5) Open all 3 xlsx workbooks and the Terminations_Template workbook​

4) This is where the excel comes in

4.1) The code I posted above for the filter has changed slightly - It now filters to display all records EXCEPT today's date​

4.2) With my filter I delete the VISIBLE rows and then all the records with today's date are moved to the top​

4.3) This step is where I need to copy the remaining rows to the Terminations_Template.xlsx workbook​

4.4) Close all workbooks except Terminations_Template.xlsx​

Updated Code:

Filter:

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

End Sub

Delete Visible Rows:

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

For whatever reason though, the

.Offset(1,0)

doesn't exclude the first row.. But this is not an issue for me right now..

Your help is much appreciated Skip!

 

[SkipVought]

Doing the import from csv to an xlsx file seems to be a bit difficult, because the way each spreadsheet is structured is different and then the import doesn't work properly. Would saving the csv file as an xlsx file not be a more simpler solution?

Here’s the design overview that I’m suggesting in your Terminations_Template workbook:
[ul]
[li]First three sheets, each IMPORT the data from a .csv text file[/li]
[li]Sheet 4 performs a query using the data from the first three sheets to combine the data[/li]
[/ul]
Don’t know what other sheets you might need yet.

So setting up three sheets to IMPORT each one of your .csv files, is a ONE TIME task. It is a very simple process and not at all difficult.

Saving each .csv as a .xlsx snd then getting the data into your Terminations_Template workbook is a greater effort and adds additional files.

With the IMPORT process, all your data is in one workbook ready to manipulate.

But if you’re set on doing it some other way, so be it. What’s the next thing you need to happen?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Furthermore (plz include my previous post) I'm making this assumption: that each of the three .csv files have different employeeNumbers.

So here's how they map...
[pre]
1 2 3
employeeNumber employeenumber employeenumber
givenName displayname displayname
ts_employee_end_date ts_employee_end_date ts_employee_end_date
ts_business_unit ts_business_unit ts_business_unit
mail mail mail

ts_supervisor_last_name
ts_supervisor_first_name
ts_supervisor_displayname ts_supervisor_displayname
ts_supervisor_employee_number ts_supervisor_employee_number ts_supervisor_employee_number
branch_user branch_user branch_user
ts_supervisor_mail ts_supervisor_mail ts_supervisor_mail

ts_cost_centre ts_cost_centre
ts_supervisor_telephone ts_supervisor_telephone
status status
[/pre]

So what do you do with the columns that don't map to all three?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Eitel13]

Hi Skip,

I really appreciate your patience and assistance with this.

I am keen to giving the import option a try, I just seem to be struggling with it

1) The MAIN reason I am struggling with the import is because when doing the import, I do not know which options to select

1.1) When I import for the first csv file, I select the Delimited option​

1.2) Then I tick the My data has headers check box​

1.3) I click Next​

1.4) By default, the Tab checkbox is ticked - I noticed with the first csv file, this option doesn't display the data correctly, so I instead tick the Comma check box​

1.5) I click Next​

1.6) I then select the columns I want and skip the ones I do not need - at this step I select the Date radio button for the Date column​

1.7) I click Finish and the import works​

1.8) The only issue I have here is that at line A21, it creates its own concatenated line of data that has no relevance with the import... Its like random data it couldn't sort - Like instead of there being only one comma separating columns, there's 2 or more and the "import" doesn't know how to handle it​

When I try do the steps above with the other 2 csv files, I get stuck at step 1.4 already because the data is not "structured" the same way (see 1.8 above).. Even if I try with the other check box options, it still does not work like it does with the first import.

2) So if my understanding is correct, I import all 3 workbooks to one workbook, but each import will create a new sheet, so in essence in this centralized workbook I will have 4 sheets - 1 sheet will become the "merged" sheet by combining the other 3 sheets?

3) Is the workbook that I am importing the 3 extracts to, the Terminations_Template workbook or what workbook am I actually importing to?

4) The way you showed how the sheets would map, I would say look correct, however I would like to try and have the format for Tool 1 regarding the ts_supervisor_last_name & ts_supervisor_first_name concatenated to make a column of its own - ts_supervisor_displayname - This just to allow an "easier" merge of the 3 sheets

5) After I do the actual import to the new/centralized workbook, what do i do then to "merge" the 3 worksheets within this workbook?

6) I know you say that this import will be a one time thing, but how does this work?

Because everyday it's a new file that the data needs to be "read" from, how would the query know to go look at the new file? - I hope this makes sense to you?​

Example

Today the file we do an import from will be 28/02/2018_Terminations_Tool1 and tomorrow the import would need to be done from the 01/03/2018_Terminations_Tool1 file - but effectively the link is to the 28/02/2018_Terminations_Tool1, so wouldn't I need to "redo" the import for the new file again each day? If that is the case, this is what I'm trying to avoid​

If perhaps I run the script that makes a copy of the latest files and renames them to a "default" name like Tool 1, Tool 2, & Tool 3 respectively, then essentially each day when I run the script I will simply be replacing the previous days files with the current days files and the "Name" of the file wouldn't look different from the point of the import - Just a thought?​

I am pretty much stuck at the moment....

 

[SkipVought]

It would be helpful if you would upload test versions of your three .csv files. You would only need a few rows of data in each with care to include the dates/data of interest along with other dates/data fir purposes of filtering...

...and to demonstrate this import issue.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

import all 3 workbooks .csv files to one workbook, but each import will create you wiil put in a new sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's an example of three .csv files imported into one workbook, with code that Imports based on today's date and Joins data from the three sheets.

Open the workbook and toggle to the VBA editor. In the Import procedure, change the path accordingly. Then Run Import and Join.

Each day that you run the Import procedure, it will import that day’s three text files into your three sheets. Then run the Join procedure to produce new data on the Join sheet.

The principle I follow is this for a recurring import or query process where changes in filepath or filename or queried data are required...

1) use Data > Get External Data... to get data from text files, Excel files (including the Excel workbook you’re in), Access files/databases, Oracle, DB2, SQL Server. This will be a ONE TIME task that puts a QueryTable object in your sheet.

2) use code like this to access the Connection String and the SQL String to be able to change either one as required...

With ActiveSheet.ListObjects(1).QueryTable
   Debug.Print .Connection
   Debug.Print .CommandString
End With

Note that performing this manual task more than once on a sheet, will place multiple QueryTables on your sheet which can only be identified via code. If you must try again, Select ALL the cells on the sheet, Right-Click DELETE, which will Delete all objects on the sheet.

3) code your own refresh process which includes changing the Connection String and/or the Commans String.

This is the Import and Join procedures in the workbook...

Sub Import()
    Dim sPath As String, sDB As String, sConn As String
    Dim i As Integer
    
'[b]put your path to the .csv files here
    sPath = "C:\Users\Skip\Documents"      '[/b]
    
    For i = 1 To 3
        sDB = Format(Date, "dd-mm-yyyy") & "_Terminations_Tool" & i & ".csv"
        sConn = "TEXT;" & sPath & "\" & sDB & ""
        With Sheets(i)
            Select Case .Name
                Case "Join", "Sheet5"
                Case Else
                    With .QueryTables(1)
                        .Connection = sConn
                        .TextFileCommaDelimiter = True
                        .TextFilePromptOnRefresh = False
                        .Refresh False
                    End With
            End Select
        End With
    Next
    
End Sub

Sub Join()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
    
    sPath = ThisWorkbook.Path
    
    sDB = ThisWorkbook.Name
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=C:\Users\Skip\Documents;"
    sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT "
    sSQL = sSQL & "  t1.`employeeNumber `"
    sSQL = sSQL & ", t1.`givenName `"
    sSQL = sSQL & ", t1.`ts_employee_end_date `"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `TT1$` t1"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "UNION ALL"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SELECT "
    sSQL = sSQL & "  t2.`employeeNumber `"
    sSQL = sSQL & ", t2.`displayname `"
    sSQL = sSQL & ", t2.`ts_employee_end_date `"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `TT2$` t2"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "UNION ALL"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "SELECT "
    sSQL = sSQL & "  t3.`employeeNumber `"
    sSQL = sSQL & ", t3.`displayname `"
    sSQL = sSQL & ", t3.`ts_employee_end_date `"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `TT3$` t3"
    
    With Sheets("Join").ListObjects(1).QueryTable
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

1.8) The only issue I have here is that at line A21, it creates its own concatenated line of data that has no relevance with the import... Its like random data it couldn't sort - Like instead of there being only one comma separating columns, there's 2 or more and the "import" doesn't know how to handle it

I would open this csv file - which is just a simple text file - in Notepad and look at the text in line 21. You may find it to be 'corrupted' with either missing or extra commas.


---- Andy

There is a great need for a sarcasm font.

 

[Eitel13]

Hi all,

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

Skip, I appreciate your effort in this thread, however, I was not able to figure out how to effectively/accurately make use of the method you suggested.. Perhaps you were right in that I was "set" in wanting to use my "method"

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)

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

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

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

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

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

 

[Andrzejek]

If you:
2.3} Converts the csv files into xlsx files and delete's the csv copies
How come you keep opening CSV files? They should not be there - you deleted them...

Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations Non HR.[red]csv[/red]")
...
Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations TOOL.[red]csv[/red]")
...
Set x = Workbooks.Open("C:\Users\Desktop\Terminations Report\Daily Terminations.[red]csv[/red]")
...

And to remind you about Skip's point made above:

First let me recommend that you do not open these .csv text files, or any other text files for that matter, directly with Excel. It is risky business, since Excel can and does change data

And you keep opening and closing [tt]Terminations Template.xlsx[/tt] - Why?

Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")
...
y.Close True
...
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")
...
y.Close True
...
Set y = Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")
...
y.Close True

But - if it works and you are happy with it, then it is done....


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Why cycle through the data several times?

Sub copySheetFilterConsolidate()
    Dim wb As Workbook
    Dim ws As Worksheet, wsCon As Worksheet
    Dim x As Long, lastrow As Long
    Dim sCSVNames(2) As String, i As Integer
    
    sCSVNames(0) = "Daily Terminations Non HR"
    sCSVNames(1) = "Daily Terminations TOOL"
    sCSVNames(2) = "Daily Terminations"
    
    x = CLng(Date)
    
    With Workbooks.Open("C:\Users\Desktop\Terminations Report\Terminations Template.xlsx")
        Set wsCon = .Worksheets("Consolidated")
        For Each ws In .Worksheets
            Select Case ws.Name
                'I assume that these three sheets are the first three sheets in Terminations Template
                Case "Daily Terminations Non HR", "Daily Terminations TOOL", "Daily Terminations"
                    'open the .csv using Excel
                    Set wb = Workbooks.Open("C:\Users\Desktop\Terminations Report\" & sCSVNames(i) & ".xlsx")
                    'copy the data from the .csv to the corresponding sheet in Terminations Template
                    wb.Sheets(1).UsedRange.Copy .Sheets(" & sCSVNames(i) & ").Cells(1, 1)
                    'close the workbook from the .csv without saving
                    Application.DisplayAlerts False
                    wb.Close
                    Application.DisplayAlerts True
                    
                    'in the Terminations Template Workbook
                    'filter the sheet to < current date
                    ws.UsedRange.AutoFilter _
                        Field:=9, _
                        Criteria1:="<" & x, _
                        Operator:=xlAnd, _
                        Criteria2:="<" & x - 1
                    'delete visible rows
                    ws.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                    'copy data to Consolidated Sheet
                    lastrow = wsCon.UsedRange.Rows.Count            ' last row in column C
                    ws.Range("A1:M" & lastrow).Copy
                    wsCon.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                    
                    i = i + 1
                Case "Consolidated"
                
            End Select
        Next
        .Save
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Eitel13]

Thank you for the response @Andrzejek & @Skip..

1) @Andrzejek, the code I posted with regards to the opening of the .csv files was incorrect.. I'm not making use of the .csv files, its definitely the .xlsx files.. I just checked now again - thank you for pointing that out.

2) @Andrzejek I honestly don't know why I continuously open and close that same file... Still learning as I go along here lol, but again, thank you for pointing it out... I will work on a better solution, which I actually think Skip has provided above

3) @Skip, thank you for the code sample.. As far as I can tell, it is literally 1/10th of the length of code I posted and yet performs the same job.... BETTER. I tried to run it but I got the following error:

Compile error: Invalid use of property​

This is at the

Application.DisplayAlerts False

line of code​

I also noticed something else I made a mistake with... I didn't create a "loop" to work through all 3 sheets.. i.e, filter, delete & copy.. I only did it for one, which would mean I would have had to execute a macro for each worksheet. But again, it looks like you provided a solution to this above..

Thank you again, look forwards to your response

 

[SkipVought]

In what application does your code reside?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

DisplayAlerts is Application's property, not method, so the syntax:
[tt]Application.DisplayAlerts = False[/tt]

combo

 

[SkipVought]

Duh! Of course, combo is correct.

‘
                    Application.DisplayAlerts = False
                    wb.Close
                    Application.DisplayAlerts = True

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]