Excel VBA: How to Copy an Entire Sheet and Paste as Values

Contents

Excel VBA: How to Copy an Entire Sheet and Paste as Values

When working with Microsoft Excel, one of the most common tasks is copying a worksheet. But often, you don’t want to keep the formulas, links, or references from the original sheet—you just want the raw data. That’s where Excel VBA comes in. With a few lines of code, you can copy an entire worksheet and paste it as values only, effectively removing all formulas while preserving the layout and results.

This guide will walk you through everything you need to know about copying an entire sheet and pasting it as values with VBA, including practical examples, best practices, and real-world use cases.

✅ Why Copy a Sheet as Values?

There are many scenarios where pasting values instead of formulas is essential:

Performance: Large workbooks with many formulas can become slow. Converting them to values speeds things up.
Data sharing: When sending reports to colleagues or clients, you may not want to share the underlying formulas.
Preserving snapshots: Sometimes you need to capture a “frozen” version of the sheet for auditing or record-keeping.
Avoiding broken links: Copying formulas that reference other sheets or files may cause errors when shared.

By pasting only values, you create a static version of your worksheet that is portable, lightweight, and safe to distribute.

✅ The Manual Way (Without VBA)

Before diving into VBA, let’s look at the manual method:

Select the entire sheet by clicking the triangle button at the top-left corner (above row 1, left of column A).
Copy (Ctrl + C).
Right-click anywhere and choose Paste Special > Values.

This works fine for one-time tasks, but if you do this frequently, VBA automation will save you a lot of time.

✅ VBA Basics: Paste Values for Entire Sheet

Here’s the simplest VBA macro to copy all values from one sheet to another:

Sub CopySheetAsValues()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
'Set source and target sheets
Set wsSource = ThisWorkbook.Sheets("Sheet1")
Set wsTarget = ThisWorkbook.Sheets("Sheet2")
'Clear target sheet first
wsTarget.Cells.Clear
'Copy and paste values only
wsSource.Cells.Copy
wsTarget.Cells.PasteSpecial Paste:=xlPasteValues
'Remove copy highlight (the moving border)
Application.CutCopyMode = False
End Sub

Explanation:

wsSource.Cells.Copy: Copies the entire sheet.
.PasteSpecial Paste:=xlPasteValues: Pastes only values (no formulas, no formatting).
Application.CutCopyMode = False: Clears the clipboard outline.

This script transforms all formulas in Sheet1 into plain values in Sheet2.

✅ Copy Entire Sheet Into a New Sheet (Values Only)

Sometimes, you want to create a new worksheet with values only. Here’s how:

Sub CopySheetToNewAsValues()
Dim wsSource As Worksheet
Dim wsNew As Worksheet
'Set the source sheet
Set wsSource = ThisWorkbook.Sheets("Sheet1")
'Create a new sheet
Set wsNew = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
wsNew.Name = "Values_" & wsSource.Name
'Copy and paste as values
wsSource.Cells.Copy
wsNew.Cells.PasteSpecial Paste:=xlPasteValues
'Remove copy outline
Application.CutCopyMode = False
End Sub

This macro:

Creates a brand-new sheet named Values_Sheet1.
Pastes the data as values.
Leaves the original sheet untouched.

✅ Keep Formatting While Removing Formulas

What if you want to keep the cell formatting (colors, borders, fonts) along with the values?

Sub CopySheetValuesAndFormats()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Set wsSource = ThisWorkbook.Sheets("Sheet1")
Set wsTarget = ThisWorkbook.Sheets("Sheet2")
wsTarget.Cells.Clear
'Copy values
wsSource.Cells.Copy
wsTarget.Cells.PasteSpecial Paste:=xlPasteValues
'Copy formatting
wsSource.Cells.Copy
wsTarget.Cells.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
End Sub

This ensures the target sheet looks identical to the source, but without formulas.

✅ Alternative Method: Assigning Values Directly

Instead of using Copy and PasteSpecial, you can assign values directly:

Sub DirectValueCopy()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Set wsSource = ThisWorkbook.Sheets("Sheet1")
Set wsTarget = ThisWorkbook.Sheets("Sheet2")
'Direct assignment removes formulas automatically
wsTarget.Cells.Value = wsSource.Cells.Value
End Sub

This is faster and avoids using the clipboard. However, it only transfers values—not formatting.

✅ Real-World Use Cases

1. Monthly Report Snapshots

Finance teams often need a static version of reports each month. A VBA macro can generate a “values only” sheet automatically, ensuring data integrity.

2. Data Export Preparation

Before sending data to another department or uploading to a system, you can remove formulas to ensure compatibility.

3. Large File Optimization

Big workbooks with complex formulas can slow down. By converting old sheets to values, you reduce file size and speed up processing.

4. Protecting Confidential Formulas

If your model contains sensitive formulas, but you need to share the results, pasting values only is a safe option.

✅ Best Practices for Copying Sheets as Values

✅ Use descriptive names for output sheets (e.g., Snapshot_Jan2025).
✅ Clear target sheets before pasting to avoid leftover data.
✅ Consider formatting needs—choose between values only or values + formats.
✅ Disable ScreenUpdating for faster execution when dealing with large sheets:

Application.ScreenUpdating = False
'... your code ...
Application.ScreenUpdating = True

✅ Error handling: Always add simple error traps for robustness.

✅ Common Mistakes and How to Avoid Them

Mistake	Problem	Solution
Forgetting to clear target sheet	Old data remains	Use `wsTarget.Cells.Clear`
Not disabling `CutCopyMode`	Moving border stays active	Add `Application.CutCopyMode = False`
Direct assignment without checking	Overwrites formatting	Use `PasteSpecial` if you need formatting
Copying very large sheets	Slow performance	Use `Value` assignment instead of clipboard

✅ Advanced Tip: Loop Through All Sheets and Convert to Values

If you want to convert all sheets in a workbook to values only:

Sub ConvertAllSheetsToValues()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Sheets
ws.Cells.Value = ws.Cells.Value
Next ws
Application.ScreenUpdating = True
MsgBox "All sheets converted to values."
End Sub

This is especially useful when archiving or finalizing reports.

✅ Frequently Asked Questions (FAQ)

❓ Will pasting values remove formatting?

Yes, unless you also paste formats separately. Use xlPasteFormats to keep styles.

❓ Can I copy hidden rows and columns as values?

Yes, hidden rows and columns are included in the copy.

❓ Does pasting values reduce file size?

Often, yes. Removing formulas and links makes workbooks lighter.

❓ Can I undo VBA paste values?

Only if you immediately press Ctrl + Z after running the macro. Otherwise, changes are permanent.

❓ Is there a way to keep images and charts?

Copying values only does not affect charts or images. They remain intact.

✅ Summary

Copying an entire sheet and pasting as values in Excel VBA is a powerful way to simplify workbooks, preserve data, and share reports safely.

Use .PasteSpecial xlPasteValues to remove formulas.
Use .Value = .Value for faster, code-only transfer.
Add .PasteSpecial xlPasteFormats to keep formatting.
Automate for snapshots, exports, and performance optimization.

By mastering this VBA technique, you can build efficient, professional, and secure Excel workbooks that are easy to share and maintain.

✅ Final Thoughts

Excel VBA provides the flexibility to automate repetitive tasks like copying entire sheets as values. Whether you’re preparing monthly reports, cleaning up a file for distribution, or optimizing workbook performance, this skill will save time and prevent errors.

Once you incorporate these VBA methods into your workflow, you’ll wonder how you ever managed without them.