automating paste special value

[tumbler]

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 !!

 

[Cbrine]

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