How to Copy Data from One Worksheet to Another Using VBA in Excel

Why Automate Copying Data Between Worksheets?

Copying and pasting data manually can take up valuable time and introduce mistakes. With Excel VBA, you can automate repetitive tasks and ensure consistency across your reports, dashboards, and summaries. This approach:

• Saves time by automating repetitive copy/paste tasks
• Eliminates human error
• Works instantly at the click of a button
• Scales easily for larger or multiple datasets

Step-by-Step: Copy Data Between Worksheets with VBA

Here’s a simple macro that copies data from one worksheet to another.

Sub CopyData()
    Sheets("Sheet1").Range("A1:D10").Copy
    Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    MsgBox "Copy complete!"
End Sub

How it works:

1. Copies range A1:D10 from Sheet1.
2. Pastes values into Sheet2 starting at A1.
3. Displays a “Copy complete” message box when done.

How to Assign the Macro to a Button

To make this process even easier, you can run the macro from a clickable button in your sheet:

1. Go to Insert → Shapes and draw a shape (like a rectangle).
2. Right-click the shape and choose Assign Macro.
3. Select CopyData and click OK.
4. Now each time you click the shape, Excel copies your data instantly.

Dynamic Copy Example (Automatically Detect Data Range)

If your range changes frequently, use this version that finds the last used row automatically:

Sub CopyDynamicRange()
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRow As Long

    Set wsSource = Sheets("Sheet1")
    Set wsDest = Sheets("Sheet2")

    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    wsSource.Range("A1:D" & lastRow).Copy
    wsDest.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    MsgBox "Dynamic copy complete!"
End Sub

Practical Uses for This VBA Macro

This code is incredibly versatile. Here are some real-world uses:

• Copying daily reports or transaction data to a master sheet
• Consolidating team submissions into one summary
• Moving filtered results or calculations to another tab
• Preparing dashboards or printing summaries automatically

Get More VBA Examples

Visit vbahowto.com for more free VBA snippets, templates, and automation tutorials. Learn how to:

• Copy data dynamically
• Automate chart creation
• Clean and format data
• Send automated emails with Excel data