Copy Column Data from Excel Sheet1 to Sheet2 Automatically Using VBA
HTML-код
- Опубликовано: 3 дек 2014
- How to copy specific data from specific columns in Excel Sheet1 to Sheet2 using VBA.
Complete details available at this link:
www.exceltrainingvideos.com/co...
![Chk Chk Boom - Stray Kids ストレイキッズ 스트레이 키즈 [Music Bank] | KBS WORLD TV 240719](http://i.ytimg.com/vi/63QPwqCRGcw/mqdefault.jpg)
You are a wonderful teacher sir. Even this 71 yr old vet can understand and learn. Thank you so very much.
Excellent and simple way of teaching.
Well Done sir! Good and simple to understand video for a novice in excel like myself.
Thank you, going through it line by line really helped
Thank you for your demo video. It is very useful to me.
Thank you Sir for this tutorial.
Hi Mr Dinesh. That was a great demonstration again.I have a query regarding the reports I work on daily basis.The scenario is every time when i pull the report from the database the rows and column will be increased/decreased,so for this I had declared default range like Range("B3:S1000").copy but I wanted a dynamic command where it copies the data towards the row side(end) & column side(end) and paste the info in another workbook/sheet.Could you please provide your insights?
Hi Dinesh I would like to have a demo on the following requirement. I would like to track the changes and need to copy the data to next sheet. Like if I have 10 rows in my excel when I started editing I would like to copy entire data of that to next sheet can you help me
I would like to transfer everything on Sheet 1 with formulas and heading to Sheet 2 can this be done with one copy and paste?
Hello, Dinesh
thank you for as always very useful video.
The video was great. I was able to recreate your example and it worked. I have tweeked the code for my use but it does not work. It does not error but it also does not do anything. My question for you would be how do I change the code when finding what you are looking for? In your example you are looking for "Notebook" in column 2. How would you change the code if column 2 said This product is a "Notebook". You are still looking for the word Notebook. Thanks
hi, sir i am from Cambodia, i work in garment factor as a staff do report, i want to make the smart form in excel run by vba code ( i use 2 sheets by one sheet for store data and one sheet for 1. search data, 2. insert data, 3. edit data
It's really helpful for me we have asked questions in today but no reply Sir have solved my problem after watch your these two videos.
Cool
Hi Sir,
your videos are excellent. i tried one macro related to this. But what is the problem is? some cells are merged row as well as column. so it didnt work. what will we do?.Please advise.
Sir, your tutorial is very usefull, Thanks
So nice of you! Please share the Excel tutorials with all your friends.
Hi and thank you in advance. I need to copy & send a table by email, but I need to hide some columns, not to be sent, without deleting them from the table, can you help me? Thank you
Hi sir. Thanks for such videos. I have one query. I have 7 columns and rows depends upon data entered needs to copy from ine sheet to another. But my problem is my data table is in the middle of the sheet. First 7 rows are projevt description and the. After data table i have some summarh. So pls adviae what would be the code. Pls sir need ur help.
Sir I am stuck in a project need help, how to contact you via email or do you have any website where I can upload data and ask you my doubts?
sir how insert data in in singal column
Excellent method of explanation, instead of copying the data from sheet1 to sheet2, if we want to transfer (cut and past) or once data is copied that data in sheet1 must be deleted, how can it be accomplished
thanks in advance
Hi Sir, could you help me about copy and paste a column data where formula has applied already one sheet to another sheet pls? I tried to do following ur class but it’s work for when there is formula applied only. Hope you would consider my request. Thanks n regards
Also the data where i want to paste, its also. In he same. Replicae of sheet 1.
Hello Dinesh...Can you Please make a video example where we can copy the data based on cell or whole row color( let say row whole color red) from sheet1 to sheet2.
It will be great!! THank you for your great efforts!!
Great video :).
I have a question regarding this code. I used your code for my excel file that contains 10 000 rows and its takes really long time (severals mins) for the vba code to check a specific work and copy/paste to another worksheet. Is there anyway I can speed up the process?
+Kim Chuying Try adding this line of code before the Macro runs: Application.ScreenUpdating = False Now when the Macro finished executing all commands add this line of code: Application.ScreenUpdating = True. This will keep the worksheet from showing the changes as they happen and save loads of time. Hope this helps.
superb masterji
Thanks for the feedback.
Dear Sir
Would you make Video Of Same Thing With Macro Please
It was useful, thank you!
Glad it was helpful!
Hello 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. Thank you for your help.
Hi
What do you mean with "if sheet1 is full". In one Sheet there are 1048576 Rows and Columns 'till XFD.
Please define "full"
hello eiger, sorry i meant if that specific cell has data - copy paste in the sheet2 instead of sheet1. In that way, I can use multiple sheets.
Quoit1 Hi Quoit1
Actually is really easy for VBA to do that. Just do exactly what did you say.
First of all make a backup your Muster file!
Secondly develope the code as you need it.
Use the macro recorder to do some test.
Check if the cell "B2" in Sheet1 is empty, elseif not check if the same in Sheet2 is it.
You can also use a FOR statement For each Sheet in thisworkbook, to check if the B2 empy is.
The code of Dinesh do not make sense in your case, you have to write something complete new for u.
bye
Your tutorial video are excellent. I need to know if my data is in the middle. I mean if i want to copy my data table which is the middle of the certain data then what would be the code. Assuming first 4 rows are project name and description and data column starts from 7th row and ends at row 45 and again some summary data is there, so now how do i write the code. Pls suggest.
Question is not quite clear. But let me try to answer. Let's say you wish to copy data from rows 7 to 45, then you need to select the rows using the CELLS property, use the copy command, go to the area where you wish to paste, let's say in column X and row 67, select that cell like so: cells(67,24).Select. Now you issue the paste command. In Excel it doesn't matter where your data is in the worksheet.
But it's a good idea to always input your data in an Excel worksheet properly. This makes analysis much easier - with or without VBA. Here's a link to an interesting article about 'Guidelines for entering
data on a worksheet' support.office.com/en-us/article/guidelines-for-organizing-and-formatting-data-on-a-worksheet-90895cad-6c85-4e02-90d3-8798660166e3
Show less
I used your code but it wont input the data with the macro it only inputs the column titles help would be great.
Hi Walter
I've developed a code with data imputbox, so you can choose what kind of product you want to copy on sheet2 if the price is above 2000.
Let me know if you need more settings.
Enjoy it
Sub copypastecDATA()
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")
On Error Resume Next
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) >= 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
Namaste Takyar,
This is my 1st comment in youtube.
Thank's a lot 4 all your helpfully codes you develope, and specially thank to share it with us :) Sukra.
Please i don't want absolutely offend you, i took fiew minute to implement you code with an input box.
I thought is to limitate to select one product manually in VBA editor.
Do you think, may i send you the code or may i post it here down?
One more time thank
Eiger67
Hi Mr Dinesh Kumar. I have watched your video. Thanks for explaining . I did the same and everything was OK. But I have a question. How to paste information to the next column everytime when I copy it? could you please explain?
+Sarkhan Tariverdiev You have to specify the column in the 'destination' part of the code or use an appropriate looping process so that the data goes into your required column.
Hello, i have watch a few of your videos and it is very helpful. But i have some problem. I have a table of 10 people. They will give me different data each day. Is it possible to automatically filter their names A to Z each time i copy and paste the table to excel without the need to press the filter button?
Hi
Do you have a sample file?
Thank you sir, I have one question if i have 2 nos worksheet in excel sheet with different header and i want to transfer data from one worksheet to another worksheet for specific column. Please Help and hope for create video.
www.exceltrainingvideos.com/copy-column-data-automatically-from-excel-sheet1-to-excel-sheet2-with-vba/
@@Exceltrainingvideos Thank you so much sir for instant reply and very useful to me.
Hi
I am stuck with a problem hope you can help me with a solution
I have sheet 1 named as MENU and Sheet 2 Named as Master List with product name and details
so when I Enter Name in MENU I want excel to give me details of the product by searching the sheet 2
is it possible
Looking forward
Do a search at www.exceltrainingvideos.com
Hi sir kindly help me for same programme same i had copy but it is showing some error in 5 th row
Copy/Past of code will not work.
Sir,can we write code sothat to transfer required data into sheet2 without using run button or command button automatically like we using normal formulas linking sheet2 from sheet1
You can assign the macro to a keyboard shortcut.
Thanking you sir...
Superb sir
Thanks!
sir i didnt understand function of erow and last row
Have a look at this link: www.exceltrainingvideos.com/tag/find-last-column-automatically/
Or do a search here: www.exceltrainingvideos.com
case 1. search data, and 2. insert data i see your RUclips i can do it already, but case 3. search data come and edit data and replace it with edit i can't do vba, please can you make out the RUclips in case 3. sir ? thank you first Sir
+Yeng Roth015 Have a look at this link: www.exceltrainingvideos.com/tag/manage-database-with-userform/
Sir same program code I am getting run time error 424 object required
Recheck your code!
Can you help me in this line
if Cells(i, 4) = "" Then "" in the Cell i have three different Words that have string "Mass B ( Ecco-" , how can i let the line do something after finding the this Part of a word
in the Sheet
MsgBox "String found!"
or cells(i,8)=strItem
@@Exceltrainingvideos check this its not working
For i = 2 To lastrow
If Cells(i, 4) =stritem "Mass B" Then
Call_Time = Sheet1.Cells(i, 3)
Functions = Sheet1.Cells(i, 4)
Manager = Sheet1.Cells(i, 5)
i tried the below
If Cells(i, 4) = "Text 1" And "text 2" And "Text 3" Then
I got lot of information
Please share the VBA tutorial on social media too!
Thanx
Hi SIr i am getting a data out of range
Check your code line by line.
Hi Dinesh, continuing from my last comment, I am looking for something like:
if Sheet2 Range("B2") ""
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 pasting on different sheets. Thanks!
Hi
Sorry i don't understand exactly what you mean.
SO?
if Sheet2 Range("B2") ""then
Sheet2.Cells(erow,1) = Product_Name and Sheet2.Cells(erow,3) = Product_Price
Sheet3.Cells(erow,1) = Product_Name and Sheet2.Cells(erow,3) = Product_Price
Sheet4.Cells(erow,1) = Product_Name and Sheet2.Cells(erow,3) = Product_Price
end if
eiger eiger
Thank you eiger, but I dont think that will work in my case. I have a single master file that is used to keep historical data. If I have to copy column data from that to master file, it should go in sheet1. Now if I have a second file with column data, that should go into sheet2. I do not want to write-over or erase any data. Your solution will only look at sheet2 range("B2") and if thats not empty - it will only copy-paste same data multiple times in sheet2,sheet3,sheet4 (it might also over write). Let say if you already have data in sheet3 and sheet4, so code should skip those sheets and paste column data in sheet5 only.
hello sir I have 1 query
if I want to add good 1 by 1 in next row by choosing a particular goods how can i do that by using vb
Reframe your question.
Super explanation.
After completing everything, I tried to run, but gets error:
"Compile error:
End If without block If"
Please rectify this error.
The error is self-explanatory: you forgot some code related to the construct:
If (condition) then
do something
End if
Recommended: Check the code line by line.
Copy the cell value of 200 from cell A1 on Sheet1. Go to Sheet2, click in cell A1 and click on the drop-down arrow of Paste button on the Home tab and select Paste Link button. It will generate a link by automatically entering the formula =Sheet1!A1 .
Dear Can you make one Video how to Do
i have one problem to Copy multi lines to 2nd sheet, is it possible to copy or select 4 or 5 lines and past 2nd sheet with single click
Sir I have similar type of question. Can you please look into this and give proper solution:
I have data in Column A and B in Sheet2 and I have taken some of data from Column A and paste them in column A in Sheet1 and now I want to import data from Sheet2 Column B for matched data of column A in both sheet. I have used below logic to do this but getting error.
For k=2 To 400
Cells(k,2).Value = WorksheetFunction.Index(Sheet2!Range("B2:B1255"), WorksheetFunction.Match(Cells(K, 1).Value, Sheet2!Range("A2:A1255")))
Next k
This link will help: www.exceltrainingvideos.com/tag/using-index-with-match-in-vba/
Thank you very much sir for this tutorial.... Sir is there any way to solve my question without using index-match or any other lookup formula in VBA?
Hi Dinesh Kumarji. I have copied data from Sheet1 to Sheet using this code:
Sheets("Trades").Cells(1, 2).Copy
Sheets("Archive").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is working fine. Now the question is like this: Data in cell (1,2) can be in uppercase or lowercase. But the destination cell must contain data in UPPERCASE only. How to ensure this?
+Deepak Kamtikar Let's assume your data is in column1. Now you can use a looping process like so:
For i = 1 To 3
Cells(i, 1).Value = UCase(Cells(i, 1).Value)
Next i
+Dinesh Kumar Takyar , yes. This is working fine. Thanks a lot for instant reply Deneshji.
+Deepak Kamtikar
There is more more way to force uppercase entry like this. If my data is in Sheet1 and I wish to ensure Uppercase entry in cell A1, then following code in sheet1 may do the job:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address="$A$1" Then
Call Macro1
End If
End Sub
And Macro1 in Module1 is:
Sub macro1()
Sheets("sheet1").Cells(1, 1).Value = UCase(Cells(1, 1).Value)
End Sub
This way, when user enters data in cell A1 and hits enter key, the data will be automatically converted to Uppercase.
Regards.