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