Locate excel file, then extract columns, paste special values into a named sheet / tab

[Pup Denab]

HI Everyone
I hope one of you can help me with this,
I need to copy columns A-P, then paste special (values only) into another sheet, my problem is the sheet where its going is named however the sheet where it comes from is a system generated name, which is alpha numeric (and not open), the only constant is where it is saved, as this is automatic extracted, into a certain folder, is it possible to collect the data from the newest excel sheet in a folder ?


From
C:\Documents and Settings\All Users\Desktop\updatenov\anyname

To
C:\Documents and Settings\All Users\Desktop\matrix.xlsm\rawdata

I'm looking for vba solution to automate the process

any help would be welcome
Thanks

 

[Dedonovan]

Sorry I forgot to include current code that I am trying to piece together without luck:

Sub AllFiles()Dim MyFolder As String 'Path containing the files for looping
Dim MyFile As String 'Filename obtained by Dir function
Dim MyBook As Workbook
MyFolder = "C:\Desktop\XXXAccount Master" & Application.PathSeparator 'Assigndirectory to MyFolder
MyFile = Dir(MyFolder & "*.xlsx") 'Dir gets the first file of the folder
If MyFile = "" Then
MsgBox "No files found in " & MyFolder & " with extension .xlsx"
Exit Sub
End If
Do
Call PupDenab
Loop
End Sub
Sub PupDenab()
Const destWb As String = "wbtest.xlsm" 'Change to suit
Dim pStr As String, MyFile As String, LastSav As Variant, fNam As String
pStr = "C:\Desktop\XXXAccount Master\"
MyFile = Dir(pStr & "*.xlsx")
If MyFile = vbNullString Then
Exit Sub
Else
LastSav = FileDateTime(pStr & MyFile)
fNam = MyFile
End If
Do
MyFile = Dir()
If MyFile = vbNullString Then Exit Do 'No files left in folder
If FileDateTime(pStr & MyFile) > LastSav Then
LastSav = FileDateTime(pStr & MyFile)
fNam = MyFile
End If
Loop
If Not WorkBookOpen(fNam) Then
Workbooks.Open pStr & fNam
End If
Workbooks(fNam).Sheets("Sheet1").Columns("A:P").Copy
Workbooks(destWb).Sheets("shtest").Range("A1").PasteSpecial Paste:=xlValues
Workbooks(fNam).Sheets("Sheet1").Range("B2").Select
Application.CutCopyMode = False
ActiveWindow.Close
End Sub


Function WorkBookOpen(book As String)
Dim wbName As String
On Error GoTo notOpen
wbName = Workbooks(book).Name
WorkBookOpen = True
Exit Function
notOpen:
WorkBookOpen = False
End Function