Problem with format and copy values

[Nader]

This code is to copy the values from sheet1 to sheet(bills) values after values and left between each values one row.
But when I change the format ( the font or the size of font or bold?) of sheet("bills") it doesn?t work well .it copy the first values and left between them about 900 rows.

[vba]

Dim j As Integer
j = Worksheets(lblagent.Caption).UsedRange.Rows.Count + 1 + Worksheets(lblagent.Caption).UsedRange.Row

Sheets(1).Range("A24:A1000").Copy Worksheets(lblagent.Caption).Range("a" & j)

[/vba]

How to fix this problem ?

[MikeO]

Use this instead:

Sheets(1).Range("A24:A1000").Copy Worksheets(lblagent.Caption).Range("a65536").End(xlUp).Offset(2)

[Nader]

Thank you
Why the property End(xlUp)?

[mdmackillop]

Hi Mike
Better to use
[VBA]
Sheets(1).Range("A24:A1000").Copy Worksheets(lblagent.Caption).Cells(Rows.Count, 1).End(xlUp).Offset(2)
[/VBA]
to ensure compatability with Excel2007

[mdmackillop]

Cells(Rows.Count, 1).End(xlUp) gives the same result as selecting the last cell in column A and pressing Control + Up Arrow. Offset(2) uses the cell 2 cells below the last used cell in the column.

[Nader]

Thank you for help