I have to copy the contents of some cells from an external spreadsheet
to another (in the current Workbook). The range is e.g. all data in the
cells of the row 2-4 in all of the columns.

What is the best way to do this?

My current code is something like this

Application.ScreenUpdating = False
' path set to some workbook
Set wb = Workbooks.Open(sPath)
Dim lng As Long
Dim i As Integer
With ThisWorkbook.Worksheets("Sheet1")
' sSheet points to a specific Spreadsheet in the external Workbook
For lng = 1 To wb.Worksheets(sSheet).Columns.Count
For i = 2 To 4
.Cells(10 + i, lng).Value = wb.Worksheets(sSheet).Cells(i, lng).Value
' copy some of the formatting as well.
.Cells(10 + i, lng).Interior.ColorIndex = wb.Worksheets(sSheet).Cells(i, lng).Interior.ColorIndex
.Cells(10 + i, lng).Font.Size = wb.Worksheets(sSheet).Cells(i, lng).Font.Size
Next
Next
End With

This copies all data in row 2-4 in all columns in the external
spreadsheet.

Howver, I have a feeling that there is a better way to do it. My current
problem is that I need all the formatting as well, that is
text-orientation as well as row height and column width.

Also, the source spreadsheet in the external Workbook has data in
something like the first 250 rows. How do I copy these rows only to
my target spreadsheet? If I try

wb.WorkSheets(sSheet).Rows.Count

, I get 65536 ... which is a bit more than I need :)

I hope you guys can help me with this one ...

--
Jesper Stocholm - http://stocholm.dk
The Web submission form is the preferred procedure. However, if you don't
have access to the Internet you may send your submission by e-mail.
(Nyhedsbrev fra 9th Conference on Reliable Software Technologies)