Pasting formulas between workbooks without workbook link

Have you ever needed to copy and paste a range of cells containing formula, each referencing cells in other worksheets, from one workbook to another ?  When you copy the formula across workbooks, Excel, by default, copies a link from the original file.  So instead of copying “=Sheet2!D6+Sheet2!E6” you end up with =[YourOriginalFile.xls]Sheet2!D6+[YourOriginalFile.xls]Sheet2!E6.

This has always frustrated me, I can see why it would be useful, but wouldn’t it be nice to have a simple way of copying the formula without the original file name.

For a while now I’ve been using a fairly simple work-around, using Edit / Replace.

• Select the range you want to copy
• Edit->Replace
• What:= “=” (the equal sign)  /  With:=”^^” (or some unique string)
• Replace All

Now the formula are text strings, these can be copied and pasted without Excel doing anything to “help” you.

• Select the new range
• Edit->Replace
• What:= “^^” (or your unique string) / With:=”=” (equal sign)
• Replace All

Now the text strings are formulas again.  You will need to ensure you have matching worksheets in that new workbook, otherwise you’ll be dismissing lots of dialogs.

Application.Filesearch Replacement For Office 2007

If you have ever used completed a search for files using VBA you probably made use of Application.Filesearch.  However, if you’ve updated to Office 2007 or 2010 you may have noticed that Filesearch has been removed… but the (very) old Dir remains, so you can use this instead, an example of which follows:

Sub FileSearch()
    Dim fso As Object
    Dim FileName As String
    Dim strArr(1 To 65536, 1 To 1) As String
    Dim i As Long

    ' Set the directory / filename you are looking for
    Const strDir As String = "C:\Your\File\Path"
    Const SearchTerm As String = "YourFileNameTerm"

    ' Complete the search
    Let FileName = Dir$(strDir & "\*" & SearchTerm & "*.xls")
    Do While FileName <> vbNullString
        ' For each file found load to the array
        Let i = i + 1
        Let strArr(i, 1) = strDir & "\" & FileName
        Let FileName = Dir$()
    Loop

    ' Search within sub-folders
    Set fso = CreateObject("Scripting.FileSystemObject")
    Call RecurseSubFolders(fso.GetFolder(strDir), strArr(), i, SearchTerm)

    ' Tidy up and copy the results to the active worksheet
    Set fso = Nothing
    If i > 0 Then
        Range("A1").Resize(i).Value = strArr
    End If

End Sub

Private Sub RecurseSubFolders( _
    ByRef Folder As Object, _
    ByRef strArr() As String, _
    ByRef i As Long, _
    ByRef SearchTerm As String)

    Dim SubFolder As Object
    Dim FileName As String

    ' Search sub folders
    For Each SubFolder In Folder.SubFolders
        Let FileName = Dir$(SubFolder.Path & "\*" & SearchTerm & "*.xls")
        Do While FileName <> vbNullString
            Let i = i + 1
            Let strArr(i, 1) = SubFolder.Path & "\" & FileName
            Let FileName = Dir$()
        Loop
        Call RecurseSubFolders(SubFolder, strArr(), i, SearchTerm)
    Next
End Sub

Excel 2007 – Useful Microsoft Office Shortcuts

The basic Microsoft Office document shortcuts

• For a New Document – CTRL+N
• To Open a Previously Saved Document – CTRL+O
• To Print your Document / Open the Print Settings – CTRL+P
• To Save your Document – CTRL+S
• To Close your Document – CTRL+W

Text Adjustments to your Documents

• Cut the Highlighted text – CTRL+X
• Copy the Highlighted text – CTRL+C
• Paste the Highlighted text – CTRL+V
• Select All the Text in the Document – CTRL+A

Change the Highlighted Text to your set Normal Font Style – CTRL+SHIFT+N

• To Change the Font – CTRL+D
• To Insert a Hyperlink – CTRL+K
• To make the Text Bold – CTRL+B
• To make the Text Italic – CTRL+I
• To Underline the Text – CTRL+U
• To Justify the Paragraph – CTRL+J
• To Left Align the Text – CTRL+L
• To Right Align the Text – CTRL+R
• To Centre the Text – CTRL+E

For the minor mistakes that creep into your Document

• To Find something in your Document – CTRL+F
• To Undo – CTRL+Z
• To check your Spelling and Grammar – F7
• To use the Thesaurus – Shift+F7

And if anything goes wrong you can always use

• Help – F1

Quick Zoom, assuming you are using a computer with a mouse and it has a wheel, then you can quickly and easily zoom in and out of a document.

• To Zoom In – CTRL+Scroll up
• To Zoom Out – CTRL+Scroll Down

Excel 2007 Developer Ribbon

To do any sort of VBA coding within Microsoft Excel 2007 it will prove useful to turn on the Developer Ribbon.  You can use Alt+F11 to switch between the VBE and Excel, but having the Developer Ribbon turned on will make life easier.  The Developer Ribbon is like the Visual basic toolbar in Excel 97 through 2003.

To turn on the Developer Ribbon you will need to click the Office button , then select Excel Options, bottom right of the menu.  Within the ‘Popular’ category select the ‘Show Developer tab in the Ribbon’.

Once this is checked you will see the following controls now available within Excel.