set rng = ws.Range(ws.cells(TopRowPos,2),ws.Cells(BottmRowPos,2))
rng.copy
or
rng.Resize(,10).copy to copy 10 columns wide as an example - adjust to
fit your needs.
--
Regards,
Tom Ogilvy
"ALATL" wrote:
> I have the following code that does the following:
>
> *Sorts data according to one column B
> *While the SOB values are all equal in Column B, it goes through data and
> identifies the bottom and top position of each row and stores it in a Long
> TopRowPos and Long BottomRowPos variable.
>
> I would like to copy that range of data in TopRowPos and Long BottomRowPos
> but can't because it is not a range value. There is a type mismatch between
> the long & range variables and I can't assign them.
>
> Does anyone have any suggestions?
>
>
> Dim BottomRowPos As Long
> Dim TopRowPos As Long
> Dim i As Long
> Dim SOB As String
> Dim Same As Boolean
> Dim WS As Worksheet
> Dim TopRange As Range
> Dim BottomRange As Range
>
>
> 'Sort data by Set of Books
> Set WS = Worksheets("Sheet1")
>
> Cells.Select
> Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> 'Identify Range for Each Set of Books
> BottomRowPos = WS.Range("B65536").End(xlUp).Row
>
> While BottomRowPos > 1
> TopRowPos = BottomRowPos
>
> 'While the SOB values are equal
> While WS.Cells(TopRowPos - 1, 2) = WS.Cells(BottomRowPos, 2)
> TopRowPos = TopRowPos - 1
> Wend
>
> Same = True
>
> 'Set SOB to BottomRowPos
> SOB = WS.Cells(BottomRowPos, 2)
>
> For i = TopRowPos To BottomRowPos
> If WS.Cells(i, 2) <> SOB Then Same = False
> If Not Same Then i = BottomRowPos
> Next i
>
> 'Executes if Same is True. Copy range of SOB to new worksheet
> **This is where I need help**
> **I have a top & bottom row position but i don't know how to tell it to copy
> & paste to a new worksheet**
>
> Thx!
|
Similar Threads
