Copy data from new file in a folder

[K]

Hi all, I have a macro in "Sheet1" which copies the data from all the
files which are in folder ("C:\Test"). The files which are in this
folder have six digit number in their names. Like for example (see
below)
Record 556987 entry.xlsx
Record 587945 reg.xlsx
Record 547896 fig.xlsx
etc

the macro also extract that six digit number from the files name from
where it copies the data and put those six digit numbers in column A
of "Sheet1" so I know from which files I have copied the data. Time to
time new files get save in folder ("C:\Test") by my self and my
colleagues and every time when i know there are new files in the
folder i run macro to update "Sheet1" so i can have latest data copied
from the files. At the moment macro opens all the files one by one
and copies the data from the files and extract six digit numbers from
their name and close those files. i want some code that macro first
look in six digit numbers in files name and match them with six digit
numbers in column A of "Sheet1" and if there is new file with new six
digit numbers in its name then macro should only open that to copy the
data instead opeing all the files from which i have already copied the
data. Please can any friend can help.

 

[Per Jessen]

Hi

Maybe something like this:

Sub PPP()
Dim TargetRange As Range
Sheets("Sheet1").Activate
Set TargetRange = Range("A1").End(xlDown)

'your loop to open files, before next file is opened,
'insert next lines and fit to suit
TargetNum = Mid("NextFileName", 8, 6)
Set f = TargetRange.Find(what:=TargetNum, after:=Range("A1"),
lookat:=xlWhole)

If f Is Nothing Then
'New filename
'
' Your code to open file and import data

End If
'Next
End Sub

Regards,
Per