Problem finding ranges without using Selection

[frippe123]

Hi, I've some issues when copying a range I don't know how large it is. Earlier I have been using .select and .copy but this gets very slow if you do it alot. There are two types of ranges I want to copy. The first one is only one column in width and the other one the width of the range is unknown.

The first one I'm trying to use this code:

Sheets(strSheetNew).Range("StartCell") = Sheets(strSheetOld).Range("b5", Range("B5").End(xlDown)).Value

The error I get is "Application-Defined or Object-Defined error". Is it impossible to do it this way?

The second one I've tried to use Cell.find but I can't put it together. What would be the best way in copying an unknown range (rows and columns) similar to the first example?

Best regards
Frippe

[Bob Phillips]

With Worksheets(strSheetOld)

lastrow = .Range("B5").End(xlDown).Row
lastcol = .Range("B5").End(xlToRight).Column
.Range("B5").Resize(lastrow - 4, lastcol - 1).Copy _
Worksheets(strSheetNew).Range("StartCell")
End With

[frippe123]

Excellent, thank you very much.





With Worksheets(strSheetOld)

lastrow = .Range("B5").End(xlDown).Row
lastcol = .Range("B5").End(xlToRight).Column
.Range("B5").Resize(lastrow - 4, lastcol - 1).Copy _
Worksheets(strSheetNew).Range("StartCell")
End With