Problem with VBA code

[gheyman]

This filters and copies then errors where it should be pasting.

Sheets("CMOPUpload").Range("B" & lstrw).Paste

Private Sub CommandButton1_Click()

'Dim CMOP As Worksheet
Dim lstrw As Long

'Set CMOP = Worksheets("Sheet9")

Set lstwr = Sheets("CMOPUpload").Range("B" & Rows.Count).End(xlUp).Offset(1)

ActiveSheet.Range("A4").AutoFilter Field:=6, Criteria1:="Yes", Operator:=xlFilterValues
 
    Range("A3").Select
    ActiveCell.Offset(1, 0).Select
    
    Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy
    
    Sheets("CMOPUpload").Range("B" & lstrw).Paste
    

    Worksheets("ItemIDList").AutoFilterMode = False
    Application.CutCopyMode = False


End Sub

 

[RoryA]

Use PasteSpecial - the Range object doesn't have a Paste method.

 

[steve the fish]

You have some errors:

Set lstwr = Sheets("CMOPUpload").Range("B" & Rows.Count).End(xlUp).Offset(1)

needs to be

lst[COLOR=#ff0000]rw[/COLOR] = Sheets("CMOPUpload").Range("B" & Rows.Count).End(xlUp).Offset(1)[COLOR=#ff0000].Row[/COLOR]

then you can replace this:

Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy
Sheets("CMOPUpload").Range("B" & lstrw).Paste

with either:

Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy Sheets("CMOPUpload").Range("B" & lstrw)

or

Range(Selection, Selection.End(xlDown).Offset(0, 4)).Copy
Sheets("CMOPUpload").Range("B" & lstrw).PasteSpecial xlPasteValues

See if that works.

 

[footoo]

In your code, you haven't assigned a row number to lstrw

Also, "Set lstwr" ?

 

[footoo]

Private Sub CommandButton1_Click()


'Dim CMOP As Worksheet
Dim lstrw As Range


'Set CMOP = Worksheets("Sheet9")


Set lstrw = Sheets("CMOPUpload").Range("B" & Rows.Count).End(xlUp).Offset(1)


ActiveSheet.Range("A4").AutoFilter Field:=6, Criteria1:="Yes", Operator:=xlFilterValues


Range([A4], [A4].End(xlDown).Offset(0, 4)).Copy lastrw


Worksheets("ItemIDList").AutoFilterMode = False


End Sub

 

[gheyman]

THANKS I really appreciate the help.