help with copy down vba

[kl2190]

hi all,

after copying cell E2 to the last used row in coloum C on sheet "saw list" i want to copy it down to the last used row on that sheet. but i can seem to get the destination right??

LastrowSL = Worksheets("Saw List").UsedRange.Rows.Count
Lastcell = Worksheets("Saw List").Cells(Rows.Count, "C").End(xlUp).Row
Worksheets("Windows").Range("E2").Copy _
Destination:=Worksheets("Saw List").Range("C" & Lastcell + 1)
Worksheets("Saw List").Range("C" & Lastcell + 1).Copy _
Destination:=Worksheets("Saw List").Range("C" & LastrowSL + 1)

 

[circledchicken]

Hi,

Try this (although I'm not sure this is exactly what you want from the question):

With Worksheets("Saw List")

    Lastcell = .Cells(Rows.Count, "C").End(xlUp).Row
    LastrowSL = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
    
    Worksheets("Windows").Range("E2").Copy _
    Destination:=Union(.Range("C" & Lastcell + 1), .Range("C" & LastrowSL + 1))

End With

This copies cell E2 from the 'Windows' sheet. In the 'Saw List' it pastes to the last used row in column C + 1, and also pastes to column C in the last used row of the worksheet + 1.
(Note, if the last used row in the worksheet is also in column C it will paste over itself the second time).

 

[venkat1926]

not completely clear

will not this smaller macro do what you want

Sub testone()
Worksheets("windows").Range("E2").Copy Worksheets("saw list").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
End Sub

 

[kl2190]

sorry i didnt make it clear. but i want it to copy from "windows" sheet to "saw list" sheet the last used row+1 in column C down to the last used row in that sheet and all the cells in between. example if C5 is the last used cell in column C and row 8 is the last used row on the sheet. it will copy to C6,C7,C8

 

[circledchicken]

Hi,

Using venkat1926's suggestion to shorten this, the following should work:

With Worksheets("Saw List")

    Worksheets("Windows").Range("E2").Copy _
    Destination:=.Range(.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0), _
                        .Range("C" & .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row))

End With

You can assign the relevant destination bits to variables and do it that way if it makes more sense. Also note that you might not have to use the copy method depending on what you want to achieve - e.g. if your just copying the value you can do this directly i.e.:

With Worksheets("Saw List")

.Range(.Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0), _
.Range("C" & .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row)) = _
    Worksheets("Windows").Range("E2")

End With