Perhaps something like this...

.Range("A5").resize(rngsummary.rows.count, 1).value = rngsummary.value
with rngdept
.Range("B5").resize(.rows.count, .columns.count).value = .value
end with

If all you want is the values you do not need to copy. Just set the values
equal...
--
HTH...

Jim Thomlinson


"RyanH" wrote:

I have a range (rngSummary) on a master worksheet that I need to copy to 17
different other worksheets. On that master worksheet I need to use Union to
combine rngSummary with another range (rngDept) that changes for each of the
17 different departments (worksheets). But I am getting a Run Time Error:
That command cannot beb used on multiple selections. Is there another method
I could used to combine these two ranges?

Sub CompileDepts()

' set the global summary schedule
lngLastRow = wksGlobal.Cells(Rows.Count, "A").End(xlUp).Row
Set rngSummary = wksGlobal.Range("A3:K" & lngLastRow)

' compile depts
intDeptColumn = 20
For Each dept In colDepts

Application.StatusBar = "Compiling " & dept.Name & " Department
Schedule...Please Wait."

Set rngDept = wksGlobal.Range(wksGlobal.Cells(3, intDeptColumn), _
wksGlobal.Cells(lngLastRow,
intDeptColumn + 2))
intDeptColumn = intDeptColumn + 3

' copy entire summary and dept column to dept
ERROR Union(rngSummary, rngDept).Copy

' paste values only
.Range("A5").PasteSpecial Paste:=xlPasteValues
Next dept
End Sub
--
Cheers,
Ryan