Error using Union in a Loop

[RyanH]

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

 

[Jim Thomlinson]

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...

 

[Joel]

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]

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?

 

[Jim Thomlinson]

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.

 

[RyanH]

Looking at the Object Browser I figured so, but wasn't sure if it could be
used another way. Thanks!

 

[Jim Thomlinson]

Not to pick but declare your variables... it makes it a lot easier for us to
debug code if the variables are declared. Most of us around these parts use
option explicit so when you don't declare your variables we have to comment
out that line and then double check all of your spelling... your code is far
too good for us to have to worry about rookie stuff like that... that's my
rant for the day... ;-)