using named ranges in macros

assign the value of the named range to a variable, thus:

dim abc as string

abc = sheets("Sheet 1").range("Account").value

where Account is a named range in Sheet 1

Pause application for a few seconds

Application.Wait Now + TimeValue("00:00:2")

Not Like

If Not Range("A" & i).Value Like "CLOS*" Then

Wildcards in formulas

http://excelitch.com/how-to-use-excel-wildcard-characters-in-formulas/

Loop through range

http://www.excel-easy.com/vba/examples/loop-through-defined-range.html

Extract File List from Folder

Sub ListFiles2()
Dim fileList() As String
Dim fName As String
Dim fPath As String
Dim i As Integer
Dim startrow As Integer
Dim ws As Worksheet
Dim filetype  As String

'=======================================================
fPath = "C:\Temp\"
filetype = "*"
Set ws = Worksheets("Sheet2")
startrow = 2    'starting row for the data
'========================================================

fName = Dir(fPath & "*." & filetype)
While fName <> ""
    i = i + 1
    ReDim Preserve fileList(1 To i)
    fileList(i) = fName
    fName = Dir()
Wend
If i = 0 Then
    MsgBox "No files found"
    Exit Sub
End If
    
For i = 1 To UBound(fileList)
    ws.Range("A" & i + startrow).Value = fileList(i)
Next
Columns(1).AutoFit

End Sub

Extracting filenames within a given zip file

Sub ListZipDetails()
  Dim R As Long, PathFilename As Variant, FileNameInZip As Variant, oApp As Object
  PathFilename = Application.GetOpenFilename("ZipFiles (*.zip), *.zip")
  If PathFilename = "False" Then Exit Sub
  R = Cells(Rows.Count, "A").End(xlUp).Row
  Set oApp = CreateObject("Shell.Application")
  For Each FileNameInZip In oApp.Namespace(PathFilename).Items
    R = R + 1
    Cells(R, "A").Value = FileNameInZip & "  (" & PathFilename & ")"
  Next
  Set oApp = Nothing
End Sub

NOTE: As written, this code will not iterate through folders inside of the zip.