Format numbers, copy, paste as text

R2ah1ze1l

Board Regular
Joined
Nov 10, 2017
Messages
93
I have values on a spreadsheet that I want formatted to a 4th decimal place, then I want to copy those values (with 0's showing) and paste them as text into another spreadsheet.
Value 1 appears as 0.008924 but should show as .0090
Value 2 appears as 1.04 but should show as 1.0400

I have 4 columns of data and 3 of them are numeric entries, the sheet the values are coming from can be formatted if needed but that formatting would need to be done via this script. Below is what I'm using to populate my new worksheet.

wsMeas: Holding original data
wsData: new document

Code:
Windows(wbName).Activate
    feat_data = wsMeas.Range(wsMeas.Cells(1, 2), wsMeas.Cells(lastr + 1, 2)).Value
    nom_val = wsMeas.Range(wsMeas.Cells(1, 3), wsMeas.Cells(lastr + 1, 3)).Value
    p_tol = wsMeas.Range(wsMeas.Cells(1, 4), wsMeas.Cells(lastr + 1, 4)).Value
    m_tol = wsMeas.Range(wsMeas.Cells(1, 5), wsMeas.Cells(lastr + 1, 5)).Value
wsData.Activate
    wsData.Range(wsData.Cells(2, 1), wsData.Cells(1 + lastr, 1)).Value = feat_data
    wsData.Range(wsData.Cells(2, 2), wsData.Cells(1 + lastr, 2)).Value = pn_name
    wsData.Range(wsData.Cells(2, 3), wsData.Cells(1 + lastr, 3)).Value = "Variable"
    wsData.Range(wsData.Cells(2, 4), wsData.Cells(1 + lastr, 4)).Value = "Bilateral"
    'wsData.Range(wsData.Cells(2, 5), wsData.Cells(1 + lastr, 5)).Value = nom_val & " +" & p_tol & " / -" & m_tol
    wsData.Range(wsData.Cells(2, 6), wsData.Cells(1 + lastr, 6)).Value = nom_val
    wsData.Range(wsData.Cells(2, 7), wsData.Cells(1 + lastr, 7)).Value = p_tol
    wsData.Range(wsData.Cells(2, 8), wsData.Cells(1 + lastr, 8)).Value = m_tol
    wsData.Range(wsData.Cells(2, 14), wsData.Cells(1 + lastr, 14)).Value = "No"
    wsData.Range(wsData.Cells(2, 15), wsData.Cells(1 + lastr, 15)).Value = "No"
    wsData.Range(wsData.Cells(2, 19), wsData.Cells(1 + lastr, 19)).Value = op_nam
    wsData.Range(wsData.Cells(2, 20), wsData.Cells(1 + lastr, 20)).Value = "100 Percent"
    wsData.Range(wsData.Cells(2, 27), wsData.Cells(1 + lastr, 27)).Value = "CMM"
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,224,618
Messages
6,179,917
Members
452,949
Latest member
beartooth91

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