Union is a method of the application and not of the worksheet. Union returns
a range object which is by default part of a single worksheet.
--
HTH...

Jim Thomlinson


"RyanH" wrote:

Thats for the Replys guys!
Jim, I don't think I could use the resize method here, but I can just make
Range1.Value = Range2.Value I don't know why I din't think of that instead of
copying and pasting.

Joel, I am getting an Error when I put wksGlobal in front of Union. Error
Object does not support this method. Any ideas?
--
Cheers,
Ryan


"Joel" wrote:

Union only works on a single worksheet. without specifying the worksheet
will give an error. Try this

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

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


"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