• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 103
  • Last Modified:

Amend Transfer code

Hi,

I need an amendment to the following piece of code

The copy is a regular paste, i need a paste special

Many thanks

Sub macro()
Dim arrData As Variant
Dim arrTransfer As Variant
LastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).Row
arrData = Join(WorksheetFunction.Index(Sheets("Add Delete Team Members").Range("N15:W17").Value, 1, 0), ";")
arrTransfer = Join(WorksheetFunction.Index(Sheets("Change Summary").Range("N" & LastTransferRow & ":A" & LastTransferRow).Value, 1, 0), ";")
If arrData = arrTransfer Then
    Res = MsgBox("This is the same data, are you sure you want to send", vbYesNo)
    If Res = vbYes Then
        Sheets("Add Delete Team Members").Range("N15:W17").Copy Sheets("Change Summary").Range("A" & LastTransferRow + 1)
    Else
        Exit Sub
    End If
Else
    Sheets("Add Delete Team Members").Range("N15:W17").Copy Sheets("Change Summary").Range("A" & LastTransferRow + 1)
End If
End Sub

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
1 Solution
 
duncanb7Commented:
You can try this,

Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial   xlPasteValues  

OR

Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial    xlPasteFormats
0
 
Seamus2626Author Commented:
Hi Duncan,

I dropped that in and got the error message

"Invalid use of property" on line xlPasteValues

Sub New_Staff()
Dim arrData As Variant
Dim arrTransfer As Variant
LastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).Row
arrData = Join(WorksheetFunction.Index(Sheets("Add Delete Team Members").Range("N15:W17").Value, 1, 0), ";")
arrTransfer = Join(WorksheetFunction.Index(Sheets("Change Summary").Range("N" & LastTransferRow & ":A" & LastTransferRow).Value, 1, 0), ";")
If arrData = arrTransfer Then
    Res = MsgBox("This is the same data, are you sure you want to send", vbYesNo)
    If Res = vbYes Then
        Sheets("Add Delete Team Members").Range("N15:W17").Copy Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial
        xlPasteValues


    Else
        Exit Sub
    End If
Else
    Sheets("Add Delete Team Members").Range("N15:W17").Copy Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial
    xlPasteValues
End If
End Sub

Open in new window

0
 
duncanb7Commented:
Could you  write your question in detail , what you want which sheet which range or cell to get pasteSpecial and what format for the paste you want ?

duncan
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Seamus2626Author Commented:
Okay,

So my code picks up range N15:W17 from tab "Add Delete Team Members" and pastes over to tab "Change Summary", it pastes to the next available row after the headings (A1:L1)

When it pastes, its pastes with formats, i do not want the formats, i only want the values

Thanks
0
 
duncanb7Commented:
You need to the put the range and sheet  you want with this PasteSpeical with values only

.PasteSpecial   xlPasteValues
0
 
Seamus2626Author Commented:
Thanks Duncan but It does not work on this code

Put that line into my code where you would put it and i will run

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now