Copy Destination:= is pasting formulas instead of values

jameslec

New Member
Joined
Jun 21, 2013
Messages
10
I fell like this is an easy answer. I am copying cells from 1 worksheet to another if a condition is met. My code is working but it is pasting the formulas from the cell instead of the value of the cell. How can I modify this code to paste the values?

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngCheck As Range
Dim CheckCell As Range
Dim lRow As Long


Set rngCheck = Intersect(Me.Columns("BJ"), Target)


If Not rngCheck Is Nothing Then
For Each CheckCell In rngCheck.Cells
If CheckCell.Value = "Y" Then
With Sheets("Event Log")
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Me.Cells(CheckCell.Row, "A").Copy Destination:=.Cells(lRow, "A")
Me.Cells(CheckCell.Row, "BA").Copy Destination:=.Cells(lRow, "B")
Me.Cells(CheckCell.Row, "BH").Copy Destination:=.Cells(lRow, "E")
Me.Cells(CheckCell.Row, "BB").Copy Destination:=.Cells(lRow, "F")
End With
End If
Next CheckCell
End If


End Sub
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Try incorporating something like this in your code

Code:
Range("A" & Rows.count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Regards,
Howard
 

jameslec

New Member
Joined
Jun 21, 2013
Messages
10
:) Thank you! I got it to work by changing it to:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngCheck As Range
Dim CheckCell As Range
Dim lRow As Long


Set rngCheck = Intersect(Me.Columns("BJ"), Target)


If Not rngCheck Is Nothing Then
For Each CheckCell In rngCheck.Cells
If CheckCell.Value = "Y" Then
With Sheets("Event Log")
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Me.Cells(CheckCell.Row, "A").Copy
Sheets("Event log").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Me.Cells(CheckCell.Row, "BA").Copy
Sheets("Event log").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Me.Cells(CheckCell.Row, "BH").Copy
Sheets("Event log").Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
Me.Cells(CheckCell.Row, "BB").Copy
Sheets("Event log").Range("D" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
End With
End If
Next CheckCell
End If


End Sub
 

ThaiFood

Board Regular
Joined
May 11, 2014
Messages
57

ADVERTISEMENT

So I have a question on this topic. I have the basically the same code that I changed a bit to work for my Excel Project. But I need it to send the data to Multiple Pages based on the cell value in row "K". As now it works fine with 1 page but I need it to work with 13 or so sheets. I know it may seem confusing as to why I use the I, AD, AE but for my purpose thats what I need. Now below is the code I am using but lets say instead of "Test" I have "Test1", "Test2", "Test3" on up to "Test13". Whenever I enter Test1, I need it copied to worksheet "Test1". The same would go for the rest "Test3" would goto WorkSheet "Test3". As I said before the code below works fine for the "Test" page even drops to next avail line. I am sure that its something I am just overlooking but I so far cannot find the answer. Any help on this would be amazing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCheck As Range
Dim CheckCell As Range
Dim lRow As Long
Set rngCheck = Intersect(Me.Columns("K"), Target)
If Not rngCheck Is Nothing Then
For Each CheckCell In rngCheck.Cells
If CheckCell.Value = "Test" Then
With Sheets("Complete-Test")
lRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
Me.Cells(CheckCell.Row, "I").Copy Destination:=.Cells(lRow, "I")
Me.Cells(CheckCell.Row, "AD").Copy Destination:=.Cells(lRow, "AD")
Me.Cells(CheckCell.Row, "AE").Copy Destination:=.Cells(lRow, "AE")

End With
End If
Next CheckCell
End If
End Sub
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi ThaiFood, welcome to the forum.

So do you want enter Test1 in a cell on the worksheet and have the code look at each CheckCell in column K and:

If ChechkCell = Test1 copy to worksheet Test1 columns I, AD, AE

You enter Test2 and:

If ChechkCell = Test2 copy to worksheet Test2 columns I, AD, AE

You enter Test3 and:

If ChechkCell = Test3 copy to worksheet Test3 columns I, AD, AE

You enter Test13 and

If ChechkCell = Test13 copy to worksheet Test13 columns I, AD, AE

And each entry on each sheet will go below the last one entered.

Can you post a link to an example workbook along with detailed instructions of what you want?

Howard
 

ThaiFood

Board Regular
Joined
May 11, 2014
Messages
57

ADVERTISEMENT

Hi ThaiFood, welcome to the forum.

So do you want enter Test1 in a cell on the worksheet and have the code look at each CheckCell in column K and:

If ChechkCell = Test1 copy to worksheet Test1 columns I, AD, AE

You enter Test2 and:

If ChechkCell = Test2 copy to worksheet Test2 columns I, AD, AE

You enter Test3 and:

If ChechkCell = Test3 copy to worksheet Test3 columns I, AD, AE

You enter Test13 and

If ChechkCell = Test13 copy to worksheet Test13 columns I, AD, AE

And each entry on each sheet will go below the last one entered.

Can you post a link to an example workbook along with detailed instructions of what you want?

Howard

Thanks Howard,


Well, basically this is for work. I have a larger excel sheet that I copy info from and paste into my own which basically acts as a tracker for service orders. The test names are actually MFG names but thats not important at the moment. The only cells I want to copy are I, AD, AE which are the only ones that contain the info that I need. It already works fine but for only 1 MFG or "Test" entered into row K. But I need to work for upto 13 different "Test" or "Test1" sheets based on whats entered into "K" Cell. I don't need a button or anything like that.

I would post an attachment but it won't let me for some reason. I created a test workbook and it works fine as well. Anyway, please let me know if you need more info.
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
You cannot post an attachment, you must post a link.

Use something like Drop Box, which is what I use, and there are others.

Some of the MVP's in this forum have signature blocks with the various link processors.

I still not up to speed on how you want the sheet to preform.

A link would be most helpful.

Regards,
Howard
 

ThaiFood

Board Regular
Joined
May 11, 2014
Messages
57
Howard,

Snakehips cleared it up for me. It was my fault for not explaining clearly or at least understanding the code correctly. Everything works fine now. Thanks for your assistance as well.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,687
Messages
5,597,541
Members
414,154
Latest member
thevaper

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
Top