Excel VBA: How to Paste Values from Another Sheet to the Current Sheet — Step-by-Step Automation Guide

Copying and pasting values between sheets is one of the most common automation tasks in Excel VBA. Whether you are preparing a summary sheet, consolidating data from multiple departments, or creating a daily report from a data source sheet, transferring values only ensures stability and prevents formula-related errors.

In this detailed guide, you’ll learn how to paste values only from another sheet into the active sheet safely and efficiently. We’ll go through multiple examples — from basic usage to dynamic automation — and discuss real business scenarios where this approach significantly improves performance and reliability, especially when combined with RPA tools like UiPath or Power Automate.

---

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

When you copy data between sheets in Excel, standard copy-paste operations also bring formulas, conditional formatting, hyperlinks, and even hidden references. This can lead to:

• Unexpected formula links across sheets
• Wrong or outdated values when source data changes
• Increased file size
• Slower workbook performance

By copying values only, you paste static, reliable data that won’t change even if the original sheet is modified.

This approach is essential when:

• You prepare a clean output for managers or clients
• You’re building dashboards that shouldn’t depend on volatile formulas
• You want to create data snapshots for daily or monthly archiving
• You’re preparing Excel data for RPA robots to read safely

---

✅ Basic Example: Paste Values from Another Sheet to Active Sheet

・Simplest way to copy data as values

Sub PasteValuesFromAnotherSheet()
Dim wsSource As Worksheet
Set wsSource = Sheets("Data")
' Copy values from "Data" sheet to current sheet
ActiveSheet.Range("A1:A10").Value = wsSource.Range("A1:A10").Value
End Sub

✅ This copies values (not formulas or formats) from the “Data” sheet into the current sheet.
✅ Fast and reliable — no clipboard involved.

---

✅ How It Works (Step-by-Step)

1. Declare and set a variable for the source sheet (e.g., "Data")
2. Select the active sheet automatically using ActiveSheet
3. Use the .Value = .Value approach to transfer data
4. Verify results — only raw data, no formulas remain
5. Optionally, format or clear the range afterward

This method executes in milliseconds and doesn’t require activating or selecting sheets.

---

✅ Copy Dynamic Range Automatically

・When the number of rows changes daily

Sub PasteDynamicValues()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim lastRow As Long, lastCol As Long
Set wsSrc = Sheets("Source")
Set wsDst = ActiveSheet
lastRow = wsSrc.Cells(Rows.Count, 1).End(xlUp).Row
lastCol = wsSrc.Cells(1, Columns.Count).End(xlToLeft).Column
wsDst.Range("A1").Resize(lastRow, lastCol).Value = _
wsSrc.Range("A1").Resize(lastRow, lastCol).Value
End Sub

✅ Automatically adjusts to the size of your dataset
✅ Works even if data length changes every day
✅ Perfect for automated reporting

---

✅ Copy Values from Multiple Sheets to the Current Sheet

・Example: Consolidate monthly sheets into one summary

Sub MergeDataToCurrentSheet()
Dim ws As Worksheet
Dim wsDst As Worksheet
Dim nextRow As Long
Set wsDst = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> wsDst.Name Then
nextRow = wsDst.Cells(Rows.Count, 1).End(xlUp).Row + 1
wsDst.Range("A" & nextRow).Resize(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count).Value = ws.UsedRange.Value
End If
Next ws
End Sub

✅ Automatically loops through all sheets
✅ Merges data into one consolidated sheet
✅ Great for weekly or monthly aggregation

---

✅ Paste Values and Keep Number Formatting

If you also need to preserve numeric or date formats (e.g., currency, percentages):

Sub PasteValuesWithFormat()
Dim wsSrc As Worksheet
Set wsSrc = Sheets("Input")
ActiveSheet.Range("A1:D20").Value = wsSrc.Range("A1:D20").Value
ActiveSheet.Range("A1:D20").NumberFormat = wsSrc.Range("A1:D20").NumberFormat
End Sub

✅ Maintains the professional look of your data
✅ Prevents “text stored as number” errors

---

✅ Paste Values to a Specific Start Position in Current Sheet

・Define where to place incoming data

Sub PasteValuesAtPosition()
Dim wsSrc As Worksheet
Set wsSrc = Sheets("Report")
ActiveSheet.Range("C5").Resize(10, 5).Value = wsSrc.Range("A1:E10").Value
End Sub

✅ Full control over destination
✅ Common when aligning copied data within a dashboard

---

✅ Paste Values from Filtered Data (Visible Cells Only)

When your source sheet is filtered, you can copy only visible rows.

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

✅ Skips hidden rows automatically
✅ Perfect for filtered exports or conditional reports

---

✅ Paste from Another Workbook to the Current Sheet

For multi-file operations, you can use:

Sub PasteValuesFromWorkbook()
Workbooks("SourceFile.xlsx").Sheets("Sheet1").Range("A1:D20").Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

✅ Works across Excel files
✅ Great for automation between system exports and reporting templates

---

✅ Combine with UiPath or Power Automate

In real business workflows, RPA tools frequently handle Excel operations. However, copying formulas can break automation because of dependencies or recalculations.
Using VBA to paste values only ensures stable, robot-friendly data.

Typical process:

1. UiPath opens Excel and triggers the VBA macro.
2. The macro copies values from the “Source” sheet.
3. Values are pasted to the current sheet (used as output).
4. UiPath continues workflow (e.g., saving and emailing file).

✅ No broken references
✅ Clean, formula-free data
✅ Compatible with automation platforms

---

✅ Error Handling and Safety Checks

Always protect your code against unexpected conditions:

Sub SafeValueTransfer()
On Error GoTo ErrorHandler
Dim wsSrc As Worksheet
Set wsSrc = Sheets("Data")
ActiveSheet.Range("A1:D10").Value = wsSrc.Range("A1:D10").Value
MsgBox "Transfer completed successfully!"
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical
End Sub

✅ Displays clear messages on failure
✅ Keeps automation stable even under unexpected input

---

✅ Performance Tips for Large-Scale Transfers

Technique	Effect
Application.ScreenUpdating = False	Hides flicker during process
.Value = .Value instead of Copy/Paste	Faster and lighter
Application.CutCopyMode = False	Clears clipboard
Limit range size	Avoid unnecessary data movement
Use UsedRange or LastRow	Handle dynamic data smartly

Example:

Application.ScreenUpdating = False
ActiveSheet.UsedRange.Value = Sheets("Source").UsedRange.Value
Application.ScreenUpdating = True

✅ Even large datasets transfer instantly

---

✅ Hands-On Exercise: Build a Daily Report Macro

Try the following practical exercise:

Goal: Copy daily results from “RawData” into the current sheet as static values.

Steps:

1. Open “RawData” (source)
2. Use VBA to copy UsedRange
3. Paste values into the active sheet
4. Clear copy mode
5. Save the result as a finalized report

Code Example:

Sub DailyReportTransfer()
Dim wsSrc As Worksheet
Set wsSrc = Sheets("RawData")
ActiveSheet.UsedRange.Clear
ActiveSheet.Range("A1").Resize(wsSrc.UsedRange.Rows.Count, wsSrc.UsedRange.Columns.Count).Value = _
wsSrc.UsedRange.Value
MsgBox "Daily report updated successfully!"
End Sub

✅ Simple, fast, and practical for real-world reporting tasks.

---

✅ Summary: Paste Values from Another Sheet to the Current Sheet Efficiently

• Pasting values only prevents formula errors and keeps data stable
• .Value = .Value is faster and safer than Copy/Paste
• Dynamic range detection keeps automation flexible
• Combining with UiPath / Power Automate ensures RPA reliability
• Perfect for dashboards, reports, and daily automated exports

By mastering value-only pasting between sheets, you’ll streamline your workflow, reduce risk, and make your Excel automation professional and future-proof.
Your macros will run faster, your data will remain consistent, and your reporting will always stay one step ahead.