Copying a Range When Rows Are Long Variables

[Guest]

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!

 

[Guest]

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.

 

[Guest]

hmm..... this isn't quite giving me the result i am looking for.

The copy seems to work as I see all of the cells selected but it never
pastes anything. I will need it to paste to the next empty cell in the
destination worksheet.

I appreciate your time!