VBA to Paste Values and Number Formatting

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
360
Office Version
  1. 2013
Platform
  1. Windows
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
 
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
It is as simple as:
VBA Code:
SubPasteVals()
    ActiveCell.PasteSpecial xlPasteValuesAndNumberformats
End Sub
But note that it cannot be undone, whereas using the short-cut does allow undo.
 
Upvote 0
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.
 
Upvote 0
It is as simple as:
VBA Code:
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()
 
Upvote 0

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