Copy and Paste Between Multiple Sheets

[blu817]

I am trying to create a macro that consolidates multiple excel spreadsheets. Now this macro will be ran everyday so the save date varies and the spreadsheets that I am copying are in a share drive. My approach to doing this was to open a new spreadsheet save it as today's date and call the other files in the share drive, copy the sheets and paste them into the consolidated file. The issue I am running into is after copying the sheet the macro is unable to recall the consolidated sheet because of the varying dates. I am still new to VBA and have tried multiple things and lost at this point. The code I've written is below and any suggestions/help with be greatly appreciated.


Sub Consolidated_Sheet()






Dim wb As Workbook
Set wb = Workbooks.Add
Dim XlsFolder As String
Dim fname As String
Dim Newbook As Workbook
Dim SaveFile As String
Dim myWS As Worksheet
XlsFolder = "U:\TSClearing\Best Execution\Limited Quotation Exceptions\Consolidated LQ"






'Save Consolidated
ActiveWorkbook.SaveAs Filename:= _
"U:\TSClearing\Best Execution\Limited Quotation Exceptions\Consolidated LQ" & Format(WorksheetFunction.WorkDay(Date, -1), "mmddyy") & ".xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Workbooks.Open Filename:= _
"U:\TSClearing\Best Execution\Limited Quotation Exceptions\Consolidated LQ" & Format(WorksheetFunction.WorkDay(Date, -1), "mmddyy") & ".xlsx"


'Open KCG file
Workbooks.Open Filename:= _
"U:\TSClearing\Best Execution\Limited Quotation Exceptions\KCG\NITE_" & Format(WorksheetFunction.WorkDay(Date, -1), "mmddyy") & ".xls"


Columns("A:AC").Select
Selection.Copy
'this next line is where I am having issue, looks like it cant recall based on workday
Windows("WorkDay(Date, -1).xlsx").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False











End Sub