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
