I have two different ranges that I want to combine as one, copy then paste to
another worksheet. My current code copies the Range("A3:T" & lngLastRow),
why?
I want to leave out the columns between Col.K and Col.T.

Dim CopyPasteRanges()

Dim lngLastRow As Long
Dim rngSummary As Range
Dim rngDept as Range

lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set rngSummary = Sheets("Sheet1").Range("A3:K" & lngLastRow)
Set rngDept = Sheets("Sheet1").Range("T3:T" & lngLastRow))

' copy entire summary and dept column to dept
Range(Union(rngSummary, rngDept)).Copy
Destination:=Sheets("Sheet2").Range("A5")

End Sub

--
Cheers,
Ryan

Hello Ryan,
Ron de Bruin has some great stuff along the lines of what you are
trying to do, see: Copy/Paste/Merge examples.
http://www.rondebruin.nl/tips.htm


HTH--Lonnie M.

Hi Ryan

Remove the Range statement before "Union":

Union(rngSummary, rngDept).Copy Destination:=Sheets("Sheet2").Range("A5")

Regards,
Per


"RyanH" <(E-Mail Removed)> skrev i meddelelsen
news:623A38A6-B2E7-4FB4-A08D-(E-Mail Removed)...
>I have two different ranges that I want to combine as one, copy then paste
>to
> another worksheet. My current code copies the Range("A3:T" & lngLastRow),
> why?
> I want to leave out the columns between Col.K and Col.T.
>
> Dim CopyPasteRanges()
>
> Dim lngLastRow As Long
> Dim rngSummary As Range
> Dim rngDept as Range
>
> lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
> Set rngSummary = Sheets("Sheet1").Range("A3:K" & lngLastRow)
> Set rngDept = Sheets("Sheet1").Range("T3:T" & lngLastRow))
>
> ' copy entire summary and dept column to dept
> Range(Union(rngSummary, rngDept)).Copy
> Destination:=Sheets("Sheet2").Range("A5")
>
> End Sub
>
> --
> Cheers,
> Ryan

Thanks Per! Thats what I needed.
--
Cheers,
Ryan


"Per Jessen" wrote:

> Hi Ryan
>
> Remove the Range statement before "Union":
>
> Union(rngSummary, rngDept).Copy Destination:=Sheets("Sheet2").Range("A5")
>
> Regards,
> Per
>
>
> "RyanH" <(E-Mail Removed)> skrev i meddelelsen
> news:623A38A6-B2E7-4FB4-A08D-(E-Mail Removed)...
> >I have two different ranges that I want to combine as one, copy then paste
> >to
> > another worksheet. My current code copies the Range("A3:T" & lngLastRow),
> > why?
> > I want to leave out the columns between Col.K and Col.T.
> >
> > Dim CopyPasteRanges()
> >
> > Dim lngLastRow As Long
> > Dim rngSummary As Range
> > Dim rngDept as Range
> >
> > lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
> > Set rngSummary = Sheets("Sheet1").Range("A3:K" & lngLastRow)
> > Set rngDept = Sheets("Sheet1").Range("T3:T" & lngLastRow))
> >
> > ' copy entire summary and dept column to dept
> > Range(Union(rngSummary, rngDept)).Copy
> > Destination:=Sheets("Sheet2").Range("A5")
> >
> > End Sub
> >
> > --
> > Cheers,
> > Ryan
>
>