automating paste special value

tumbler

New Member
Joined
Jun 17, 2003
Messages
35
I've been working on trying to CONCATENATE a link to a bit of data in a separate file. I have many files to draw data from - this is the reason for wanting to automate it.

What I have done is generated the link text string, and then I want to automate Copy-Paste Special-Value, so that the link is the actual link string not CONCATENATE ..... now I'm doing this with a macro - so it is working, but it pastes the full correct text string, but stops there - I then need to enter the cell, and leave it again ! - then the formula works - without being changed.

If I try to automate that with a MACRO, the VB code contains the actual cell contents - just just enter/leave, so the macro won't work when a variable changes - as it contains the exact references from when I recorded the macro.

I think I have made things much more complex than they need to be here - but have had little success with getting help on this subject - even here ! - Is this a really complicated problem ? - I need help, please, I CHALLENGE YOU !!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
I actually went your route and built code to do this for me. Later, I was looking through the buttons available with Excel, and I actually found a default button already setup, and added it to my button bar. Just right click on the button bars and select customize then click on the commands tab. Click on edit in the listbox then scroll down the right window until you see the paste value icon. Just drag an drop this to your toolbar.

Here's the code I use for my custom button, I still use it, because I can assign the macro to a ctrl- key combo for ease of use.

NOTE-My custom function cannot be undone, but the excel one can be undone.

Sub PasteValues()
On Error GoTo Errorhandler
Dim test As String
test = Selection.Address
Selection.PasteSpecial Paste:=xlValues
Exit Sub
Errorhandler:
Select Case Err.Number

Case 1004
Exit Sub
Case Else
MsgBox Err.Number & " " & Err.Description
Exit Sub
End Select

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,782
Members
414,020
Latest member
Meghdad

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