Excel VBA: How to Paste Values from the Current Sheet to Another Sheet — Complete Guide for Professionals

In Excel automation, copying and pasting between sheets is one of the most frequent tasks. However, when your goal is to paste only values — not formulas, links, or formats — you need precision. Whether you are preparing a report, exporting data to a summary sheet, or cleaning up an imported file, VBA allows you to perform value-only transfers between sheets quickly and safely.

This guide explains how to copy and paste values only from the current worksheet to another sheet, without using the clipboard. We’ll cover both beginner-friendly examples and advanced techniques for handling dynamic data, formatting, and performance optimization. By the end, you’ll know how to build a clean and efficient automation workflow ideal for business and RPA integration.

---

✅ Why Paste Values from the Current Sheet to Another Sheet?

When you copy formulas across sheets manually, Excel often causes these issues:

• Formulas reference wrong cells (#REF! errors)
• Data changes when the source sheet updates
• Hidden links cause file corruption
• File size grows unnecessarily
• Calculations slow down large reports

Instead, when you paste values, you create a snapshot of your data.
✅ No formula references
✅ No recalculations
✅ Clean data ready for sharing or automation

This is especially useful for:

• Monthly summaries
• Finalized sales reports
• Cleaning imported data before upload
• Sending outputs to UiPath or Power Automate workflows

---

✅ Basic Example: Copy Values to Another Sheet

・Simplest VBA Code

Sub CopyValuesToOtherSheet()
Sheets("Sheet2").Range("A1:A10").Value = Sheets("Sheet1").Range("A1:A10").Value
End Sub

✅ Copies data from Sheet1 to Sheet2
✅ No clipboard involved
✅ Only values — formulas are removed

If you are running this code from Sheet1, you can also use:

Sub CopyValuesFromActiveSheet()
Sheets("Summary").Range("A1:A10").Value = ActiveSheet.Range("A1:A10").Value
End Sub

---

✅ Step-by-Step: How It Works

1. Identify the range you want to copy (e.g., A1:A10).
2. Identify the destination sheet and range (e.g., Sheet2!A1:A10).
3. Use .Value = .Value instead of .Copy / .PasteSpecial.
4. Verify data has been pasted as numbers or text — no formulas remain.
5. Optional: Clear clipboard to avoid residual data.

✅ Faster than standard copy-paste
✅ No flicker or “marching ants” copy border

---

✅ Copy Entire Range Dynamically (Auto-Detect Data Size)

・Using LastRow and LastColumn Detection

Sub CopyDynamicRangeToSheet()
Dim lastRow As Long, lastCol As Long
Dim src As Worksheet, dest As Worksheet
Set src = ActiveSheet
Set dest = Sheets("Output")
lastRow = src.Cells(Rows.Count, 1).End(xlUp).Row
lastCol = src.Cells(1, Columns.Count).End(xlToLeft).Column
dest.Range("A1").Resize(lastRow, lastCol).Value = src.Range("A1").Resize(lastRow, lastCol).Value
End Sub

✅ Automatically adapts to the actual data size
✅ Ideal for dynamic reports or daily data exports

---

✅ Paste Values to Another Sheet and Keep Number Formats

You can preserve number formatting (currency, date, percent) by copying values first, then applying formats.

Sub CopyValuesWithFormat()
Dim wsSrc As Worksheet, wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Sheets("Report")
wsDst.Range("A1:D20").Value = wsSrc.Range("A1:D20").Value
wsDst.Range("A1:D20").NumberFormat = wsSrc.Range("A1:D20").NumberFormat
End Sub

✅ Keeps financial data readable
✅ Prevents “text as number” issues common in imports

---

✅ Paste Values to the Next Available Row

Often, you need to append new data below existing entries in the target sheet.

Sub AppendValuesToAnotherSheet()
Dim wsSrc As Worksheet, wsDst As Worksheet
Dim nextRow As Long
Set wsSrc = ActiveSheet
Set wsDst = Sheets("History")
nextRow = wsDst.Cells(Rows.Count, 1).End(xlUp).Row + 1
wsDst.Range("A" & nextRow).Resize(10, 5).Value = wsSrc.Range("A1:E10").Value
End Sub

✅ Adds data dynamically without overwriting
✅ Common in cumulative logs or historical tracking

---

✅ Use PasteSpecial for Controlled Transfers

If you want to use the clipboard intentionally for mixed operations:

Sub CopyValuesUsingPasteSpecial()
ActiveSheet.Range("A1:D10").Copy
Sheets("Archive").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

✅ Works even if the range contains mixed data types
✅ Familiar structure for those transitioning from manual Excel use

However, .Value = .Value is still faster and cleaner for pure automation.

---

✅ Paste Values After Filtering

When data is filtered, direct assignment copies hidden rows too.
To copy only visible rows, use SpecialCells.

Sub CopyVisibleValuesOnly()
Dim wsSrc As Worksheet, wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Sheets("FilteredOutput")
wsSrc.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
wsDst.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

✅ Ensures filtered results only
✅ Useful for exporting summarized or conditional reports

---

✅ Paste Values Between Sheets in Different Workbooks

Sometimes your destination sheet is in another file.

Sub CopyValuesToAnotherWorkbook()
Workbooks("Book2.xlsx").Sheets("Target").Range("A1:D10").Value = _
ThisWorkbook.Sheets("Source").Range("A1:D10").Value
End Sub

✅ Transfers values instantly between workbooks
✅ Perfect for cross-file automation

---

✅ Combine with RPA Tools (UiPath, Power Automate)

In enterprise automation, this VBA process is often part of a larger RPA flow:

1. UiPath downloads Excel reports from ERP
2. VBA script copies only the required values to a clean “Output” sheet
3. UiPath uploads the processed file to SharePoint or emails it

This setup eliminates broken links and dynamic formulas that can confuse RPA bots.
✅ Robots handle static, predictable data
✅ Human-free, error-free processing

---

✅ Error Handling for Robust Automation

Always guard against sheet name or data errors:

On Error Resume Next
If Sheets("Report") Is Nothing Then
MsgBox "Report sheet not found!", vbCritical
Exit Sub
End If
On Error GoTo 0

✅ Prevents unexpected termination
✅ Adds reliability for scheduled automations

---

✅ Performance Optimization Tips

Technique	Description
Disable screen updates	Application.ScreenUpdating = False
Avoid clipboard use	Use .Value = .Value instead of .Copy
Clear CutCopyMode	Prevents paste errors
Limit range size	Copy only what’s needed
Use UsedRange	Automatically detects active data

Example:

Sub FastValueTransfer()
Application.ScreenUpdating = False
Sheets("Summary").UsedRange.Value = Sheets("Data").UsedRange.Value
Application.ScreenUpdating = True
End Sub

✅ Executes in seconds even for thousands of rows

---

✅ Hands-On Practice: Build a Mini Automation Workflow

Let’s create a simple business-use macro.

Goal: Copy sales results from the active sheet to a “Monthly_Report” sheet as values.

Steps:

1. Identify source sheet = ActiveSheet
2. Identify destination sheet = “Monthly_Report”
3. Copy all used data
4. Paste as values only
5. Clear clipboard
6. Confirm success

Code Example:

Sub ExportToMonthlyReport()
Dim wsSrc As Worksheet, wsDst As Worksheet
Set wsSrc = ActiveSheet
Set wsDst = Sheets("Monthly_Report")
wsDst.UsedRange.Clear
wsDst.Range("A1").Resize(wsSrc.UsedRange.Rows.Count, wsSrc.UsedRange.Columns.Count).Value = _
wsSrc.UsedRange.Value
MsgBox "Report updated successfully!"
End Sub

✅ Creates a fresh, formula-free monthly report in seconds.

---

✅ Summary: Paste Values Between Sheets to Simplify and Secure Automation

• Copying values only ensures data stability and reliability
• .Value = .Value is faster and safer than standard Copy/Paste
• Dynamic range detection adapts to real data size
• Perfect for daily reporting, cleaning, and RPA workflows
• Builds the foundation for clean, formula-free Excel automation

By pasting values only between sheets, you eliminate calculation errors, speed up Excel, and ensure your data stays consistent across reports.
This technique is a must-have in every VBA developer’s toolkit — simple, powerful, and scalable.