How to automate copying of column data from sheet to sheet using Excel VBA. We have earlier learnt how to transfer row data from one worksheet to another with Excel VBA. Sometimes you may need to copy specific column data from one worksheet to another quickly and automatically. The Excel VBA code below explains step by step how to perform such a transfer of column data from one worksheet to another. Before studying the VBA code watch the training video:
You can watch the video also on YouTube.
Sub copycolumns()
Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 6) = “Maharashtra” Then
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)
Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)
Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 3)
End If
Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select
End Sub
Download a sample Excel file:
Hi can you please help I have created a userform which on opening the workbook is showing on sheet2 but I want it to open and stay on sheet1 and when I enter data I want it to go onto sheet2 in rows. I have given names to the three sheet tabs at the bottom which is confusing
Thank you very much for all your tutorials! They are very helpful!!
I have used your sample to start my document but now every time I press ‘Copy Data’, it duplicates the information every time.
Can you kindly advise on how to not have duplicates please.
Dear Sir
when I try to run the above above code I get an error in the 3rd last row. I have copied the code as mention above. Pls help
Sheet2.Columns().AutoFit
run time error ‘424’ object not defined
You could change it by the following line codes :
Worksheets(“Sheet2”).Columns(“A:F”).AutoFit
Sheets(“sheet2”).Select
Sheets(“sheet2”).Range(“A1”).Select
I’ve attached the working code. There was a slight change on the last Range statement. Also, if you copy/paste the code from this page, some characters, like “, will not paste correctly. It is best to copy/paste the code to a notepad to remove any special characters, then copy into Excel. This gentleman’s code is some of the best I’ve seen – clean and simple.
Sub copycolumns()
Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 6) = “Maharashtra” Then
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)
Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 2)
Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 3)
End If
Next i
Application.CutCopyMode = False
Sheet2.Columns.AutoFit
ActiveSheet.Range(“A1”).Select
End Sub
hello sir, great lessons!
This code is giving me a Runtime error, subscript out of range error.
I have simply copy pasted this.
I know this sounds silly, but being a beginner Im just not able to figure out what the issue is
Can anybody help me, please?
Thanks so so much!!!
I am having a problem with this code. Here is what i have:
Sub copycolumns()
Dim lastRow As Long, erow As Long
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets(“Sheet2”).Cells(erow, 1)
Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range(“A1”).Select
End Sub
I am getting the “Subscript out of range” error on line 9, I can’t see why it will not Paste.
Can you PLEASE help me,
Much thanks in advance,
Bill
Sir I have a vba code and i want modification in that. If I want to copy particular column like column c, column F, Column I, Column K etc. of sheet1 and pest in Shett2 in the Column A, Column D, Column G, Column M etc…..and the filter should be as it is that is given in the following code…
So would you convey me by my mail i.e. bhaiswarpravin@gmail.com
Option Explicit
Sub copyDatabyCondition()
‘copy data if column S isn’t empty
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim gSourceRow As Long, gSourceTotal As Long
Dim gTargetRow As Long
‘intialization
Set wsSource = ThisWorkbook.Sheets(“INPUT”)
Set wsTarget = ThisWorkbook.Sheets(“Sheet2”)
‘find the last row in both tables
gTargetRow = wsTarget.[A65536].End(xlUp).Row
With wsSource
‘for each row in the source table
For gSourceRow = 11 To .[A65536].End(xlUp).Row
‘column S isn’t empty?
If .Cells(gSourceRow, “S”) 0 Then
gTargetRow = gTargetRow + 1 ‘increment row no. & appending a new line
‘copying a line from source table to target table
wsTarget.Range(“A” & gTargetRow & “:N” & gTargetRow).Value = _
.Range(“L” & gSourceRow & “:Y” & gSourceRow).Value
End If
Next
End With
End Sub
I have formulas in the sheet 1 cells and want to paste them as values. How do I do that? Please?
Hi,
I am using the code above for achieving copy and pasting based on the specific criteria. But how do i set the criteria based on the following:
I have following data in the column:
D14WS108,D578S108,D72SS108,D14578S105
And i want the data that start with D14 copied over to tab called D14 and the data that starts with D57 move it over to tab called D57
I put in the following code for critiera selection but it doesn’t work
If Sheet1.Cells(i, 26) = “D14” Then
Can you please help
Hi,
I followed advice on how to copy/paste data from one sheet to another based on specific condition. In my case, I have written the macro (which I could send via email), however I believe that due to the number of .select and .activate operations I am getting the error “Run time 1004” method range of object worksheet failed sometimes I also get the run time error 13. DO you know what could be possibly happening? Is there a way of not using .select and .activate for this same example.
Thanks
HELLO BRO I WANT TO US VBA IN EXCEL BUT WHEN I CLICK ON BUTTON IT SHOW THE MACRO MAY NOT BE AVAILABLE IN THIS WORKBOOK OR MAY BE DISABLE
BUT IN MY EXCEL MACRO IS ENABLE AND I SAVED IN MACRO ENABLE EXCEL SHEET FORMAT
WHATS THE PROBLEM PLEASE HELP ME OUT
Hello,
I tried these three methods and they didn’t work
str1 = “C” & CStr(i + a) & “:D” & CStr(i + a)
str2 = “B” & CStr(lastrow + i)
ThisWorkbook.Sheets(“Sheet2”).Range(str1).Select
Selection.Copy
ThisWorkbook.Sheets(“Sheet1”).Range(str2).Select
ActiveSheet.Paste
‘ Sheets(“Sheet2”).Select
‘ Range(str1).Select
‘ Selection.Copy
‘ Sheets(“Sheet1”).Select
‘ Range(str2).Select
‘ ActiveSheet.Paste
‘ ws2.Range(ws2.Cells(3, i + a), ws2.Cells(4, i + a)).Copy Destination:=ws1.Range(ws1.Cells(lastrow + i, 2), ws1.Cells(lastrow + i, 2))
‘ MsgBox ws1.Range(ws1.Cells(lastrow + i, 2), ws1.Cells(lastrow + i, 3)).Value
‘ ws1.Cells(lastrow + i, 2) = ws2.Cells(i + a, 3).Select
Non of them are working
thanx for the tutorial and the script. i have one question, how about i want use inputbox for the selected column and inputbox for the text that we want to filter. i just want to add inputbox for user doesn’t need to make change at vb code view. user can choose their selected column and text using inputbox. thank you.
hello thank you for your excellent topics
is it possible to change this macro to paste cells that contain formula from one sheet to another?
when i use this macro it show #ref in my destination cells!!!
Hello, I have a different situation where I’ve to do the following:
1. Workbook1 has 5 worksheets, and each worksheet has same layout (name of the columns and row count).
I have to select just a few columns from every sheet(A, C, D K, T, Z) from each worksheet and paste it below each other in a new worksheet in Workbook2.
2. Workbook1 is closed and I’m trying to fetch data from it by using the ADO connection.
I’ve not been able to figure this out yet after trying a lot.
Could you please help me with this?
Any help would be appreciated!
Thank you
Abhi
I want to generate receipt for my customers when i enter their data into sheet one and click print. Here I have some more information to collect from client side. but only need to print their name , mob and some other details into receipt. how can I do this with excel VBA?
Hi
I am looking to be able to automatically move a row of data from A – I from one sheet (Action Log) to another sheet (closed Actions) When the status in cells column I is changed to complete. can you please help me with this problem?
Kind regards,
Julie Ann
Where’s the sample file?
Sir i contacted you one of the post.
I need your help urgently.
Am. Sharing my email address. Would mind to.talk to me. So i can provide what is my requirment. Amd a sample data. Pls pls.
Dear Sir,
Very help full post. I was searching for this since very long. All working fine but only issue is that when i change the sheet names according to my excel code not working. My sheets name are “Master Collection” and Todays collection. I do not want to change my sheet. Also please help me with current month formula. My sheet has got date (do not want to Add month in excel) and i want code to copy data which falls under current month. Awaiting your response