CODEWITHSUNDEEP
vbaexcel
user1564251
user1564251

Reputation: 1

Excel - VBA Object does not support this property or method - Paste - Excel

the line that is giving me trouble is ""Sheets(CStr(WS_M.Cells(n, START_C))).Cells(n, START_C).Paste""

this is supposed to find the tab name in column 3 and go to that tab and paste the tab name in that tab. 

    Const START_C = 3
    Const MAX_TRAN = 1000
    Const START_R = 2
    Const MASTER = "MASTER"

    Sub MOVEDATACORRECTLY()

    Dim WS_M As Worksheet
    Dim thisWB As Workbook
    Set thisWB = ActiveWorkbook
    Set WS_M = Worksheets(MASTER)

    For M = START_R To (START_R + MAX_TRAN)
        If WS_M.Cells(M, (START_C + 1)) = "" Then Exit For

    Next M

    M = M - 1

    For n = START_R To M
       WS_M.Cells(n, START_C).Copy
       Sheets(CStr(WS_M.Cells(n, START_C))).Cells(n, START_C).Paste      

    Next n

    End Sub

Upvotes: 0

Views: 4868

Answers (2)

Cylian
Cylian

Reputation: 11182

For copying a range of cells in Excel, using Copy method makes the VBA program easier to crash / or to give inpredictable results.

Suppose during the your procedure copies data from system clipboard and user was trying to store some other to system clipboard!

Not always, but from users this kind of mistake might happened.

So I always prefer to use a better approach, something like Swaping the range on the fly. Here's a small demonstration:

Public Sub Sample_Copy_without_Clipboard()
    Dim dRange As Range, iRange As Range
    Set iRange = Range("A1:B3")
    Set dRange = Range("D1:E3")
    dRange.Value = iRange.Value
End Sub

Note: This method works only with unformatted textual data. If not then either use Tim's suggestion or DanM's answer.

Upvotes: 1

devuxer
devuxer

Reputation: 42374

Try this instead:

For n = START_R To M
       WS_M.Cells(n, START_C).Copy
       Sheets(CStr(WS_M.Cells(n, START_C))).Cells(n, START_C).Select
       ActiveSheet.Paste
Next n

If you look at the documentation for the Excel Range object, Paste is not in the list of members. There is PasteSpecial, however. I haven't experimented with that, but that might also work.

Upvotes: 1

Related Questions