We have learnt how to transfer data from rows from one Excel worksheet to another. How can we copy column data automatically from Excel Sheet1 to Excel sheet2 with VBA. We can refine the process of extracting specific data from specific columns and then transferring the data to a new worksheet using VBA. This automation of copying data from one worksheet to another Excel worksheet can help to analyze data and create excellent reports quickly and easily.

The automated process of copying data from columns of one Excel worksheet to another involves the following steps:

- Decide into which worksheet you wish to copy the data
- Clear this sheet of all data to avoid duplication
- Assign appropriate headers to the appropriate cells like Product Name to Range A1 and so on
- Now activate the sheet from where you wish to get the data
- Based on a condition or conditions loop through all the data
- Capture the data that meets the criteria into variables
- Now go to the sheet where the data is to be placed
- Find the first blank row in that sheet
- Place the captured data into appropriate cell range(s) via the variables
- Go back to the data sheet
- Keep on looping through all the data in this manner
- Make sure the data in the ‘report’ sheet is viewable by auto fitting the columns
- Come back to the data sheet

Watch the training video before you start studying the Excel VBA code:

You can view the video also on YouTube.

Download a sample file by clicking on the Excel icon:

The complete VBA code to automate the process of copying data from columns from one Excel sheet to another:

Sub copypastecolumndata()

Sheet2.Select

Sheet2.Cells.ClearContents

Range(“a1”).Value = “Product Name”

Range(“B1”).Value = “Price (Indian Rupees)”

Sheet1.Select

Dim Product_Name As String

Dim Product_Price As Single

Dim lastrow As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

If Cells(i, 2) = “Notebook” And Cells(i, 3) >= 20000 Then

Product_Name = Sheet1.Cells(i, 1)

Product_Price = Sheet1.Cells(i, 3)

Sheet2.Activate

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheet2.Cells(erow, 1) = Product_Name

Sheet2.Cells(erow, 2) = Product_Price

Range(“A:B”).Columns.AutoFit

Sheet1.Activate

End If

Next i

End Sub

Eiger67Namaste Takyar

I would like implement this code with an Inputbox, i think to change the product to find in the VBA editor is to limited and is not work that anyone can do.

Please don’t get offened if i do this, i consider you as a big teacher.

Here my pice of code what do you think about it:

Sub copypastecolumndata()

Sheet2.Select

Sheet2.Cells.ClearContents

Range(“A1”).Value = “Product Name”

Range(“B1”).Value = “Price (INdian Rupees)”

Sheet1.Select

Dim Product_Name As String

Dim Product_Price As Single

Dim lastRow As Long

Dim i As Long

Dim sSearch As String

Dim eRow As Long

lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

sSearch = InputBox(“Please select the Product to find”)

Set x = ActiveSheet.Columns(“B”).Find( _

sSearch, LookIn:=xlValues, lookat:=xlWhole, _

searchorder:=xlByRows)

If x Is Nothing Then

MsgBox “The selected Product is not available!” & vbCrLf & _

“Pleas select another one.” & vbCrLf & _

“Thank you”, vbInformation

Exit Sub

End If

For i = 2 To lastRow

If Cells(i, 2) = x And Cells(i, 3) >= 100 Then

Product_Name = Sheet1.Cells(i, 1)

Product_Price = Sheet1.Cells(i, 3)

Sheet2.Activate

eRow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheet2.Cells(eRow, 1) = Product_Name

Sheet2.Cells(eRow, 2) = Product_Price

Range(“A:B”).Columns.AutoFit

Sheet1.Activate

End If

Next i

End Sub

Have a nice day

Eiger67

Quoit1Hello Dinesh! Thank you for your videos. Question: How would you modify this code such that if sheet1 is full, paste column data in sheet2. If sheet2 has data, paste in sheet3 so on. I am looking for something like:

if Sheet2 Range(“B3”) “”

then Sheet2.Cells(erow,1) = Product_Name and Sheet2.Cells(erow,3) = Product_Price

Else go to Sheet3…so on. This way I can run code multiple times transferring column data on different sheets. Thanks! Quoit1

AfaqueI would like to generate a code where it copies date to a specific sheet based on a user form to another sheet which is linked to a model.

For example: my sheet would compose of multiple projects and six monthly results. These results changes based on percentage of completion for each project. The user can change result based on percentage of completion for each project. further, I would like to control percentage of completion should not exceed 100% for any given project.

Please send me your email address.

Thanks in advance

Afaque

YOGESH LADDear Sir,

pls help,

i have one data excel file sheet 1 all data , sheet 2 VLOOKUP & data printing box

i am vlookup doing in sheet2 only 2nd row .i want to each code vlookup data copy to sheet3.

carlosdid exatcly like you did but i get

run-time error ‘424’ object required

carlos“did exatcly like you did but i get

run-time error ‘424’ object required”

i discovered what i did wrong! In the vba program I need to substitute Sheet1 and Sheet2 for the equivalent of my excel language which is Folha1 and Folha2

srimanii am still getting the error i used folha1 and folha2 also can you please help me out