CODEWITHSUNDEEP
excelvbacopy-paste
Rossco Stringer
Rossco Stringer

Reputation: 11

Unable to get my code to execute paste special

I have a small VBA code to copy a row from one sheet and paste to another, it works fine for paste but not for paste special, as I am trying to paste values only and not just paste.

this is my code, very basic. Noted that the pastespecial is changed to paste the code works fine.

thanks for you help

Private Sub CommandButton1_Click()

    a = Worksheets("Inventory List Costing Review").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 10 To a

        If Worksheets("Inventory List Costing Review").Cells(i, 19).Value = "Completed" Then

            Worksheets("Inventory List Costing Review").Rows(i).Copy
            Worksheets("Completed by Sales").Activate
            b = Worksheets("Completed by Sales").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Completed by Sales").Cells(b + 1, 1).Select
            ActiveSheet.PasteSpecial Paste:=xlPasteValues, operation:=xlNone
            Worksheets("Inventory List Costing Review").Activate

        End If

    Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Inventory List Costing Review").Cells(1, 1).Select

End Sub

Upvotes: 1

Views: 76

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

PasteSpecial xlPasteValues vs Assigning Values

A Quick Fix

If you insist on using PasteSpecial, in the IF clause you can use:

Worksheets("Inventory List Costing Review").Rows(i).Copy
b = Worksheets("Completed by Sales").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Completed by Sales").Cells(b + 1, 1).PasteSpecial Paste:=xlPasteValues

But a better (more efficient) way is:

b = Worksheets("Completed by Sales").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Completed by Sales").Rows(b + 1).Value = _
  Worksheets("Inventory List Costing Review").Rows(i).Value

when Application.CutCopyMode = False and ... Cells(1, 1).Select are not needed anymore.

Improvements

  • If you use Option Explicit, it will 'force' you to qualify all variables (a, b).
  • If you additionally qualify the workbook and worksheets, the code becomes quite readable.
  • Since the code can be run from a command button on any sheet, you can give it a suitable name and put it into a standard module. Then you can easily call it in the click event code of a command button (located in a sheet module).

Standard Module e.g. Module1

Option Explicit

Sub updateSales()
    
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    Dim src As Worksheet
    Set src = wb.Worksheets("Inventory List Costing Review")
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets("Completed by Sales")
    
    Dim a As Long
    Dim b As Long
    
    a = src.Cells(src.Rows.Count, 1).End(xlUp).Row
    
    For i = 10 To a
        If src.Cells(i, 19).Value = "Completed" Then
            b = tgt.Cells(tgt.Rows.Count, 1).End(xlUp).Row
            tgt.Rows(b + 1).Value = src.Rows(i).Value
        End If
    Next

End Sub

Sheet Module e.g. Inventory List Costing Review and/or Completed by Sales

Option Explicit

Private Sub CommandButton1_Click()
    updateSales
End Sub

Upvotes: 3

Slaqr
Slaqr

Reputation: 563

The following should work (cleaned up a bit of clutter along the way). Although if you're just copying data from cells it would be faster to assign the values directly to the destination cells instead of copy-pasting.

Private Sub CommandButton1_Click()

With Worksheets("Inventory List Costing Review")
    a = .Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 10 To a
        If .Cells(i, 19).Value = "Completed" Then
        
            .Rows(i).Copy
            b = Worksheets("Completed by Sales").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Completed by Sales").Cells(b + 1, 1).PasteSpecial Paste:=xlPasteValues, operation:=xlNone
        End If
    Next

    Application.CutCopyMode = False
    .Cells(1, 1).Select
End With

End Sub

Upvotes: 0

Related Questions