VBA Macro to Dynamically Copy and Paste from One Sheet to Another

This blog post explains about to copy and paste from one sheet to another dynamically in Excel VBA.

Here we have two worksheets “Sheet1” and “Sheet2”.

We have some data in “Sheet1” and we want to paste it into “Sheet 2”.

It needs to be copied under the existing dataset in “Sheet2”.

So simple VBA code to copy and paste does not work here.

In this case, we need to implement some dynamic VBA code tricks to accomplish the task.

First, try to understand the situation of the data.

Sheet 1 has the below data from range “A2” to “E5”.

And we want to copy this Sheet 1 data into Sheet 2.

But Sheet 2 has already some existing data, see the images below.

So if we will copy Sheet 1 data into Sheet 2, it may copy upon the existing data.

Doing this will remove our existing dataset, which is not good at all.

Because keeping existing data the same is our top priority.

Here we will paste our dataset from Sheet 1 to Sheet 2 below the existing dataset.

So how will this will work here?

This is the one situation to copy data below the existing dataset in Sheet 2.

The second situation is that our dataset in Sheet 1 is taken from “A2:E5” and may increase or decrease in Sheet 1.

So again need to copy data dynamically.

Suppose our dataset range increases then our VBA code will copy all the data till it is existing.

This means this time we have data in Sheet 1 from range “A2:E5” and it may increase up to “A2:E10” or maybe more than this.

So need to create VBA macro code dynamically.

Dynamically Copy and Paste from One Sheet to Another

Here we will use a Variable that will select data until the last row of the dataset.

LCopyRow = Worksheets(“Sheet1”).Cells(Sheet1.Rows.Count, 1).End(xlUp).Row

This “LCopyRow” variable will select the dataset till the end, it does not matter how long your dataset is.

Now go to Sheet 2 and see the dataset here.

This is the place where our dataset needs to be pasted.

Here we need to find the next blank row of the existing dataset in Sheet 2 to paste our data from Sheet 1.

See image below.

But how to find the last blank row of the data.

So we need to apply a variable here also, which will find the next blank row below the dataset in Sheet 2.

LDistRow = Worksheets(“Sheet2”).Cells(Sheet2.Rows.Count, 1).End(xlUp).Row + 1

See at the end of the variable (+1), which indicates that this variable will find the last row of the dataset. And (+1) will go to the next blank row of the dataset.

This variable “LDistRow” will find the next blank row below the dataset in Sheet 2.

So here we have two variables, one for Sheet 1 and 2^nd one is for Sheet 2.

LCopyRow for Sheet 1

LDistRow for Sheet 2

The first variable will select the complete dataset from Sheet 1.

The second variable will go to the next blank row of the dataset in Sheet 2.

Then rest VBA macro code will work its job done.

Now we will use the complete VBA code to Copy and paste the dataset dynamically.

Sub Copy_Paste_One_To_Another_Sheet()
Dim LCopyRow As Long
Dim LDistRow As Long

LCopyRow = Worksheets(“Sheet1”).Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
‘this is the last row variable for 1st sheet

LDistRow = Worksheets(“Sheet2”).Cells(Sheet2.Rows.Count, 1).End(xlUp).Row + 1
‘this is the last row variable for the second sheet

Worksheets(“Sheet1”).Range(“A2: E” & LCopyRow).Copy _
Destination:=Worksheets(“Sheet2”).Range(“A” & LDistRow)

End Sub

This is our simple VBA macro to copy data from one Sheet to Another.

Now if your sheet name is different, you can use your sheet names in place of “Sheet 1” or “Sheet 2”.

Here, the VBA macro is used to copy and paste from one sheet to another.

If you want to learn more about Copy and Paste in Excel VBA, please visit Microsoft Office Support.

I hope this VBA code will help you.

Please feel free to put any comment or suggestion in the comment box below.

Other Related Post

How to Lock Scroll Area in Excel with VBA (Step By Step Guide)

How to Hide Blank Rows in Excel Using VBA Macro

3 Quick Ways to Move or Copy a Worksheet into Another Workbook

3 Quick Ways to Change the Font Color in Excel VBA

How to Split Cells Diagonally in Excel (Step by Step Guide)