Copy sheet to new workbook

ThisWorkbook.Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs "C:\Target.xlsx", FileFormat:=51
This will automatically create a new workbook called Target.xlsx with the relevant sheet

Paste Overwrite

    Application.CutCopyMode = False

turn it back to True before Exit Sub.

Nested Quotes

prefer creating a global variable: Public Const vbDoubleQuote As String = """" 'represents 1 double quote (") Public Const vbSingleQuote As String = "'" 'represents 1 single quote (') and using it like so: Shell "explorer.exe " & vbDoubleQuote & sPath & vbDoubleQuote, vbNormalFocus


"" creates a quote character


CHR(34) creates a quote character CHAR() is used as an Excel cell formula, e.g. writing "=CHAR(34)" in a cell, but for VBA code you use the CHR() function

Traversing Directories

http://analystcave.com/vba-dir-function-how-to-traverse-directories/#Traversing_directories

Dir Function

The Excel Dir function returns the first filename (directory) that matches the path and the provided attributes. To retrieve subsequent filenames (directories) that match the path and attributes call the Dir function again without providing ANY arguments

Cycle through files in a folder

For example if you are looking for the file "test":

Dir takes wild cards so you could make a big difference adding the filter for test up front and avoiding testing each file

Sub LoopThroughFiles()
    Dim StrFile As String
    StrFile = Dir("c:\testfolder\*test*")
    Do While Len(StrFile) > 0
        Debug.Print StrFile
        StrFile = Dir
    Loop
End Sub

Excel RegEx

http://analystcave.com/excel-regex-tutorial/

instr function

http://analystcave.com/vba-reference-functions/vba-string-functions/vba-instr-function/

LIKE function

1.  "*" – matches any number of characters
2. "?" – matches any 1 character
3. "[]"– matches any 1 character specified between the brackets
4. "- "  matches any range of characters e.g. [a-z] matches any non-capital 1 letter of the alphabet
5. "#" – matches any digit character

Matching against letters

If "My house number is 22" Like "*##" then

   Debug.Print "Match: String contains a 2 digit number"

End If




Matching against numbers

1 2 3

If "My house number is 22" Like "*##" then    Debug.Print "Match: String contains a 2 digit number" End If

Matching a phone number with either dashes or dots

1 "123-345-678" Like "###[-.]###[-.]###"

Matching a certain string within another string

1 "fewfwfewfwefdogfefweff" Like "*dog*"