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:
- Declare and define your source and destination worksheets (“Src” and “Dst”)
- 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:
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.