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-31 01:21:53
ModifyPoint:500 Replies:11 POST_ID:829181USER_ID:11690
Topic:
Microsoft Excel Spreadsheet Software;;
If Range("Y15") = 1 Then
Sheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1) PasteSpecial Paste:=xlPasteValues
This line of code has incorrect syntax, can someone suggest new syntax or a new line of code to transfer the range from one sheet to another using paste special
Thanks
Sheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1) PasteSpecial Paste:=xlPasteValues
This line of code has incorrect syntax, can someone suggest new syntax or a new line of code to transfer the range from one sheet to another using paste special
Thanks
Expert: duncanb7 replied at 2024-07-31 03:32:40
Thanks for your points
Have a nice day
Duncan
Have a nice day
Duncan
Author: Seamus2626 replied at 2024-07-31 03:27:21
Perfect, thanks for your help Duncan!
Accepted Solution
Expert: duncanb7 replied at 2024-07-31 02:23:58
500 points EXCELLENT
be reminded, your method to use If then elseif else endif
look at Microsoft VBA manual at http://msdn.microsoft.com/en-us/library/752y8abs.aspx
try this your original idea as following simplify code , but it is strange you use
If Then
Else
Endif
Duncan
look at Microsoft VBA manual at http://msdn.microsoft.com/en-us/library/752y8abs.aspx
try this your original idea as following simplify code , but it is strange you use
If Then
Else
Endif
Duncan
Sub TransferNewData()Sheets("Change Summary").SelectSet cs = Sheets("Change Summary")Set adtm = Sheets("Add Delete Team Members")lastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).RowWith adtmIf .Range("Y15") = 1 Then.Range("N15:W15").Copycs.Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValuesElseEnd IfIf .Range("Y16") = 1 Then.Range("N16:W16").Copycs.Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValuesElseEnd IfIf .Range("Y17") = 1 Then.Range("N17:W17").Copycs.Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValuesElseEnd IfEnd WithEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:
Expert: duncanb7 replied at 2024-07-31 02:18:46
typing error at on around Y15 ,lastTransferRow , it should be lastTransferRow+1 om my code
Duncan
Duncan
Author: Seamus2626 replied at 2024-07-31 02:10:06
Il amend and try, thanks!
Author: Seamus2626 replied at 2024-07-31 02:09:47
Y15 etc is on the "add delete team members" sheet
Expert: duncanb7 replied at 2024-07-31 01:58:22
Y15,Y16,Y17 , is it on Change Summary sheet,right ?
Try this
Duncan
Try this
Duncan
Sub TransferNewData()Sheets("Change Summary").SelectlastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).RowIf Sheets("Change Summary").Range("Y15") = 1 ThenSheets("Add Delete Team Members").Range("N15:W15").CopySheets("Change Summary").Range("A" & lastTransferRow).PasteSpecial Paste:=xlPasteValuesElseEnd IfIf Sheets("Change Summary").Range("Y16") = 1 ThenSheets("Add Delete Team Members").Range("N16:W16").CopySheets("Change Summary").Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValuesElseEnd IfIf Sheets("Change Summary").Range("Y17") = 1 ThenSheets("Add Delete Team Members").Range("N17:W17").CopySheets("Change Summary").Range("A" & lastTransferRow + 1).PasteSpecial Paste:=xlPasteValuesElseEnd If'Sheets("Change Summary").Range("Y" & lastTransferRow).SelectEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:
Author: Seamus2626 replied at 2024-07-31 01:43:45
Ya, i dont mind, if you could plug it in and it works, id be very happy!!
Thanks
Thanks
Expert: duncanb7 replied at 2024-07-31 01:42:28
Could you use two lines instead of one line ?
for example,
.copy
.PasteSpecial paste:=xlpastevalues
If so, it will be easier
Duncan
for example,
.copy
.PasteSpecial paste:=xlpastevalues
If so, it will be easier
Duncan
Author: Seamus2626 replied at 2024-07-31 01:40:12
Hi Duncan,
Below is my sub, with your line added, im getting the error message
"Unable to get the pastespecial property of the range class"
Thanks
Below is my sub, with your line added, im getting the error message
"Unable to get the pastespecial property of the range class"
Thanks
Sub TransferNewData()LastTransferRow = Sheets("Change Summary").Range("A" & Rows.Count).End(xlUp).RowIf Range("Y15") = 1 ThenSheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)ElseEnd IfIf Range("Y16") = 1 ThenSheets("Add Delete Team Members").Range("N16:W16").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)ElseEnd IfIf Range("Y17") = 1 ThenSheets("Add Delete Team Members").Range("N17:W17").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues)ElseEnd IfEnd Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:
Expert: duncanb7 replied at 2024-07-31 01:32:37
Did you do .select and .activate the sheet ? Just reminder
Try this,
Try this,
Sheets("Add Delete Team Members").Range("N15:W15").Copy Destination:=Sheets("Change Summary").Range("A" & LastTransferRow + 1).PasteSpecial(xlPasteValues) 1:
Duncan
