VBA to Paste Values and Number Formatting

[MrDB4Excel]

I have tried in vain to create an Xlam file or just a simple VBA that allows me to create a selection set and then paste that content to another location via another selection cell or range.
By selection set, I refer to the code giving time to select a range.
I have tried several approaches but something is not working right.
So if anyone can steer me in the right direction with code that does the following it would be much appreciated.
Item number 1 is a question regarding correct syntax.
I now do this using items 2 through 5 below, and I would like to use number 6 instead of selecting the Quick Access Toolbar button.
Items 2 through 5 are often repeated for other named ranges throughout this workbook.

1) Here on the first line do I need to have a macro name?
2) First I pick a cell that highlights a specific range, this cell is hyperlinked to a named range
3) Next I do the CTRL-C
4) Next I select a cell that jumps to another sheet ((((this is the content of that cell: =HYPERLINK("#DailyInitialData!a"&(COUNTA(AW:AW)+J2),"2: Paste in DailyInitialData")
5) Next I select the Quick Access Toolbar button for Pasting Values And Number Formats
6) Instead of selecting the Quick Access Toolbar button for Pasting Values And NumberFormats I want to create a button on the sheet field that does this using the following code which obviously is missing vital script:



rRange.pastespecial xlPasteValuesAndNumberFormats = True

End If

End Sub

 

[MrDB4Excel]

Would a short-cut key combination help?

1. Control+c to copy whatever it needs copying
2. Select paste location
3. Control+Alt+V to open the Paste special dialog
4. press u to select "Values and number formats"
5. Enter to do the paste.

Or (English Excel):

1. Control C to copy
2. Select paste location
3. Alt+h, v, a to paste values and number formats.

I tried this and although a bit confusing it did work. I have never been a fan of shortcut keys involving more than two keys, e.g. ctrl v, ctrl c, ctrl z, ctrl x.
What I am now wondering is if it would be possible to view Microsoft' Excel's VBA that does this Paste Values and Number Formatting. It just seems that it should be a very simple macro.
Surely someone out here in Excel VBA land has some inkling of the lines of code that does this.
Going forward I assure you it will remain simpler to continue as I have by selecting the Quick Access tool rather than any key combo more than my usual two-key combos.
There just has to be some relevant lines of code to do exactly what the Paste Values and Number Formatting does by the selection of the Quick Access tool.

 

[MrDB4Excel]

I have not tried this yet but soon will. How about this:
Sub PasteValuesAndNumberFormatting()


Dim copyRange As Range
Dim pasteRange As Range
On Error Resume Next
Set copyRange = Application.InputBox("Select the range to copy:", Type:=8)
Set pasteRange = Application.InputBox("Select the destination range to paste:", Type:=8)
On Error GoTo 0
If Not copyRange Is Nothing And Not pasteRange Is Nothing Then
copyRange.Copy
With pasteRange
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteNumberFormats
End With
Application.CutCopyMode = False
End If
End Sub


Does not work as I expected. It demands I tell it what is the copy range and the paste range. I only want my copy range to be pasted with Paste Values and Number Formatting when I select an object with the assigned macro.

 

[MrDB4Excel]

I have not tried this yet but soon will. How about this:
Sub PasteValuesAndNumberFormatting()


Dim copyRange As Range
Dim pasteRange As Range
On Error Resume Next
Set copyRange = Application.InputBox("Select the range to copy:", Type:=8)
Set pasteRange = Application.InputBox("Select the destination range to paste:", Type:=8)
On Error GoTo 0
If Not copyRange Is Nothing And Not pasteRange Is Nothing Then
copyRange.Copy
With pasteRange
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteNumberFormats
End With
Application.CutCopyMode = False
End If
End Sub


Does not work as I expected. It demands I tell it what is the copy range and the paste range. I only want my copy range to be pasted with Paste Values and Number Formatting when I select an object with the assigned macro.

Maybe something akin to the following:
Sub CopyPasteValues()
Pause, get range to copy
Pause, select where to paste (this selection jumps to the paste location)
Range("selection").PasteSpecial Paste:=xlPasteNumberFormats
End Sub

This seems to be very simple and yet it indicates exactly how I want it to work. First it pauses giving me time to select a range to copy, next it pauses again giving me time to pick the cell that hyperlinks to the correct paste location. when the cursor resides in the active cell that resulted from selecting the hyperlink jump to cell then the range that was added to the clipboard gets pasted as Values and Number Formatting.
I don't understand how this could be so difficult to write and make to work thus allowing the user to select any range to copy and jump to a given destination and completing the paste.
It seems that the pauses are key but I am wholly unfamiliar with writing pauses into code.

 

[jkpieterse]

It is as simple as:

SubPasteVals()
    ActiveCell.PasteSpecial xlPasteValuesAndNumberformats
End Sub

But note that it cannot be undone, whereas using the short-cut does allow undo.

 

[Alex Blakenburg]

Edited - Mostly deleted - same as JKP

I don't know how you plan on triggering the macro, especially if you don't like shortcuts with 3 key combination values.
Putting in position 1 - 9 of the QAT and using Alt+1-9, would be simpler and get you down to a 2 key combination.

 

[MrDB4Excel]

It is as simple as:

SubPasteVals()
    ActiveCell.PasteSpecial xlPasteValuesAndNumberformats
End Sub

But note that it cannot be undone, whereas using the short-cut does allow undo.

Perfect, exactly what I need, and no worries about needing to undo as if I need to undo it is called quitting the file without saving. But that will never need to be done as the values are easily removed with 'Clear Contents'
Again, this is absolutely perfect as it is usable with any range copied and any goto for paste.
Many thanks!!!

One minor correction: SubPasteVals() was changed to Sub PasteVals()