One benefit of modular code – the ease of reuse

The primary reason I write modular code is that it is self-documenting, easy to understand, and easy to maintain.  A secondary reason is the ease of reuse.

One of the comments to my post Two new range functions: Union and Subtract (www.dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/) was a request for code to copy a range from one worksheet to another with certain ranges excluded.  While I agree with DK that there's no need to get fancy with something that is used once a month, I couldn't pass up the opportunity to illustrate the ease of reuse of modular code.

For the UI I defined the following: Select 6 cells that specify various pieces of information and run the CopyWithExclusions macro.

The code goes into a standard module.  Start by copying from the above-referenced post the Subtract function together with the associated SubtractOneArea and Union functions.  Add the new subroutine copyWithExclusions (see below) and…well, that’s it.  We are done.

Note that the code has been lightly -- very lightly -- tested.

Sub copyWithExclusions()

    Dim SrcWB As Workbook, DestWB As Workbook, _

        SrcWS As Worksheet, DestWS As Worksheet, _

        SrcRng As Range, ExcludeRng As Range

    With Selection

    Set SrcWB = Workbooks(.Cells(1).Value)

    Set DestWB = Workbooks(.Cells(2).Value)

    Set SrcWS = SrcWB.Worksheets(.Cells(3).Value)

    Set DestWS = DestWB.Worksheets(.Cells(4).Value)

    Set SrcRng = SrcWS.Range(.Cells(5).Value)

    On Error Resume Next

    Set ExcludeRng = SrcWS.Range(.Cells(6).Value)

    On Error GoTo 0

        End With

    If Not ExcludeRng Is Nothing Then _

        Set SrcRng = Subtract(SrcRng, ExcludeRng)

    Dim anArea As Range

    For Each anArea In SrcRng.Areas

        anArea.Copy

        DestWS.Range(anArea.Address).PasteSpecial _

            xlPasteValuesAndNumberFormats

        Next anArea

    End Sub