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

 

[jkpieterse]

Here is a stab at it:

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

 

[MrDB4Excel]

Here is a stab at it:

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
	B	C	D	E	F	G	H	I	J	K	L
2	Today's Date	Time	TMC CZK to USD	TMC USD to CZK	TMC USD to EUR	TMC EUR to USD			TMC = The Money Converter	VBA-Testing.xlsm
3	Tue--25 Feb 2025	13:34:43	0.0419338	23.8471000	0.9546900	1.0474604		2: Paste in DailyInitialData		3	1: Get A2-F2
4
5										Date	2025
6								25/02/2025	13:34:43	Tue-25 Feb 2025
7								EUR to USD >>>	1.0474604	K7
8								USD to EUR >>>	0.9546900	25/02/2025
9								USD to CZK >>>	23.8471000	13:34:43
10								CZK to USD >>>	0.0419338
Current Rates

Cell Formulas
Range	Formula
K2	K2	=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,K6	B3	=TODAY()
C3,K9	C3	=NOW()
D3	D3	=$J$10
E3	E3	=$J$9
F3	F3	=$J$8
G3	G3	=$J$7
I3	I3	=HYPERLINK("#DailyInitialData!a"&(COUNTA(AG:AG)+K3),"2: Paste in DailyInitialData")
K3	K3	=DailyInitialData!K1
I6	I6	=K8
J6	J6	=K9
J7	J7	='F:\Finances\CSOB\2025\[All-In-One.xlsm]USD-Primary'!I3
J8	J8	='F:\Finances\CSOB\2025\[All-In-One.xlsm]USD-Primary'!I2
J9:J10	J9	='F:\Finances\CSOB\2025\[All-In-One.xlsm]USD-Primary'!J2

VBA-Testing.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Time	TMC CZK to USD	TMC USD to CZK	TMC USD to EUR	TMC EUR to USD	CZK From Pension $ Per TMC	Dollars		1	3	3	K7	$B$3	1
2	Tue--25 Feb 2025	13:34:24	0.0419338	23.8471000	0.9546900	1.0474604	2,384.71 ₽	$100.00
DailyInitialData

Cell Formulas
Range	Formula
J1	J1	=EXTRACTNUMBERS(O1,TRUE)
K1	K1	=SUM(J1+2)
L1	L1	=EXTRACTNUMBERS(K1)
N1	N1	=ADDRESS(L1,2)
O1	O1	=COUNTA(C2:C22)
G2	G2	=IF(D2*$H2=0,"",D2*$H2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:G5	Expression	=WEEKDAY($A2)=1	text	NO

Hope this helps.

 

[jkpieterse]

Try if this works better:

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

 

[MrDB4Excel]

Try if this works better:

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.

 

[jkpieterse]

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.

 

[MrDB4Excel]

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?

 

[jkpieterse]

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?

 

[MrDB4Excel]

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.

 

[jkpieterse]

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.