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.
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/
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.
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
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!
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.
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?
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?
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?
+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/
+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.
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.
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.
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.
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 TH-cam. What I would like to do is copy multiple names from column 1 (Like Ethan, Ali and Alexis) onto another sheet. Can you 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 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?
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/
You can use the Worksheet_BeforeDoubleClick event: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'code goes here End Sub
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.
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.
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
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..
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?
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.
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
@@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
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.
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!
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!
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 ?
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.
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?
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
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, 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.
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.
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 TH-cam channel or website,
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
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?
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.
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
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,
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 )
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
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.
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
Just try it out and share your experience with the TH-cam community. This link will also help: www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-vba/
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
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
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/
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.
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! 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?
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
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 !
Hi Dinesh Can you please help !! i have a lot of data in a sheet that needs to produce multiple lines of same data (e.g 2) original data comes like (e.g 1) and i need it to look like e.g 2 - without copy and paste - an automated function ? e.g 1 SKU Description Qty A100 Soap 2 A101 towel 3 and so on... how can excel see the qty and reproduce the line however amount the qty shows e,g 2 SKU Description A100 Soap A100 Soap A101 towel A101 towel A101 towel appreciate any help you can assist with Tom
These links will guide: www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/ th-cam.com/video/-fFTW4If-Jw/w-d-xo.html
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
Is there a way to copy and paste matching column headers from two workbooks with multiple tabs and consolidate them to one single workbook? It looks like your code just does this for one sheet. I need to copy and paste matching columns from the source tabs to one single destination workbook. I would need the loop to detect where the last row was pasted to the destination tab and resume pasting data.
So let me rephrase. I have 2 source workbooks with multiple sheets and 1 destination sheet. The destination sheet has a bunch of columns with formulas. Row 1 contains the headers for all 3 workbooks. The sheets within the source files have a different combination of columns. The destination workbook has one sheet that I am looking to consolidate all of the date on. I need a macro to take all of the data from the matching columns between the source and destination books. It would need to be a loop and need to use xldown to detect where previous data left off and keep pasting as the macro looks through the other worksheets. Not sure if this is even possible... I must say it is super messy and I am inheriting the problem. So the headers would already be in the destination sheet in row 1.
Even if it were semi automated macro process where I run the macro for each active sheet with the source workbooks and it pastes starting in the next blank row of the destination sheet in a separate workbook would be a win for me. I would then just keep changing tabs of the source spreadsheets. Right now I manually copying atleast 20 different columns from the source files and then pasting them for each sheet from the source.
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.
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.
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)?
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
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
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.
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.
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 ?
Hello Dinesh I really could use your help creating a Macro or VBA. If there is anyway that i can send my request on what I need done to you. your response is really appreciated. thanks
@@Exceltrainingvideos Thanks for the quick reply really appreciate it. I have a Master Sheet that has names in column A and each name has its own Sheet and data ex: A1 BOB A2 JOHN A3 PETER A4 SMITH A5 LIZ A6 TOM So I have sheet named "1" in cell B1= BOB, Sheet named "2" B1= JOHN, Sheet named "3" B1= PETER Sheet named "4" B1= SMITH etc... with B1 of each sheet link to the rows in Master sheet so if JOHN is moved from A2 to A4 in master sheet than the B1 in sheet named "4" has JOHN. what I need is a macro that will move JOHN data in column A in Sheet named "2" to column A in sheet named "4". In other words I need the data from each sheet to move with the Name. The data are not stored in Master sheet its only in each individual sheet. Usually a name will be moved down when new names are added therefore. A1 BOB A2 BETH A3 KIM A4 JOHN A5 PETER A6 SMITH A7 LIZ A8 TOM Or the other scenario can be that a name is removed and that the names below will be moved back up. so the data with each header needs to move when the Names move to different sheets.
@@Exceltrainingvideos Thanks for your reply i really appreciate it. I have a Master Sheet that has Names in column A and each Name has its own Sheet and Data ex: A1 BOB A2 JOHN A3 PETER A4 SMITH A5 LIZ A6 TOM I have A sheet named "1" in B1= BOB, Sheet named "2" B1= JOHN, Sheet named "3" B1= PETER Sheet named "4" B1= SMITH etc... with B1 of each sheet link to the rows in Master sheet so if JOHN is moved from A2 to A4 in master sheet than the B1 in sheet named "4" has JOHN. what I need is a macro that will move JOHN data in column A in Sheet named "2" to column A in sheet named "4". In other words I need the data from each sheet to move with the Name. Usually a name will be moved down when new names are added therefore A1 BOB A2 BETH A3 KIM A4 JOHN A5 PETER A6 SMITH A7 LIZ A8 TOM Or the other scenario can be that a name is removed and that the names below will be moved back up. so the data with each header needs to move when the Names move to different sheets.
@@Exceltrainingvideos Im sorry if this comes through multiple times as it appears to disappears after i refresh my browser I have a Master Sheet that has Names in column A and each Name has its own Sheet and Data ex: A1 BOB A2 JOHN A3 PETER A4 SMITH A5 LIZ A6 TOM I have A sheet named "1" in B1= BOB, Sheet named "2" B1= JOHN, Sheet named "3" B1= PETER Sheet named "4" B1= SMITH etc... with B1 of each sheet link to the rows in Master sheet so if JOHN is moved from A2 to A4 in master sheet than the B1 in sheet named "4" has JOHN. what I need is a macro that will move JOHN data in column A in Sheet named "2" to column A in sheet named "4". In other words I need the data from each sheet to move with the Name. Usually a name will be moved down when new names are added therefore A1 BOB A2 BETH A3 KIM A4 JOHN A5 PETER A6 SMITH A7 LIZ A8 TOM Or the other scenario can be that a name is removed and that the names below will be moved back up. so the data with each header needs to move when the Names move to different sheets.
@@Exceltrainingvideos My reply disappear after i refresh my browser. not sure if there is a character limit on these reply or not. please let me know if my inquiry came through
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.
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!!
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.
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
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.
Hi, what if for example I have 3 tabs for each excel files and I only want one tab to be copied to the master file? How to do it? By the way I am using excel 2016 version. Thanks in advance!
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/ Or search this channel and this website www.exceltrainingvideos.com
This link will help: www.exceltrainingvideos.com/tag/methods-to-transfer-data-from-excel-worksheet-with-vba/ Alternatively do a search at www.exceltrainingvideos.com You may also like to invest in a good Excel book: Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
Hi Sir, i have a data sheet with 33000 row items. i have written a VBA code to insert a new sheet as Output Sheet. now i want to copy the records with 4 conditions by useing the Autofilter option. i am able to put the autofilter but not able to put all 4 conditions in one go. conditions are like 1. starting with DD 2. anything with the key word DD 3. Direct debit 4. anything with the key word direct debit. please help me with the VBA code.
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, 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
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.
Thank you sir. This code solved my big problem.
Incredibly thorough and easy to follow. Thanks so much!!!
Thank you!
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
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!
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.
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?
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/
Awesome!! much appreciated - you saved me a lot of time with this
Glad it helped! Please share with your friends too.
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/
Awesome! Thank you for this. Very helpful and well presented.
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.
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.
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.
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.
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 TH-cam. 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")
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 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?
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/
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
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.
Sir very useful videos .....can you share some video for pivot creation
th-cam.com/play/PLFoKoDG_7gtJbKNbtOxqvPlTg2Dyvzqus.html
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
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?
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
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
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.
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.
Thank you for this and your all the videos.
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, I am getting the same error Run time error 424. object required
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!
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 ?
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.
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?
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
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
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,
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.
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 TH-cam channel or website,
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
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?
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.
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
Do you think the same will work if the data is filtered in the sheet 1?
Just try it out and give feedback!
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,
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!
Sir..my query is similar to this video..its just that the data should be copied to other worksheet skipping one row..pls help
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
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.
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
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 TH-cam community. This link will also help: www.exceltrainingvideos.com/copy-auto-filtered-data-to-another-worksheet-automatically-with-vba/
Hello Sir! my desire if one row copy to other sheet copy are possible
This link will help: th-cam.com/video/KNgf4aB8s_k/w-d-xo.html
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.
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/
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
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.....
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/
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 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
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! 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
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?
Hi Dinesh
Can you please help !!
i have a lot of data in a sheet that needs to produce multiple lines of same data (e.g 2)
original data comes like (e.g 1) and i need it to look like e.g 2 - without copy and paste - an automated function ?
e.g 1
SKU Description Qty
A100 Soap 2
A101 towel 3
and so on...
how can excel see the qty and reproduce the line however amount the qty shows
e,g 2
SKU Description
A100 Soap
A100 Soap
A101 towel
A101 towel
A101 towel
appreciate any help you can assist with
Tom
if I only want to copy columns which I marked to be copied ( # of columns can vary) instead of a fix range. what do I do? thanks!
btw. your videos are awesome! I have learned so much
Can't we use:
PasteSpecial Paste:=xlPasteValues, Transpose:=True
so we can paste an entire range that we want...
Wait… Holly molly this was from 2015
These links will guide:
www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
th-cam.com/video/-fFTW4If-Jw/w-d-xo.html
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
Is there a way to copy and paste matching column headers from two workbooks with multiple tabs and consolidate them to one single workbook? It looks like your code just does this for one sheet. I need to copy and paste matching columns from the source tabs to one single destination workbook. I would need the loop to detect where the last row was pasted to the destination tab and resume pasting data.
Question not quite clear. Do you wish to copy the headers only or data in columns?
So let me rephrase. I have 2 source workbooks with multiple sheets and 1 destination sheet. The destination sheet has a bunch of columns with formulas. Row 1 contains the headers for all 3 workbooks. The sheets within the source files have a different combination of columns. The destination workbook has one sheet that I am looking to consolidate all of the date on. I need a macro to take all of the data from the matching columns between the source and destination books. It would need to be a loop and need to use xldown to detect where previous data left off and keep pasting as the macro looks through the other worksheets. Not sure if this is even possible... I must say it is super messy and I am inheriting the problem. So the headers would already be in the destination sheet in row 1.
Even if it were semi automated macro process where I run the macro for each active sheet with the source workbooks and it pastes starting in the next blank row of the destination sheet in a separate workbook would be a win for me. I would then just keep changing tabs of the source spreadsheets. Right now I manually copying atleast 20 different columns from the source files and then pasting them for each sheet from the source.
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/
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 th-cam.com/video/TZptQpmW2Xs/w-d-xo.html
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... 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
How to use PasteSpecial on your code? I keep get in error when I used PasteSpecial. Thanks
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,
How can i use if condition where i need to filter data with multiple data set like as per ur example State Maharashtra and New Delhi
If Sheet1.Cells(i, 6) = “Maharashtra” & Sheet1.Cells(i,7) ="New Delhi" Then
do something
End IF
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.
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 ?
Hello Dinesh I really could use your help creating a Macro or VBA. If there is anyway that i can send my request on what I need done to you. your response is really appreciated. thanks
You can post your query right here in the comments.
@@Exceltrainingvideos
Thanks for the quick reply really appreciate it. I have a Master Sheet that has names in column A and each name has its own Sheet and data ex:
A1 BOB
A2 JOHN
A3 PETER
A4 SMITH
A5 LIZ
A6 TOM
So I have sheet named "1" in cell B1= BOB, Sheet named "2" B1= JOHN, Sheet named "3" B1= PETER
Sheet named "4" B1= SMITH etc... with B1 of each sheet link to the rows in Master sheet so if JOHN is moved from A2 to A4 in master sheet than the B1 in sheet named "4" has JOHN. what I need is a macro that will move JOHN data in column A in Sheet named "2" to column A in sheet named "4". In other words I need the data from each sheet to move with the Name. The data are not stored in Master sheet its only in each individual sheet. Usually a name will be moved down when new names are added therefore.
A1 BOB
A2 BETH
A3 KIM
A4 JOHN
A5 PETER
A6 SMITH
A7 LIZ
A8 TOM
Or the other scenario can be that a name is removed and that the names below will be moved back up. so the data with each header needs to move when the Names move to different sheets.
@@Exceltrainingvideos
Thanks for your reply i really appreciate it. I have a Master Sheet that has Names in column A and each Name has its own Sheet and Data ex:
A1 BOB
A2 JOHN
A3 PETER
A4 SMITH
A5 LIZ
A6 TOM
I have A sheet named "1" in B1= BOB, Sheet named "2" B1= JOHN, Sheet named "3" B1= PETER
Sheet named "4" B1= SMITH etc... with B1 of each sheet link to the rows in Master sheet so if JOHN is moved from A2 to A4 in master sheet than the B1 in sheet named "4" has JOHN. what I need is a macro that will move JOHN data in column A in Sheet named "2" to column A in sheet named "4". In other words I need the data from each sheet to move with the Name. Usually a name will be moved down when new names are added therefore
A1 BOB
A2 BETH
A3 KIM
A4 JOHN
A5 PETER
A6 SMITH
A7 LIZ
A8 TOM
Or the other scenario can be that a name is removed and that the names below will be moved back up. so the data with each header needs to move when the Names move to different sheets.
@@Exceltrainingvideos Im sorry if this comes through multiple times as it appears to disappears after i refresh my browser I have a Master Sheet that has Names in column A and each Name has its own Sheet and Data ex:
A1 BOB
A2 JOHN
A3 PETER
A4 SMITH
A5 LIZ
A6 TOM
I have A sheet named "1" in B1= BOB, Sheet named "2" B1= JOHN, Sheet named "3" B1= PETER
Sheet named "4" B1= SMITH etc... with B1 of each sheet link to the rows in Master sheet so if JOHN is moved from A2 to A4 in master sheet than the B1 in sheet named "4" has JOHN. what I need is a macro that will move JOHN data in column A in Sheet named "2" to column A in sheet named "4". In other words I need the data from each sheet to move with the Name. Usually a name will be moved down when new names are added therefore
A1 BOB
A2 BETH
A3 KIM
A4 JOHN
A5 PETER
A6 SMITH
A7 LIZ
A8 TOM
Or the other scenario can be that a name is removed and that the names below will be moved back up. so the data with each header needs to move when the Names move to different sheets.
@@Exceltrainingvideos My reply disappear after i refresh my browser. not sure if there is a character limit on these reply or not. please let me know if my inquiry came through
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
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.
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/
Thanks for the reply, am looking for Excel VBA training. Please share your contact if you provide training as well.
Not at present.
i am doing inventory for my bar"how to automate copy closing rows as opening in the next sheet which are in numbers..
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.
Can you tell me how to edit this code if the Columns are in another workbook?
Get the path to the column.
Hi, what if for example I have 3 tabs for each excel files and I only want one tab to be copied to the master file? How to do it? By the way I am using excel 2016 version. Thanks in advance!
www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
Or search this channel and this website www.exceltrainingvideos.com
Thank you so muchhh
hi sir i have doubt where did we specified to copy maharastra state only????????
The link to this VBA tutorial will help: www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/
Is there a way for me to Paste the data as a value on the other sheet?
This link will help: www.exceltrainingvideos.com/tag/methods-to-transfer-data-from-excel-worksheet-with-vba/
Alternatively do a search at www.exceltrainingvideos.com
You may also like to invest in a good Excel book: Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
Hi Sir,
i have a data sheet with 33000 row items. i have written a VBA code to insert a new sheet as Output Sheet. now i want to copy the records with 4 conditions by useing the Autofilter option. i am able to put the autofilter but not able to put all 4 conditions in one go. conditions are like
1. starting with DD
2. anything with the key word DD
3. Direct debit
4. anything with the key word direct debit.
please help me with the VBA code.
+debasis mahapatro This video might help: th-cam.com/video/TZptQpmW2Xs/w-d-xo.html
What is the use of last line code range("A1"), please clarify
No use. You can now view your data better.
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
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, 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
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