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
 
Here is a stab at it:
VBA Code:
Sub CopyPasteValuesNumFormats()
    Dim pasteRng As Range
    Dim copyRng As Range
    'Select what to copy
    On Error Resume Next
    Set copyRng = Application.InputBox("Please select the range to copy", "Range to copy", "namedRange2CopyGoesHere", , , , , 8)
    On Error GoTo 0

    If Not copyRng Is Nothing Then
        'if we haven't cancelled selecting what to copy
        With Worksheets("DailyInitialData")
            'take the last row in column AW containing anything and paste beneath that row,
            'offset by whatever is in cell J2
            Set pasteRng = .Range("A" & .Range("AW" & .Rows.Count).End(xlUp).Row + 1 + .Range("J2").Value)
        End With
        'Now do the copy and paste special
        copyRng.Copy
        pasteRng.PasteSpecial xlPasteValuesAndNumberFormats
        'Empty clipboard
        Application.CutCopyMode = False
    End If
End Sub
 
Upvote 0
Here is a stab at it:
VBA Code:
Sub CopyPasteValuesNumFormats()
    Dim pasteRng As Range
    Dim copyRng As Range
    'Select what to copy
    On Error Resume Next
    Set copyRng = Application.InputBox("Please select the range to copy", "Range to copy", "namedRange2CopyGoesHere", , , , , 8)
    On Error GoTo 0

    If Not copyRng Is Nothing Then
        'if we haven't cancelled selecting what to copy
        With Worksheets("DailyInitialData")
            'take the last row in column AW containing anything and paste beneath that row,
            'offset by whatever is in cell J2
            Set pasteRng = .Range("A" & .Range("AW" & .Rows.Count).End(xlUp).Row + 1 + .Range("J2").Value)
        End With
        'Now do the copy and paste special
        copyRng.Copy
        pasteRng.PasteSpecial xlPasteValuesAndNumberFormats
        'Empty clipboard
        Application.CutCopyMode = False
    End If
End Sub
After the line: Set copyRng = Application.InputBox("Please select the range to copy", "Range to copy", "Day_Init_Data", , , , , 8) then the macro stops and does nothing.
It does select this range and institutes the copy but then nothing after this.
I will attach a couple Xl2bb mini sheets to help you to see what is going on.

VBA-Testing.xlsm
BCDEFGHIJKL
2Today's DateTimeTMC CZK to USDTMC USD to CZKTMC USD to EURTMC EUR to USDTMC = The Money ConverterVBA-Testing.xlsm
3Tue--25 Feb 202513:34:430.041933823.84710000.95469001.04746042: Paste in DailyInitialData31: Get A2-F2
4
5Date2025
625/02/202513:34:43Tue-25 Feb 2025
7EUR to USD >>>1.0474604K7
8USD to EUR >>>0.954690025/02/2025
9USD to CZK >>>23.847100013:34:43
10CZK to USD >>>0.0419338
Current Rates
Cell Formulas
RangeFormula
K2K2=IF(ISERROR(MID(CELL("filename",$B$2),FIND("[",CELL("filename",$B$2))+1,FIND("]",CELL("filename",$B$2))-FIND("[",CELL("filename",$B$2))-1)),"This workbook never saved",MID(CELL("filename",$B$2),FIND("[",CELL("filename",$B$2))+1,FIND("]",CELL("filename",$B$2))-FIND("[",CELL("filename",$B$2))-1))
B3,K8,K6B3=TODAY()
C3,K9C3=NOW()
D3D3=$J$10
E3E3=$J$9
F3F3=$J$8
G3G3=$J$7
I3I3=HYPERLINK("#DailyInitialData!a"&(COUNTA(AG:AG)+K3),"2: Paste in DailyInitialData")
K3K3=DailyInitialData!K1
I6I6=K8
J6J6=K9
J7J7='F:\Finances\CSOB\2025\[All-In-One.xlsm]USD-Primary'!I3
J8J8='F:\Finances\CSOB\2025\[All-In-One.xlsm]USD-Primary'!I2
J9:J10J9='F:\Finances\CSOB\2025\[All-In-One.xlsm]USD-Primary'!J2



VBA-Testing.xlsm
ABCDEFGHIJKLMNO
1DateTimeTMC CZK to USDTMC USD to CZKTMC USD to EURTMC EUR to USDCZK From Pension $ Per TMCDollars133K7$B$31
2Tue--25 Feb 202513:34:240.041933823.84710000.95469001.04746042,384.71 ₽$100.00
DailyInitialData
Cell Formulas
RangeFormula
J1J1=EXTRACTNUMBERS(O1,TRUE)
K1K1=SUM(J1+2)
L1L1=EXTRACTNUMBERS(K1)
N1N1=ADDRESS(L1,2)
O1O1=COUNTA(C2:C22)
G2G2=IF(D2*$H2=0,"",D2*$H2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:G5Expression=WEEKDAY($A2)=1textNO


Hope this helps.
 
Upvote 0
Try if this works better:
VBA Code:
Sub CopyPasteValuesNumFormats()
    Dim pasteRng As Range
    Dim copyRng As Range
    'Select what to copy
    On Error Resume Next
    Set copyRng = Application.InputBox("Please select the range to copy", "Range to copy", Range("namedRange2CopyGoesHere").Address, , , , , 8)
    On Error GoTo 0

    If Not copyRng Is Nothing Then
        'if we haven't cancelled selecting what to copy
        With Worksheets("DailyInitialData")
            'take the last row in column AW containing anything and paste beneath that row,
            'offset by whatever is in cell J2
            Set pasteRng = .Range("A" & .Range("AW" & .Rows.Count).End(xlUp).Row + 1 + .Range("J2").Value)
        End With
        'Now do the copy and paste special
        copyRng.Copy
        pasteRng.PasteSpecial xlPasteValuesAndNumberFormats
        'Empty clipboard
        Application.CutCopyMode = False
    End If
End Sub
 
Upvote 0
Try if this works better:
VBA Code:
Sub CopyPasteValuesNumFormats()
    Dim pasteRng As Range
    Dim copyRng As Range
    'Select what to copy
    On Error Resume Next
    Set copyRng = Application.InputBox("Please select the range to copy", "Range to copy", Range("namedRange2CopyGoesHere").Address, , , , , 8)
    On Error GoTo 0

    If Not copyRng Is Nothing Then
        'if we haven't cancelled selecting what to copy
        With Worksheets("DailyInitialData")
            'take the last row in column AW containing anything and paste beneath that row,
            'offset by whatever is in cell J2
            Set pasteRng = .Range("A" & .Range("AW" & .Rows.Count).End(xlUp).Row + 1 + .Range("J2").Value)
        End With
        'Now do the copy and paste special
        copyRng.Copy
        pasteRng.PasteSpecial xlPasteValuesAndNumberFormats
        'Empty clipboard
        Application.CutCopyMode = False
    End If
End Sub
Maybe I am not doing something right because this still just stops after doing the copy.
 
Upvote 0
Have you edited the code so "namedRange2CopyGoesHere" matches the named range you want to have copied?
Comment that "On error resume next" and then run the code, if there is an error it should show it.
 
Upvote 0
Have you edited the code so "namedRange2CopyGoesHere" matches the named range you want to have copied?
Comment that "On error resume next" and then run the code, if there is an error it should show it.
Yes, I do change that to indicate the exact named range. What is puzzling is this:
Set pasteRng = .Range("A" & .Range("AW" & .Rows.Count).End(xlUp).Row + 1 + .Range("k3").Value)
Again, when I get the named range into a copy mode it just all stops and nothing else happens.
The paste range is determined by picking the cell I3 which has the formula:
=HYPERLINK("#DailyInitialData!a"&(COUNTA(AG:AG)+K3),"2: Paste in DailyInitialData")
Did you check out all in the mini sheets?
 
Upvote 0
It looks like selecting the area to copy works, but the area to paste fails on your system. It worked on my sample file. Can you describe how the paste location must be determined exactly:
  • Which worksheet do we paste on
  • Which range (we only need the top-left cell of the paste range) and how do I arrive at that top-left cell?
 
Upvote 0
It looks like selecting the area to copy works, but the area to paste fails on your system. It worked on my sample file. Can you describe how the paste location must be determined exactly:
  • Which worksheet do we paste on
  • Which range (we only need the top-left cell of the paste range) and how do I arrive at that top-left cell?
Which worksheet do we paste on?: we paste on the DailyInitialData sheet.
Which range (we only need the top-left cell of the paste range) and how do I arrive at that top-left cell? See below explanation.
In reality what I was hoping for to begin with was a simple button in the field that contains the assigned macro that simply, only, Pastes Values and Number Formatting which I select via picking cell I3 on the Current Rates sheet.

The worksheet I uploaded as two mini sheets is because this workbook has 2 sheets: Current Rates & DailyInitialData
On the Current Rates sheet, cells I3:I4 contain the hyperlink that goes to the DailyInitialData sheet.
On the Current Rates sheet, cell K3 indicates which row to go to on the DailyInitialData sheet.
On the DailyInitialData sheet, cell O1 contains the count: =COUNTA(C2:C22) while cell L1 gets the next available empty row =EXTRACTNUMBERS(K1). Cell J1 formula: =EXTRACTNUMBERS(O1,TRUE); cell K1 formula: =SUM(J1+2) because there are 2 rows occupied via the count formula in O1.

And finally, this 2 sheet workbook is a testing example for a larger workbook that contains 3 sheets to paste to. Each of these 3 sheets gets different data from one source sheet called Current Rates. So you see, I already have in place the required picks to get the data on the Current Rates sheet along with the cell pick to go to the required sheet for pasting. This is why I was hoping for a simple button in the field on each sheet where I paste that contains the assigned macro that simply, only, Pastes Values and Number Formatting where I select via picking cell I3 on the Current Rates sheet.

This macro I was hoping would be the exact Microsoft Excel macro that runs when the Quick Access toolbar button is picked to do the Paste Values and Number Formatting. The Quick Access toolbar button is so tiny on the Quick Access toolbar and I am always hunting for it is why I want to create a button or image that can be placed in the field of each sheet that contains this macro. I use this Paste Values and Number Formatting on many workbooks and it is just so tiresome to always be hunting for this Quick Access tool, whereas a larger button or image in the field of each sheet would end that frustration. By the field of each sheet I am referring to the area bounded by A1 to whatever lower right cell is seen on a sheet that is current. For example, on the workbook that contains several sheets besides the Current Rates sheet I am now looking at it and I see the available range of A1:R42 with the zoom set to 95%. So it is within this range (which differs on each sheet due to column widths & row heights being different on each sheet) that I want to insert a button or image that I can pick to run the Paste Values and Number Formatting macro.

I hope this all makes sense. It seems we have been trying to reinvent the wheel with some code that does more than is necessary being that all I need the code to do is emulate the code that Microsoft uses to run the Paste Values and Number Formatting macro.
Once I have the data selected and copied to the clipboard and then selected the sheet where to paste to all I really need is the Paste Values and Number Formatting macro.
As you read through this you should be able to get a better handle on what I am saying here by viewing the minisheets I sent yesterday.
 
Upvote 0
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.
 
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