PDA

View Full Version : Problem with format and copy values



Nader
02-20-2008, 06:55 AM
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.


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)


How to fix this problem ?

MikeO
02-20-2008, 12:02 PM
Use this instead:


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

Nader
02-20-2008, 05:21 PM
Thank you
Why the property End(xlUp)?

mdmackillop
02-20-2008, 05:34 PM
Hi Mike
Better to use

Sheets(1).Range("A24:A1000").Copy Worksheets(lblagent.Caption).Cells(Rows.Count, 1).End(xlUp).Offset(2)

to ensure compatability with Excel2007

mdmackillop
02-20-2008, 05:36 PM
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
02-21-2008, 06:49 AM
Thank you for help