Copying data from one worksheet to another is a pretty often and routine scenario. Fortunately, we have a way to VBA Copy Sheet data from one to another using Excel VBA Macro. What is more there are many ways for us to approach this presumably simple problem.

VBA Copy using Range Copy Function

A simple way to copy data between worksheets is using the VBA Range Copy function. We can do this in many ways

Sub CopySpecifcRange()
    Dim sourceWs As Worksheet, dstWs As Worksheet
    Set sourceWs = Sheets("Src")
    Set dstWs = Sheets("Dst")
    
    Call sourceWs.Range("A1:E5").Copy(dstWs.Range("A1"))
End Sub

Below the simple steps covered above:

1. Declare and define your source and destination worksheets (“Src” and “Dst”)
2. Use Range Copy function to copy the specific range from source to destination worksheet

This is the view before of the example worksheet:

This is after using the VBA Range Copy function:

VBA Copy only on the UsedRanged

What to do however when you don’t want to bother with checking the used range of your source worksheet and just want to copy it whole? You can use the UsedRange attribute of the Source Worksheet – see example below.

Sub CopyUsedRange()
    Dim sourceWs As Worksheet, dstWs As Worksheet
    
    Set sourceWs = Sheets("Src")
    Set dstWs = Sheets("Dst")
    
    Call sourceWs.UsedRange.Copy(dstWs.Cells(1, 1))
End Sub

What’s the difference? In the above code snippet instead of copying a specific Range, we copy the entire UsedRange of the source Worksheet. The UsedRange is always any rectangle Range of cells that has been modified in any way in the Worksheet i.e. edited, color change, border etc. A UsedRange always has a starting and ending cell i.e. closest and furthest from A1 that have been modified. In my case the result was the same but the code above is much more convenient.

VBA Copy and paste values only

The VBA Copy Range function will copy the entire cell contents including formatting, borders, conditional formatting etc. Sometimes you want however to copy a variety of selected information or only the cell values without formatting. For this we would need to modify the macro above:

Sub CopyValuesOnly()
    Dim sourceWs As Worksheet, dstWs As Worksheet
    
    Set sourceWs = Sheets("Src")
    Set dstWs = Sheets("Dst")
    
    sourceWs.Range("A1:E5").Copy
    
    Call dstWs.Range("A1").PasteSpecial(Paste:=xlPasteValues)
End Sub

This time we modified the last two lines of the previous procedures. We first copy the selected Range from the source Worksheet. Then we use the VBA Range PasteSpecial function to paste the Range from the clipboard with only values to our destination Worksheet. This is the effect:

Conclusions

To VBA Copy Sheet data is simple and is regular use case. Do also check out my SQL AddIn for how to use SQL to copy data for one or multiple Worksheets.