Automate Copying of Column Data from Sheet to Sheet Using Excel VBA
HTML-код
- Опубликовано: 6 окт 2024
- How to automate copying of column data from one worksheet to another using Excel VBA.
Complete code and sample file available here:
www.exceltraini...
Get the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
![Tee Grizzley - Robbery 8 [Official Video]](http://i.ytimg.com/vi/34_vkNV6wrU/mqdefault.jpg)
hi Sir! I just want to say thank you for your very helpful tutorial, it really help me a lot specially to my work and reports.MABUHAY!!
I been searching this kind of turtorial, after 9 mos. I found it.. wooohhhh❤❤❤ thank you so much, this is very useful..🎉🎉😊😊😊
Thanks Dinesh ji,
I am facing problem to copy data with specific data and after watching your videos problem has been resolved and now i am free to do my task with in a minute. Thanks again for your efforts and solute you too.
Glad that you were able to solve your problem!
Dinesh ji, I have another issue with the same sheet. I am able to copy data from master sheet to all but next time when i update master sheet again all data copied again. Can i copy only updated data or new data with existing data. Means ovrwrite or else.
You can first clear the earlier data. The last lines of code in this link will help: www.exceltrainingvideos.com/automate-search-display-print-archive-data-with-excel-vba/
Thanks sir ji this is not exact with my expectation but we can work with it.
I have created two task here first clear all data from all sheets and then start copy data through VBA. If we having any other option to copy only new changes in the sheets with existing data comparing it will help us.
Incredibly thorough and easy to follow. Thanks so much!!!
Thank you!
Thank you sir. This code solved my big problem.
Thank you. This is very useful for me, the only thing, i would like to know if for example for filter coloum, instead of name, if i want to select all dates and ignore nil, what code i should i use. Because the date has various dates so i can't use your code. Thanks for your help.
Thanks Dinesh for your quick and easy help. Just need to know how to add option to mention parameter required in command button i.e. rather than editing code to look for maharashtra, use get option to type maharashtra when pressing command button.
Hi there!
First of all I would like to thank you for all the Excel tutorials. I have gained a lot of new Excel knowledge through these tutorials.
I have a question regarding this code. Is it also possible to sort multiple "states" to multiple worksheets? I have tried multiple things, but I couldn't get it workable.
Do you have any advice how I could solve this?
+DjAurelius Can you elaborate with an example?
+Dinesh Kumar Takyar Thank you for your quick respond.
As an example. Now the data for Maharashtra is copied to sheet 2. But what if I want to copy the info of Delhi to sheet 3 and Tamilnadu to sheet 4?
+DjAurelius These two links might help:
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
www.exceltrainingvideos.com/tag/copy-paste-data-to-specific-worksheet/
seems like alot of people are have the same problem and it as simple as typing out the code as seen in the video. I think everyone's code will work if they place a " : " after the word "DESTINATION" , it should be DESTINATION:= OR Sheet1.Paste Destination : =Worksheets("sheet2").Cells(erow,2 )
Super!
Hello, nice video. What if you want to especify on the macro the column header name, instead of having column 1, 2, etc.?
Which code would you use?
Thanks!
Hello really nice video Mr Dinesh.My question is with this program can i choose a specific cell data and copy it in one cell in the other sheet?not copying all going from a verification first.It means that when i'll click on the name for example, the macro when executed will copy the name to a cell in the other sheet.
Hello sir. Thank you for the video. It is great. I do have a question about an UPDATE button. Let's say that I have set up everything correctly and my UPDATE button works fine to transfer row of information from Sheet A to Sheet B.
Okay. After I update, the next day I have another entry for sheet A. Now, when I push update, will ONLY the new information be transferred? Or will ALL of the information copy over itself?
How do I matching statement using VBA... will paste both the statement together on the same page.. and giving matching statement formula.. like if(countif(Range=&cell)=1,"^","x")How do I remove the common values.. and remove the older lines and add the new lines... I need your help on this please.. your videos are really awesome... thank you so much for your valuable I really appreciate it...
Dinesh, thank you for sharing so much information with everyone on RUclips. What I would like to do is copy multiple names from column 1 (Like Ethan, Ali and Alexis) onto another sheet. Can you help?
Sheet1.Range("A2:A28").Copy Sheet2.Range("A2:A28")
Awesome! Thank you for this. Very helpful and well presented.
Hello Sir,
I want to start by saying thank you for your well presented videos. They are extremely helpful. My question is, is there a way where I can transfer information to the next available column? This way instead of having one long cell of information I can have it broken down into shorter and more concise columns. Thanks.
This line of code will find the next blank column:
eColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Thank you sir
You all probably dont care at all but does someone know a tool to get back into an instagram account?
I stupidly lost the account password. I love any assistance you can offer me.
Hello Mr Dinesh
The method to Automate Copying of Column Date from Sheet to Sheet Using Excel VBA was very helpful
However, if the Data to be copied contains formulas it does not work properly
I wonder if there’s a way to paste the data as Values?
Perhaps you have the answer
Try paste special. This link will guide:
www.exceltrainingvideos.com/paste-special/
www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
Sir very useful videos .....can you share some video for pivot creation
ruclips.net/p/PLFoKoDG_7gtJbKNbtOxqvPlTg2Dyvzqus
Awesome!! much appreciated - you saved me a lot of time with this
Glad it helped! Please share with your friends too.
Hello Mr. Dinesh Kumar Takyar,
I am Sam Thank you sir your tutorials have been really helpful. Sir I have question please help me. I automate copying of column data from Sheet1 to Sheet2. now I want to do same formula in sheet3 and sheet4. Just like sheet2 automate generate "Maharashtra" sheet3 generating "Tamilnadu" and sheet4 "Delhi" on just one click.
please sir I am very great full to you
do this same as soon as possible.
Im working with named work sheets eg Capture form, Province, district etc . when i apply the code, im getting a "Runtime error 424 'Object required'. it only works with default sheet names
Thank you for this and your all the videos.
While the video shows techniques for selecting ranges, if you want to actually solve this problem, the Advanced Filter does it very simply, including the conditional logic. Automating it is one line of code.
+Paul Goldberg ruclips.net/video/TZptQpmW2Xs/видео.html
Hi Dinesh Thanks for your video!! In this example you have only 6 variables so u know wat all the cells to copy.
Say for example we have 140 variables out of that 40 variables we need to copy and paste and every time position of the variable headers are not fixed ( product may come in 1 st column r 100 th column) in this seario we need to find the variable and the copy and paste right so hhow to go about it ?? using looping
Hello Sir, I am very impressed with your videos. It is very helpful for us.
Sir I need your help, have tried this code for my use but facing some error in this.
Can you please guide me on this ? It means a lot for me sir.
How can i contact you ?
Thank you sir. Your tutorials have been really helpful. I am using one of your codes but coming up with a problem when I want the data copied to Sheet2 Columns 16,17,18 instead of Columns 1,2,3. The code works fantastic as is but I need to have the data go into a specific columns on Sheet2 - which on my end is a Dashboard with charts and summary data from sheet1.
What I'm seeing on my end is that it will complete the first "Due" that the macro sees but will stop and not include the other rows that are "Due"
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)= “Due” Then
Sheet1.Cells(I,1).Copy
Erow=Sheet3.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
Please help! Thank you in advance
thank you for yr video. could you make videos to guide about array in vba and how to apply it ? many thanks.
Have a look at this web link: www.exceltrainingvideos.com/populate-listbox-on-user-form-using-arrays/
You may also like to do a search at exceltrainingvideos.com
Very helpful...almost solves my problem...Can you reference a criteria cell instead of hardcoding in the criteria. I need to be able to pull data for a specific date. I want my user to be able to change the date in a field and have the macro look at that date cell to pull the rows that meet that date field. Is that possible?
Of course! This link will guide: www.exceltrainingvideos.com/refer-cells-using-index-numbers-vba/
Can you please show how to got the copy data button to work before you altered it for only specific data?I need my button to do exactly what yours did at the start of this video. Thanks!
Thank you very much for your videos, they are extremely helpful.
I have a question, how can i copy the cell value only. I have formulas in the cells and i want to copy and paste into sheet 2 without formulas, could you please advice how to edit the code to do achieve this. Thanks for your time much appreciated.
Assume we are copying data from cell c1 in sheet1 to cell c1 in sheet 2 and the data in sheet1 is the result of a formula:
Sheet1.Range("C1").Copy
Sheet2.Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues
@@Exceltrainingvideos Thank you for your reply and your time, much appreciated.
Is there a way to use ".cells(i,1)" function and not the function ".Range("C1")
For e.g. --> Sheet1.Paste Destination:= Worksheets("Sheet2").Cells(erow,1).PasteSpecial Paste:=xlPasteValues --> this is modification of the vba code from your video above.
I have to use a for loop and and the way you designed the logic of the vba code in the above video exactly fits my application case. With the ".Range" function I dont know how to integrate the variable "i" from the for next loop, on the contrary the ".cells(i,1)" funtion is best suited to work with for next loop.
I am trying to find a possibility to modify the ".Paste Destination" function implemented in your vba code as it works perfectly with ".cells" funtion which in turn works perfectly with for next loop. The idea is to modify it in such a way that any possible format is omitted that is to say only the value from the cell is extracted or copied and pasted into sheet 2
Thank you for your advice.
Thank you for the video. If I want to take information from multiple sheets and add them to one sheet? How do I do that? For example, 6 department budgets that roll up to a master budget; each budget will have expenses added on a rolling basis. Thank you for your help.
This link will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
Or do a search at: www.exceltrainingvideos.com
Hello again!
Now it runs ...
Thank you very much, Sir!
+Sebastian Hashimi Great that you got the macro running!
+Dinesh Kumar Takyar
Hi Sir, could you explain how you solved the issue, since i am also experiencing the same issue.
Many thanks in advance!
+Dinesh Kumar Takyar What if i add a new row of entry in sheet 1 and i want it to automatically update the selection in sheet 2 without repeating the entire previous selection already copied to sheet 2.
Very Nice & Simple code.
Hello Sir,
I have one difficulty while changing entire row font color, I have a shared worksheet which have limitation to not write the macro and disabled the formatting colors options in which we have maintained the due dates information by date wise deliverable item like:
if due date = 03/15/2015 16:00 PM (US/Date/Time), now we have to automate the like which item is due today, tomorrow and "Have more time" with red color for due today and some other color for tomorrow etc..
Please advise and help.
I have a challenge for you here so this tutorial will be one of it’s kind.
Why don’t you make the process interactive by giving the user the ability to choose which columns he wants to copy interactively? This can be done logically as follows:
1- Make a table of two columns on a separated sheet i.e Dashboard sheet.
2- Read all column headers of source sheet and write them in the first column of dashboard sheet.
3- Populate checkboxes in the second column of dashboard sheet.
4- Make VBA loop that reeds correspondents selected column headers and paste them in the destination sheet.
I hope you got the idea and be able to apply it.
Hi Sir, Thank for your video.
I have a question for you. I want to copy the first row title from sheet 1 to sheet 2, and then whenever I insert or delete a column in that title row in either sheet 1 or sheet 2, the other will automatically insert or delete that column for me. Can you help me with that?
Thank you it helps me a lot, but i have few questions what if the states or city and sheet number are variable, what would be the code in VBA, sorry I dont know how to code VBA. thanks in advance..
Have a look at www.exceltrainingvideos.com for learning VBA.
hello Dinesh Sir...Love all your videos...I have combo box control for selecting months in main worksheet...if I select January all the data should go in Jan worksheet automatically and if select Feb. month ,all the data should copied in Feb worksheet? How can i do it?
Do a search at www.exceltrainingvideos.com
which video should I look?
Thanks for your great videos but still can you please help me out in Copying data from specific column and pasting it on other sheet column beside column. I don't want to paste date in the next empty row but I want to paste data in the next empty column. So many videos I saw but unable to find the solution.
Hi sir,
Can you teach us for how to prepare "Facility Management excel Sheet" which works automatically and pull data from many check lists....
Revert awaiting...
Hello!Great video. Is it also possible tot let the workbook first find out if the line isn't already existing?Let's say the first collumn is number 11220, when copying to the other workbook it first search for this number and then place it on that line.
Of course. You can use 'countif' to check whether the data being copied is a duplicate.
What is the best way to fill this in using VBA?
HI Sir,how to add multiple specific names to copy or to provide range of names to copy data
I would like to automatically copy data from sheet 1 to sheet 2. I want to copy the data from a cell on sheet 1 that spans 30,31 j,k (sorry, you might describe it as 30j -31k) into cell c2 on sheet 2. Thank you.
Hi there! Your videos are great and very helpful, but I have a data set that I am struggling with currently. I am trying to copy info from a master list to a new sheet based on certain criteria, but I have ~10 different "criteria" that I want to use... do you have an email address that I can contact you or send you the file as an example? Any help is greatly appreciated! Thank you in advance!
Hello Sir, Thank you so much for this very usefull tutorial. In this example you explained how to copy data only if the condition "Maharashtra" is met, but let's say that I also want to copy data if "Delhi" is met which is a condition in same column. How does the code needs to be modified? could you please help me? Thank you Sir in Advance.
+Carlos and Ruth Ramos Use 'AND' like so:
If cells(i,2)="Maharashtra" AND cells(i,4)= "Delhi" Then
Sir, thank you so much for the response. I tried to use the code you instructed however it doesn't copy the data. I noticed that for second criteria you wrote cells(i, 4) but I need the loop to go through same column and returned the values everytime it finds both in same column "Maharashtra" AND "Delhi".. any suggestions why you may thing this doesn't work? Thank you for your help!!
I am having the smae problem, please sir could you guide? thank you in advance
Hello Sir, I am getting the same error Run time error 424. object required
Very nice but I want How to calculate various calculations ?
Dear Sir,Greeting of the Day,Its very nice & useful also,but i have a problem when i enter some new data in sheet 1 and click on copy data then all data which is in sheet1 is again copy to sheet 2 below the first copied data.means same data again copy with new entry,Please help me out. Thanks.
You should clear the data in sheet2 before copying all the data again from sheet1.
thank u sir and one more help needed how can paste same text or name in any cell by double click option ??? how to write macro for it
You can use the Worksheet_BeforeDoubleClick event:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'code goes here
End Sub
thank you for this tips very usefull.
but how if I just want to copy value from sheet1 to another sheet ?
because the result if I use this method is like copy paste all including cells format.
thanks for help
This link will help. Instead of using 'Paste' we use 'PasteSpecial':
www.exceltrainingvideos.com/tag/generate-reports-automatically-in-excel-using-vba/
Eg.:
Worksheets("Sheet1").Range("A1").Copy
Worksheets("Sheet2").Range("D2").PasteSpecial Paste:=xlValues
Sir..my query is similar to this video..its just that the data should be copied to other worksheet skipping one row..pls help
Thanks again Mr. Kumar for this video, but what if the Headers on both sheets are changing from time to time, how can you apply the (find) method instead of fixed (range) (cell) or can the headers values that need to be found put in a collection (which contains the headers wanted) then search for it and copy from first sheet then again search on next sheet and paste
It's only the column numbers that matter.
@@Exceltrainingvideos yes, but for example what if header first name next month changed from column 1 to column 6 in sheet 1 and changed from column 1 to column 4 on sheet 2 and you don't need to change the code every time the header is changing between columns so how to apply the find method between the two sheets
Putting headers into a collection is a brilliant idea.
Very nice video sir lekin copy button & other buttons userform se kaise opret/use karenge
Please search this channel or my website www.exceltrainingvideos.com
Hi
how to copy data in sheet1 of an excel book to outlook, using macro, request the simplest minimal #LOC, by the way your KT is very good
Thanks
This is very helpful sir. But, how can i copy with coloumn names
These VBA tutorials in Excel will help: www.exceltrainingvideos.com/copy-column-data-automatically-from-excel-sheet1-to-excel-sheet2-with-vba/
www.exceltrainingvideos.com/how-to-copy-column-data-into-another-workbook/
Or search my RUclips channel or website,
Thank u very much Sir,
Kindly guide me on d following.
If we have sales details, of Jan to August in Sheet 1.
After we advance filter them Sales representative wise in separate sheets.(John in sheet 2, Joe in sheet 3 and so on) if we add new sales of September to December in sheet 1 then how it (new sales) can auto update in concerned sales representative sheets. Please guide.
This link will guide:
www.exceltrainingvideos.com/transfer-data-from-user-form-to-multiple-worksheets-in-excel-workbook-using-vba/
Dear Sir,
As you said in the tutorial, I did even though it is not working, it shows yellow highlighted in Sub copycolumns(). what wrong I did in writing a code?
How about if i want to copy all column but only a 3 records, to different sheets? Say i have 4 people to divide records in sheet one between. How can i copy all columns, 3(records) rows each, and paste in different sheets for the people the have?
Thank you boss for this, but how do i format the code to copy column A in sheet one to column C in sheet 2 and something like that
www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/
awesome thanks very nice video ...
sir
thank you for your very good video sir i need your help about vba i have xcell file contains( 6 sheets, sheet 1 main sheet, sheet 2 name client, sheet 3 client etc…) sheet 1 is main sheet this sheet (1) contain 2 column first column for data for example months from jan to december and second column for price i want to transfer or copy price column to another sheets According to name of client Knowing that he will be copied or deport prices many times a column to the columns in the customers sheets
+Hnd Hnd These links might help:
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
hello sir, Like that video but now im having a problem.
Im working with a dairy farm company and im dealing with over 15 sales men. nom i have 8 differnt packages and in a dailly basis i receive their stock out and in reports. Now im thinking of one files as that one above but for my 15 sales men with their stock out and back report that will be automatically updating.
Hello Sir, i am facing a problem while using Macro Copy Data function,i might not able to fully convey my problem but i will try,i am currently managing payroll and i want my data to be segregated automatically from main sheet to my online salary(Bank Account)to pick only cells and their corresponding values if bank account number is entered and same for Cash Salary(Don't have bank account).i would like to share my sheet so that i could get better understanding if it is possible.
sir i have done all coding for this ,but only last single row copy and paste to next sheet
Sir, I have used this video to copy data from two worksheets to a third worksheet, all located in same workbook. It works fine and I am getting the desired results. only thing is it takes 8 to 10 seconds to generate the data even though the data in first two worksheets is not more than 32 rows each. The screen flickers like it is searching and come up with the results after 8 to 10 seconds. Is this regular or something wrong in my VBA codes
You can use the following code:
Application.ScreenUpdating=False
Later set it to true:
Application.ScreenUpdating=True
Thanks a lot sir.....
Dear Sir,
What if I have a drop down on sheet 1 where in I can select the state. and based on the selection when i press the copy data button, only the data for that state is moved.
In your case, you have coded Maharashtra in your VB code... but instead if I want to rather reference that value to a cell on sheet1... how do we do that sir?
Please advice.
Thanks,
Hello Sir, all your videos are really helpful. I just have a question around this one, i am trying to copy data in "b2" to another sheet in two different columns based on the condition applied to check boxes. 7 and 8 are the linked cells with two respective columns of checkboxes,, i am trying the below code. but its not working. please suggest
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, 7) = "True" And Sheet1.Cells(i, 8) = "True" Then
Sheet1.Cells(i, 2).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 1)
Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 2)
Sheet1.Cells(i, 3).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets("Sheet2").Cells(erow, 3)
Next
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range("A1").Select
End Sub
Have a look at this link: www.exceltrainingvideos.com/tag/copy-paste-non-contiguous-cells-excel/
Hi Sir,
Thank you for the tutorial... I have created the VBA code based on your instructions...
But... when I run the code only the last row is being displayed in the sheet2
Below is the code I have written
Sub copycolumns()
Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 40).End(xlUp).Offset(5, 0).Row
Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 2)
Sheet1.Cells(i, 2).Copy
Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 3)
Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 4)
Sheet1.Cells(i, 4).Copy
Sheet1.Paste Destination:=Worksheets("sheet2").Cells(erow, 5)
Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range("A1").Select
End Sub
Could you please help me with this.
Thanks..
+Pooja Atluri Interesting. Could you mail your sample worksheet to takyardinesh@gmail.com?
Hello! Could you please advice how to paste data to arbitrary line? For example I need to start paste from "B4". Many thanks in advance.
I did it. I add "If erow = 2 Then erow = erow + X" to the cycle, where x - number of lines you want to move down.
hello
i need this same but without button action. it should be done as we enter data in to cell automatically and individually. ??
plz help me
Hello
I need a Vba code to copy cells written in red color from a sheet called Template in column E to another sheet called "Data" in column A. I need the values to be added in column A after the last record
Hello Sir. Thanks for the video, but I need help, I don't know what I'm doing wrong but the micro is only copying the last row. Please help
Hello Dinesh Ji, I have a problem in hand that I'm unable to get help on despite browsing many sites :( I have a utility which captures data every 5-10 minutes. Now if the values in the cell match a pre-defined condition it changes color. What I want is the moment there is change in the data, it should save that to another excel sheet as history because I can't watch the utility every 5-10 minutes and note the changes. Eg - Cell A1 has a value of 100 and Cell B1 has the new capturing data. The moment B1 reflects 100, my conditional formatting kicks in and B1 turns Green. But next moment when it anything other than 100 it flips back. So lets say I didnt watch the sheet for 30 min, I end up missing when the changes came. I want to be able to save those changes in another sheet. I tried with autosaving entire data every @ every 10 min interval. But this becomes messy. I want to save only if the conditional formatting matches else not. Is this possible ?
Do you think the same will work if the data is filtered in the sheet 1?
Just try it out and give feedback!
i am doing inventory for my bar"how to automate copy closing rows as opening in the next sheet which are in numbers..
Very good sir thank you
Most welcome
Thank you for sharing.
Hello! Can you help me on this? This is regarding stock trading. If a cell value is 'sell or buy' then it should automatically record the stock's (live current price). And after a while when 'sell or buy' disappears (cell value is empty) it records the changed current price into another cell beside previous record. For example suppose the price keeps changing over a period of time, lets say cell A1 value is Sell/Buy and the price (Cell B1) is 235, so it records 235 and put it in cell C1. After some time if the A1 is empty and B1 value is 200, then it should paste 200 in D1. That means we will have two price C1=235 and D1=200. It should be automated "No button to click" via macro. Is it possible?
Use a 'TIMER'. This link will help: www.exceltrainingvideos.com/how-to-create-timer-counter-using-excel-vba/
Or search www.exceltrainingvideos.com
Sir, I have a problem. I have generated a sheet1 where i have assign a formula in a specific column. I am using a lookup value in another sheet. as i change the value in sheet1 where i assign a formula the lookup value is also change. Sir, I want to know that which formula we use that as i change the value in sheet1 the lookup value will never
dear sir , how can i copy specific columns from one excel to some specific columns in another excel , means for example how to copy column b from one excel to column a of another excel automatically
Thanks ... Thanks
hello sir. good afternoon.! i am working as admin clerk in private company.how to automatic copying of rows from one sheet to another sheet using a condition.. i mean at the end of the row of sheet1,i am giving various sheet names. if the sheetsname satisfies sheet 2 file name,the entire row will copied to sheet. tell me sir. i am waiting for Reply
Thanks for the reply, am looking for Excel VBA training. Please share your contact if you provide training as well.
Not at present.
You are great
i tried the code and got error 1004, then it worked after added more explicit variables :
Sub copycolumns()
Dim lastrow As Long, erow As Long
Dim sheet1 As Worksheet
Set sheet1= Sheets("Sheet1")
Dim sheet2 As Worksheet
Set sheet2 = Sheets("Sheet2")
hello, i want to ask you about the automatically Update Inventory , if i have list including formula, how can i make a clear without deleting formula?
Thank you
Sub RemoveConstants()
Dim myConstants As Range
Set myConstants = Sheet1.Range("A1:B3").SpecialCells(xlCellTypeConstants)
On Error Resume Next
myConstants.ClearContents
End Sub
Hello Sir,
Thank you for sharing this video, need your assistance. I want to copy two states, apart from Maharashtra, Delhi as well from the same column. Help suggest me with the code sir.
Use 'AND'
Hello! Sir, this tutorial of yours was very useful. I’d like to know if there is a way, this works by us giving this specific city or string in a cell and it generates that specific data by clicking the button? Instead of changing the macro every time.
Thanks in advance.
@@Exceltrainingvideos Thank you very much for the reply sir! Sir can you suggest me any reference video on how use 'AND' function in this regard.
Hi Sir... Thank you such a nice video... However sir... I have a question.
Suppose I have a column and each cell of that particular column contains six digits number... now I want to copy the cell which is stated with number 4.... I have tried with "If Sheets("Sheet1").Cells(i, 4) = 4*" but its not working ... Could you please help me to resolve that... Thanks in advance
Sir... I found the solution with the code : If Sheets("Sheet1").Cells(i, 4) >= 400000 And Sheets("Sheet1").Cells(i, 4)
Great!
Also have a look at theses links:
www.exceltrainingvideos.com/bank-reconciliation-using-do-while-loop/
www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Or do a search at exceltrainingvideos.com
Good and helpful video I am trying to find such video but Sir as you copy which have "Maharashtra" I want copy which contains only number value if coloum don't contains any number it's data should not copyied
You have shorted which have Maharashtra with code.If Sheet1.Cells(i, 6) = “Maharashtra” Then But my sheet have value I want copy such like which value 12589 because some row have no value, some numbers value
You can use a numerical value to copy data.
Hello Sir!
Unfortunately I get the following error on the third line. It says: "Runtime error 424 'Object required'.
Line 3: lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
+Sebastian Hashimi same problem
Hey did you guys figure out the problem ?
same here man. problem not solved
Calm Ore Ion Aswan Peter Sawyer Hey guys just let us know if you figure out the solution. I will give a shot on the weekend :) , and if it works i will let you know !
i have the same error. Has anyone figured out the fix?
dear sir,
I have followed as shown in d above video. but once I save the excel sheet and next time when I open and I click on button it copies same data again in next sheet. can you inform me a method where macro will b applied only on unsaved data.
You'll have to first clear all the data in sheet2 or in the sheet where you transfer the data as explained in this link: www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/
Please be careful because you cannot undo a macro action (easily). Read carefully through the article and adjust the code according to your requirements.
Also you can do a search at www.exceltrainingvideos.com for more ideas and sample files.
How would you do this if you have Column A-T on sheet 1, but A-P on 2nd sheet. And you want the data to copy over but the columns are mismatched as in Column A on Sheet 1 is Column D on Sheet 2 etc. Everyday new data will be put on Sheet 1 and you want Sheet 2 to automatically populate specific columns (w/o any special conditions)?
Search www.exceltrainingvideos.com
Dinesh Kumar Takyar I'll take a look at this ty :)
Hi Sir,,
I have a set of data in column B and I have to copy the contents and paste as row in another sheet. Can you please help me on this how to perform by Marco
These links will help solve your problem:
www.exceltrainingvideos.com/methods-to-transfer-data-from-excel-worksheet-with-vba/
www.exceltrainingvideos.com/transfer-transpose-data-from-one-excel-worksheet-to-another-using-vba/
www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
www.exceltrainingvideos.com/transpose-data/
I have a problem to solve that looks a lot like this but a bit different. I request your help. I get data from a source on one sheet (i'll call the data sheet) and need to copy that data into another sheet(I'll call the target sheet). Column names may be the same or slightly different on the two sheets and column names appear in different columns on the two sheets. Data must be moved into the proper columns so I can submit it for processing. I want to take 12 columns from one spreadsheet (I) and move them to 12 columns on another sheet, . I want to copy all of the rows from the data sheet to the target, but not exactly the same way. On the data sheet, the husband and wife each have their own unique row, appearing on two rows. One the target sheet the husband and spouse need to be on the same row, sharing address, phone number etc. so if the last name occurs twice on the data sheet, then the 2nd appearance of that last name should copy only first name and the birth date of second occurrence on data sheet to spouse name and spouse birthdate on the target sheet. If there is no duplicate last name, then a new row containing the 12 columns from the data sheet should should copy to the 12 columns to the target under the appropriate header. The target has predefined headers, that may be different than the predefined headers in the data sheet. Can you help me figure this out? Thank you.
we have a query can i change column data to another column data after using autofilter in vba
Just try it out and share your experience with the RUclips community. This link will also help: www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-vba/
Hello Sir, I have quite a complex problem statement that I need to resolve using VBA and I need your help. The task is to copy multiple ranges from a sheet in an excel workbook and paste it into several different ranges on a sheet in one excel master workbook. This copying process has to be repeated for 6 other workbooks and paste the data into the master workbook.The structure of all the source workbooks is the same.The problem is that in the master workbook, there are rows in between the paste ranges which consist of Formulae and are not a part of the copy-paste process. Is there any way I could send you the code that I have used for one single workbook so that you get an idea? Thank you.
Search www.exceltrainingvideos.com/
Hi sir i placed column number as 153 then i am getting run time error as subscript out of range please give me the solution as soon as possible
What is the use of last line code range("A1"), please clarify
No use. You can now view your data better.
Thanks...........................
Sir, Care to change code in case sheet 2 is named differently like Madagascar or something like that, how then can we change the code ?
ruclips.net/video/I-tX5D34nps/видео.html
I have data on a sheet 1, single row 19 columns. I need to auto generate to sheet 2 without having to copy them manually. I have MS excel 2016
www.exceltrainingvideos.com/methods-to-transfer-data-from-excel-worksheet-with-vba/
Or do a search at www.exceltrainingvideos.com