Copy only part of last row that has data

[Nachtschade]

I have this code:

Range("A" & Rows.Count).End(xlUp).EntireRow.Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.PasteSpecial

It copies the entire last row that contains data and pastes it one row below.

I only want it to copy part of the row, columns A to I to be exact. How do I edit this?

 

[PCL]

See next code ... NOT TESTED

Cells(rows.count,"A").end(3).resize(1,9).Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.PasteSpecial

 

[mrshl9898]

Try:

Dim lastrow As Long
Dim sht As Worksheet


Set sht = ActiveSheet


lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


Range("A" & lastrow & ":I" & lastrow).Copy
Range("A" & lastrow + 1).PasteSpecial

 

[PCL]

a smarter one

    With Cells(Rows.Count, "A").End(3)
        .Resize(1, 9).Copy
        .Offset(1).PasteSpecial
    End With

 

[Nachtschade]

See next code ... NOT TESTED

Cells(rows.count,"A").end(3).resize(1,9).Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.PasteSpecial

Thanks a lot.

 

[Nachtschade]

I also have another question. Not sure if I should post that here or make a new topic but anyway..

I want to copy an entire column* with offset where the offset is x columns to the right and x is a cell value. The paste needs to be as value.

*Where I say entire column what I actually mean is only the cells in that contain a value in that column (there's always exactly 40 cells with a value).

 

[PCL]

another question

means new thread
and

with offset where the offset is x columns to the right and x is a cell value.

give more details:
where is x ?

 

[Nachtschade]

means new thread
and
give more details:
where is x ?

X could be in any cell.