Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by Seamus2626
at 2024-07-30 08:36:40
ModifyPoint:500 Replies:6 POST_ID:829171USER_ID:11690
Topic:
Microsoft Excel Spreadsheet Software;;
Hi,
I need an amendment to the following piece of code
The copy is a regular paste, i need a paste special
Many thanks
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 VariantDim arrTransfer As VariantLastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).RowarrData = 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 IfElse Sheets("Add Delete Team Members").Range("N15:W17").Copy Sheets("Change Summary").Range("A" & LastTransferRow + 1)End IfEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:
Author: Seamus2626 replied at 2024-07-30 09:19:56
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
Put that line into my code where you would put it and i will run
Thanks
Accepted Solution
Expert: duncanb7 replied at 2024-07-30 09:10:34
500 points EXCELLENT
You need to the put the range and sheet you want with this PasteSpeical with values only
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteValues
Author: Seamus2626 replied at 2024-07-30 09:07:00
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
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
Expert: duncanb7 replied at 2024-07-30 08:53:04
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
duncan
Author: Seamus2626 replied at 2024-07-30 08:49:46
Hi Duncan,
I dropped that in and got the error message
"Invalid use of property" on line xlPasteValues
I dropped that in and got the error message
"Invalid use of property" on line xlPasteValues
Sub New_Staff()Dim arrData As VariantDim arrTransfer As VariantLastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).RowarrData = 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 IfElse Sheets("Add Delete Team Members").Range("N15:W17").Copy Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial xlPasteValuesEnd IfEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:
Expert: duncanb7 replied at 2024-07-30 08:42:48
You can try this,
Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial xlPasteValues
OR
Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial xlPasteFormats
Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial xlPasteValues
OR
Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial xlPasteFormats
