Hi to all. I have many inspection sheets which i need to open, gather the data from, close and open the next one.

I know this is common and ive tried several things here but i dont understand enough to customise existing codes to suit me - here is what i have so far.

Sub Import1()
'
' Import1 Macro
'

'
Workbooks.Open Filename:="S:\01 Files\04 - Work\macro experiment\source data\CS - 003793 - ZS1V57A Valve.xls"
Range("A2:L67").Select
Range("L67").Activate
Selection.Copy
Windows("Tabulation.xls").Activate
Sheets("Input").Select
Range("A2:L67").Select
Range("L67").Activate
ActiveSheet.Paste
Sheets("Gathering").Select
ActiveWindow.WindowState = xlMaximized
Range("C3:AD3").Select
Range("AD3").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Table").Select
If Application.WorksheetFunction.CountA("A:A") = 0 Then
[A1].Select
Else
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks)(1, 1).Select
If Err <> 0 Then
On Error GoTo 0
[A65536].End(xlUp)(2, 1).Select
End If
On Error GoTo 0
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks("CS - 003793 - ZS1V57A Valve.xls").Close
ActiveWorkbook.names("Checks").Delete
ActiveWorkbook.names("Conditions").Delete
ActiveWorkbook.names("Date").Delete
ActiveWorkbook.names("dESCRIPTION").Delete
ActiveWorkbook.names("EW").Delete
ActiveWorkbook.names("Ex").Delete
ActiveWorkbook.names("Further").Delete
ActiveWorkbook.names("Gas").Delete
ActiveWorkbook.names("H").Delete
ActiveWorkbook.names("IP").Delete
ActiveWorkbook.names("NS").Delete
ActiveWorkbook.names("Route").Delete
ActiveWorkbook.names("Sensitivity").Delete
ActiveWorkbook.names("Temp").Delete
End Sub

that does everything i want, except i cannot work out how to change:
"Workbooks.Open Filename" to
"Open the first xls file in a fixed defined directory, and do the next bit"

(The next bit works - happy)

And:
"Workbooks("CS - 003793 - ZS1V57A Valve.xls").Close" to
"Close the xls or file you have just done things with, go back and open the next xls file in the folder"

And:
"Do this task until you have opened all the files in the folder then finish"


Ive been able to do all the first bit this evening having never done a macro before from just this forum so im thinking this is the best place for help!

Many thanks,
Stuart