How to Export Excel Data as Values While Preserving Formatting via VBA
Question details
Fix or create a VBA macro that exports spreadsheet data to a new workbook by converting formulas to values, but keeps original formatting, graphic logos, and specific necessary formulas intact.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a static report from a dynamically calculated spreadsheet without losing the professional layout, formatting, or essential design elements like company logos.
- Observed behavior
- The user needs to successfully copy the entire sheet, convert the majority of the formulas into static values, restore specific mandatory formulas, and ensure graphic objects correctly position themselves on the new sheet.
Ensure you have saved a backup copy of your original workbook and enabled the Developer tab in your spreadsheet application to access the VBA editor.
Use Direct Value Assignment and Format Copying in VBA
Utilize VBA to duplicate the sheet, convert the copied used range directly to values, and strategically restore any required formulas.
The most efficient way to convert a large range of formulas to values while keeping the original formatting is to copy the entire sheet, paste everything, and then equate the used range's values to itself. This avoids the clipboard overhead of PasteSpecial and preserves colors, fonts, and layouts.
Press Alt + F11 to open the Visual Basic for Applications (VBA) editor, then insert a new Module from the Insert menu.
Write the macro code to copy the entire source worksheet to a new workbook. For example: wsSource.Copy Before:=Workbooks.Add.Worksheets(1).
In the newly created worksheet, use the direct assignment method to overwrite formulas with their static results: newWs.UsedRange.Value = newWs.UsedRange.Value.
If certain columns (like column AP) need to remain as formulas, write them back specifically in the code using newWs.Range("AP2:AP100").Formula = "=YOUR_FORMULA_HERE".
Remove any unwanted data validation using newWs.Cells.Validation.Delete. If logos weren't copied over with the sheet, copy them manually via Shapes("Logo").Copy and paste them into the required cell coordinates.
Run Macros Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides outstanding support for VBA and macros, allowing you to easily run your data export and formatting scripts without compatibility issues.
- 1. Download and Install WPS Office: Get the latest version of WPS Office and open WPS Spreadsheet.
- 2. Enable the Developer Tab: Navigate to the Tools menu and click Developer to access the Macro and VBA Editor.
- 3. Paste and Run Your Code: Insert a new module, paste your export macro, and click Run to generate your beautifully formatted static report.
Frequently Asked Questions
Why do my logos shift or stretch when copying via a VBA macro?
Shapes and pictures rely on the underlying cell dimensions. If the new workbook does not have the exact same column widths and row heights as the original before the shape is pasted, the logo may resize or shift. Ensure you copy column widths first.
Can I convert only specific columns to values instead of the entire sheet?
Yes. Instead of using UsedRange, you can specify the target range. For example, to convert only columns A through C, you can use the code: newWs.Columns("A:C").Value = newWs.Columns("A:C").Value.
Why does PasteSpecial xlPasteValues lose my cell colors and borders?
The xlPasteValues command strictly copies the calculated data in the cells and intentionally strips out formatting. To keep your styling, you must either also execute an xlPasteFormats step, or use the direct value assignment method (.Value = .Value) which leaves the existing formats completely untouched.
