I have a script in where it copies a sheet's data, and pastes it, but over pastes...

[Cyberpawz]

Here is my script.

Sub Update_OOR()
    
    Dim wsTNO As Worksheet
    Dim wsTND As Worksheet
    Dim wsTNA As Worksheet
    Dim lastrow As Long, fstcell As Long
    
    Set wsTNO = Sheets("Tel-Nexx OOR")
    Set wsTND = Sheets("Tel-Nexx Data")
    Set wsTNA = Sheets("Tel-Nexx Archive")
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With

    With Intersect(wsTNO.UsedRange, wsTNO.Columns("S"))
        .AutoFilter 1, "<>Same"
        With Intersect(.Offset(2).EntireRow, .Parent.Range("B:P"))
            .Copy wsTNA.Cells(Rows.Count, "B").End(xlUp).Offset(1)
            .EntireRow.Delete
        End With
        .AutoFilter
    End With


'Blow away rows that are useless
    lastrow = wsTND.Range("A2").End(xlDown).Row
    wsTND.Range("O1:P1").Copy wsTND.Range("O2:P" & lastrow)
    wsTND.UsedRange.Copy Sheets.Add.Range("A1")
    
    With Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns("P"))
        ActiveSheet.Range("O:P").Calculate
        .AutoFilter 1, "<>Different"
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    
    With ActiveSheet
        lastrow = wsTND.Range("A2").End(xlDown).Row
        Intersect(.UsedRange, .Range("A2:M" & lastrow)).Copy wsTNO.Cells(Rows.Count, "B").End(xlUp).Offset(1)
        .Delete
    End With
    
    With wsTNO
        lastrow = wsTNO.Cells(Rows.Count, "B").End(xlUp).Row
        wsTNO.Range("T1:AD1").Copy
        wsTNO.Range("B3:N" & lastrow).PasteSpecial xlPasteFormats
        lastrow = wsTNO.Cells(Rows.Count, "R").End(xlUp).Row
        fstcell = wsTNO.Cells(Rows.Count, "N").End(xlUp).Row
        wsTNO.Range("AE1:AI1").Copy wsTNO.Range("O" & fstcell & ":S" & lastrow).Offset(1, 0)
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    
End Sub

It technically works perfectly till here:

With wsTNO
        lastrow = wsTNO.Cells(Rows.Count, "B").End(xlUp).Row
        wsTNO.Range("T1:AD1").Copy
        wsTNO.Range("B3:N" & lastrow).PasteSpecial xlPasteFormats
        lastrow = wsTNO.Cells(Rows.Count, "R").End(xlUp).Row
        fstcell = wsTNO.Cells(Rows.Count, "N").End(xlUp).Row
        wsTNO.Range("AE1:AI1").Copy wsTNO.Range("O" & fstcell & ":S" & lastrow).Offset(1, 0)
    End With

Now technically everything in this part works correctly, but the last line in the code, it pasts everything correctly, then it goes one step beyond. I'd like to know why. If I get rid of the offset it overwrites what is in the cell above in O through S. I need to know the first and last cell, because the data needs to be only written to a specific cell range.

If there is an easier way of doing this it be appreciated if someone could tell me, if not then can someone tell me how to fix this?

Thanks.

Attached is a workbook.

http://dl.dropbox.com/u/3327208/Exce...6LastRows.xlsm

[wallyeye]

Have you tried:

wsTNO.Range("AE1:AI1").Copy wsTNO.Range("O" & fstcell + 1 & ":S" & lastrow)

[royUK]

Wallyeye

Use code tags for all code in posts