paste special: value

DNS SHANGHAI

Board Regular
Joined
Mar 5, 2004
Messages
60
SLIPPERS TEST 2 W PASTEVALUE.xls
BCDEFGHIJKLMNOPQRSTUVWXY
2MODELDESCRIPTIONTYPESIZESCOLORSPACKPERCTNCTNSIZECTNWEIGHT
3CODENUMBEROFMODEL123456123456PRSDZNLBHCBMNWGW
411116050.230.230.230.011214
522220 
633330 
74440 
855550 
966660 
107770 
110 
DATABASE

Hi Masters of the Board,
I have this problem.
I have the code from Dan to copy from DATABASE TO WORKSHEET.
from B:Y to worksheet Col A:X
Then Copy paste special Database Col Z:AE to WORKSHEET Col Y:AD.
The problem is that I need to paste only value for the Copy paste special and not the formula.
My code is pasted below.
It tells me that it is error whenever I typed something in Database cheet.
Please help.
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then Exit Sub
If Target.Column<> 32 Then Exit Sub
If Target.Column = 32 Then
MyRow = ActiveCell.Row
Range("B" & MyRow & ":Y" & MyRow).Copy Sheets("WORKSHEET").Range("A65536").
Range("z" & MyRow & ":AE" & MyRow).Copy: PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Copy Sheets("WORKSHEET").Range(Z65536"). End(xlUp).Offset(1)
End If
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
The code you show is complete garbage I am afraid. I wonder if you copied it correctly. The best method is to select the text in the message and use keys Ctrl+C to copy, and Ctrl+V to paste into a macro sheet. Not 100% sure what you are trying to achieve. One good method of asking a question is to record a macro of your actions and then copy/paste the code into a message saying why it is not doing exactly what you want.

The code is also bad in that it is not explicit in describing which workbooks/worksheets are being used. It may be that you are copying from and pasting into the same sheet, but it seems unlikely. You will need to change the workbook/sheet names in following code :-

'====================================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then Exit Sub
' If Target.Column <> 32 Then Exit Sub ' not required in this context
'-
If Target.Column = 32 Then
MyRow = ActiveCell.Row
'--------------------------------
Workbooks("Book1.xls").Worksheets("Sheet1").Range("B" & MyRow & ":Y" & MyRow).Copy
Workbooks("Book1.xls").Worksheets("Sheet2").Range("A" & MyRow).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'--------------------------------
Workbooks("Book1.xls").Worksheets("Sheet1").Range("Z" & MyRow & ":AE" & MyRow).Copy
Workbooks("Book1.xls").Worksheets("Sheet2").Range("Y" & MyRow).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'--------------------------------
End If
'-
End Sub
'====================================================
 
Upvote 0
Try this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then Exit Sub
If Target.Column <> 32 Then Exit Sub
MyRow = ActiveCell.Row
Range("B" & MyRow & ":Y" & MyRow).Copy
Sheets("WORKSHEET").Range("A65536").End(xlUp).Offset (1)
Range("z" & MyRow & ":AE" & MyRow).Copy
Sheets("WORKSHEET").Range("Z65536").End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End Sub
 
Upvote 0
Yep, that looks vaguely similar to what I wrote but seems to have had many changes attempted. And yes, as Brian pointed out the sheet names seem to have been left out this time, but since (if?) this is all taking place within the same workbook, you don't really need to include the workbook name. (Good for you for trying though, it's the only real way to learn... getting there and doing it. :wink: )
Does what Chitosunday wrote work for you now?
 
Upvote 0
Hi Masters,
I am grateful for the advice offered.
Brain,
Thank you for your comments.
I am still very new at this code things and I am still trying to learn by changing existing codes that worked and copy some from the forum here.
I realised that I have alot more to learn about macros.
But I believe that I will be better tomorrow than I was yesterday with you guys around.
I really appreciate all the comments in any form it comes in.
Dear Dan and Chitosunday,
The code works although I made some amendments to it.
I have pasted the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then Exit Sub
If Target.Column <> 32 Then Exit Sub
If Target.Column = 32 Then
MyRow = ActiveCell.Row
Range("B" & MyRow & ":Y" & MyRow).Copy Sheets("WORKSHEET").Range("A65536").End(xlUp).Offset(1)
Range("z" & MyRow & ":AE" & MyRow).Copy
Sheets("WORKSHEET").Range("Y65536").End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
End If
End Sub
Dear Brain,
I will try your code too.
It does comes in useful with the need to copy to anohter worksheet for another purpose.
However, pleased od guide me if I do have any problems, ok?
Thanks
 
Upvote 0
Looks good, glad it's working.
You could shorten it up by one line if you choose.
One of the things Brian brought up is that the line
"If Target.Column <> 32 Then Exit Sub" is not required in this context.
That's because the following line stipulates that you only want to run the code if the target column is in fact column 32.
Personally, I would leave that line and omit the line
"If Target.Column = 32 Then" .
Either way, (I believe it's Yogi that says), keep Excelling!

Dan
 
Upvote 0
Dan,
Thanks!
I got the point and will try it out.
Really glad I found this forum and yoy.
I will keep coming back and hope that ond day soon , I can also be of assistance to tohers here.
Thanks again!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top