Thank you for sharing this video. I used the thinking to copy data from two different sheets to a third for a running record of transactions. I know it has been 7 years, but still works for me.
Thank you so much for your brilliant knowledge. I am working on a worksheet comparable to this same problem and honestly didn't think I would ever find an answer. You are the greatest! Thanks again!
Dinesh you make this look easy...absolute genius you are. I've seen other videos on VBA but the way you teach it makes more sense. I will definitely be watching more of your videos...keep up the good work!
great video on this topic. Question, what would the code be, if you wanted to use a dropdown box for the customer name to update a particular sheet tied to the customer-specific?
This link will guide: www.exceltrainingvideos.com/tag/how-to-ensure-entry-of-data-only-from-combobox-drop-down-list/ You can also search this channel or my website www.exceltrainingvideos.com
Hi Tomichan joseph just few days back i have done the same thing for my organization.one of the easiest way to do this is by using VLOOKUP function. Add a serial number in marksheet format and a serial number in data for the mark list. the VLOOKUP function will pick up the row value corresponding to that serial number in sheet 2. Also add a SPIN BUTTON from developer tab in sheet 1. set the incremental value one and print property as false. On clicking the spin button the serial number on the sheet 1 will increase by one and VlOOKUP will automatically pick up its corrosponding values from sheet 2. Hope this will help you
Hello Mr. Dinesh Thanks for all the time you spending to teach VBA (awesome) to people like me.I am a basic learner, your videos was very helpful for me to learn. I do have similar problems on excel in different level needed help.
Dear Sir thank you for sharing your knowledge. Your ideas have made my works very simple. What I thought about writing a Macro in excel has been made easy by you. But I would like to say that I have used it for the 1st time it worked well. Then I added the ClearContents. But It does not go to a next line in sheet2 & replaces the data in 2nd row. So please kindly comment in this regard. My code is written below: Private Sub CommandButton1_Click() Dim CustomerName As String, CustomerNumber As Double Worksheets("Sheet1").Select CustomerName = Range("B1") CustomerNumber = Range("B2") Worksheets("sheet2").Select Worksheets("Sheet2").Range("A1").Select If Worksheets("Sheet2").Range("A1").Offset(1, 0) "" Then Worksheets("sheet2").Range("A1").End(xlDown).Select End If ActiveCell.Offset(1, 0).Select ActiveCell.Value = CustomerName ActiveCell.Offset(0, 1).Select ActiveCell.Value = CustomerNumber Worksheets("sheet1").Select Range("B1:B2").ClearContents Worksheets("Sheet1").Range("B1").Select End Sub
Hello Mr Dinesh, your tip is really good to apply. I am also to apply this formula in my worksheets and it works fine. However, i am facing one issue rather than customer problem in number I have employee scores which should be in percentage. Please let me know the code for the same. Thank you so much.
Really like the way, Having an issue once I create my own file for macros, If you can help. Please let me know how I can send you create data file with VB program for Macro, so that you can check and let me know the mistake why its not working on my end. If you can help I will appreciate. Thanks
Thank you for your reply. I managed to test it already right after i wrote that reply. Usually i manage to find the answers when i think about them in clear-mind.
Mr. Takyar I love your videos. I am currently trying to put together an expense database that would use User From to input the data and update itself every time I close the application. any thoughts??The fields i want to use are Date, Store or Services, Description, Category, and Amount.
JOSE AVILA Please have a careful look at all the 4 videos starting here:@4CFoPl99FpgFor more details you can view this link:@tag/database-management-using-excel-userform-with-vba/
Hello Dinesh Kumar Takyar. Your videos helped me a lot. I copied your code here from your link and tested it. It works fine, but i want to have two text cells, instead of one with text and a number. What i have to change in the code?
Sir, this works very well for a single cell to copy. However I have a range of cells which i want to copy (e.g. from J9 to J22) then what should be the changes made in the code?
This guide was fantastic! Thank you! Is there any way to have it delete the information on the first sheet after it copies it over to the second sheet?
Dinesh. Your code works great. I am trying to update not only one sheets but two with the same data.. How would I use this code to update two sheets with the same information?
I find this very informative. How ever I am trying to solve this delima. I have a workbook with multiple worksheets. Each sheet is setup to display information for each individual. I would like each individual to see/read only their sheet. Example sheet 1 Mary. Sheet 2 Ann sheet 3 Betty Can I send the workbook to all of the, but how can I make so they can only see their sheet?
Sirji you post really informative videos. 1 que. Sir Hi, I have my Excel file which is updated through an odbc connection. But how can I sync that Excel data into Google Sheets that could be updated automatically in periodic intervals but without using 3rd party connectors. Any Solution? Thanks
Hi Dinesh, your videos are very helpfull and have excellent knowledge in Excel, could you please load some more videos for VBA please for beginners like how to code in VBA please.
This was exactly what I have been looking for :) If I wanted to delete the data previously entered once returning to cell C4 on sheet1 is there another step I can enter?
Hello, thank you for your video and explanation! How it works if I have hundrends of worksheets? Then how to link certain cells from each sheet to cells in a master sheet? So far I didn't find a way to do tad, so I hope that you could help me with this! Thank you in advance!
Own 4 rental properties. what I like to see is how each property is functioning/doing on a single sheet. I have for each property taxes, insurance, maintenance, yearly total rental, management fees. I like to see these on a single sheet with 4 different named properties. In addition, like to see the total loss and profit for all the 4 rentals. I also like to see bar graph next to each property for comparison. Thanks
Very nicely done with every detail. Thank you for sharing the knowledge with us sir. I have just got a job in a hotel construction site as supervisor on Fire sprinkler system . I need to make daily , weekly & monthly report with inventory, of pipes, sockets,tees, sprinkler head etc, + on progress report how many meters of different pipe on level and blocked have been made. Sir is there any way you help me. Thanking you , I appreciate your help! Truly Neal
This was very helpful. It would also be helpful to use this on a master sheet, which then updates subsequent sheets depending on which sheet it belongs to. I want to use this to create a workbook that assigned responsibilities to different people who have a sheet assigned to them. When I put the data in the master list, I want it to populate the data in the correct person's sheet so that when they click their sheet, they only see their data, and not the other person's. Do you have a way to do that?
This link will help: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/ You can also search my website www.exceltrainingvideos.com for many more solutions related to your query.
Hi Dinesh great video as i am a newbie to excel. I have 2013, it all works well bar when i click the command button the info copies over the last entry. could you please advise
Hello Dinesh, Thank you for your videos, they are very helpful. I have a monthly report that I have to send out. There are 3 sheets of data and then a summary page. How could I make a template, where copy and paste the new files, and update the summary page. I use formulas now, but it is very tedious update each cell. Thank you!
Great job Dinesh. I was able to utilize your code and it works great! Now, I also would like to create a Command Button on Sheet2 that allows me to move all the data in one row back to the the sheet1 form. The decision of which row to move back should be determined by me manually selecting the first cell on the row (lets say I would highlight the name and then press the button). Could you help me with this? Thanks in advance!
Dinesh Kumar Takyar Thank you for the info. I was also wondering if you know why the macros enabled file is messing my computer's date. When the file is open my computer's date is changing to a past date and I don't know why I'm having this issue? Could you help?
hi sir Dinesh, thanks for this very useful tips. i have further question regarding transfer of data from one worksheet to another worksheet using a particular template. i've been using one of your formula in recording all issued check and it really helps, but the formula doesn't work well to record our check vouchers transaction... 1. in a voucher i would need to save the: voucher no. voucher date payee particulars amount account name and its amount the question is, what if the voucher i prepare contains multiple account name and the template (check disbursement book) where im going to save the datas was set like having a sandries column. this is to avoid longer horizontal recording.
template to save my data have 21 columns for accounts names includes voucher no, date, payee,particulars, ref. no., and amount of voucher. all accounts name that was not listed in 21 columns should be recorded in the 22th column which was the sandries. hope you help me sir. million thanks in advance.
Excellent video, you explained it so even a novice an code in VBA. I have a problem I am trying to solve. I have about 700 excel files that I want to collate the information from into one excel document. Could you post a video showing how to write a VBA code that would sequentially go through an series of consecutively named files and import their data into a master document? Thank you.
Great posting. If you don't mind though, what would the code be if there is more than 2 cells of data that needs to be transferred over to another worksheet? I think where I am running into the problem is at the ActiveCell.Offset portion. If there are 3 cells of data to transfer, would you use ActiveCell.Offset(0,0,1).select? I am guessing not because it doesn't work properly when entering this. Any help would be greatly appreciated!! Thanks again!
Very detailed video and more of teaching how to do than just show how itz done. :) I was looking for this but I have more than 1 cell to capture at the same time. ex in this scenario would be.. capturing 10 customers name at once and putting them int he other sheet. Trying to figure out how to do that. But thanks for this vid. Appreciate it.
You can capture as much data as is in your worksheet and transfer it to another worksheet to a relevant address. You just need to define the range! Also, please do a search on my channel to find the topic or solution of your interest. You may also like to visit my website: www.exceltrainingvideos.com Thanks.
Dinesh Kumar Takyar Thanks for the quick response. I'm tried changing the Range ("C4") to lets say Range ("C4:C14") but somehow it doesn't capture all. Lame way would be to copy paste the same forumula multiple times and just change the cell name. One more thing I need to do is make that Update click button automatic. Like it should click automatically every 2 mins instead of me clicking Update. Thanks for the website link. Very interesting and browsing through right now. :)
Hi Dinesh, I want to use the basic principle you are using here. The difference is I have invoice number, address, then I have the break down of the job which includes all the costing. When the job is complete I want to send it to another sheet (Lets call it All Jobs) that keeps records of all jobs and removes from active sheet to add a new job in its place. The active sheet is only to have "Current jobs" in progress, so as I hit the "Update" button those jobs completed can be stored to All Jobs. The sheet i'm referring to has a lot of formulas in it to calculate tax, cost etc etc. So I'm hoping I can highlight a row then hit update to send it on. The other question I have, will the "All Jobs" sheet automatically file the date in a available row, as I will be using the same row over and over again in the "Current jobs". Hope this makes sense ...Thanks Again Heath
thanks for your video review, I did have a question, say I waned to transer a "date" from a DATA SHEET, to automatically post to a UPDATE SHEET, how do we write a date?
Hi Dr, i appreciate the hard work you put in for this video. I under stood the whole programming but i want to do it using Range reference. Could you please help me on this Thanks in advance.... Regards, Essa khan
Thank you sir for sharing this tutorial, i wonder how to transfer data between two workbook, i mean from sheet in workbook A to sheet in workbook B, could it be possible ? thanks in advance sir.
risdo wilson Nainggolan www.exceltrainingvideos.com/how-to-transfer-data-from-one-excel-workbook-to-another-automatically/www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Hi Dinesh Sir, Please help me out in this issue. I work in an MNC. I need to take count of every individual emp by EOD or next day morning. I need a Macro to simplify my work. Basically I want a macro to be developed for the following situation, Every Individual emp will have their own excel files with their individual names at one folder with the folder name: DAILY COUNT They will save their data in that sheet in the following manner In A1 column DATE heading In A2 Emp will press ctlr+: to update date (i.e. Current Date) In B2 Emp will paste all the Ticket# processed on that day (our targets range from 0 to 120 per day), as follows, Ticket#1 Ticket#2 . . . Ticket#120 Then emp will save it and close. • Now I want to have a Master file in folder (DAILY PRODUCTION) • Master file name DAILY • In that Master file I need a Macro(button) after running a macro o A new excel file should be opened o All the data of every individual emp should be pasted in it with the individual subtotal(grouped individually) and total at the end Total production = sum of all individual counts o After running macro data in a new opened excel file should look like follow, Name | Ticket# -- as Heading Emp1 | Ticker# Emp1 | Ticket# Emp1 | Ticket# + Emp1 Count | count of the above Ticket# Emp2 | Ticker# Emp2 | Ticket# Emp2 | Ticket# + Emp2 Count | count of the above Ticket# GrantCount | Sum of all individual counts *Above shown + symbol denotes all the count is displayed as grouped Now I manually will save this file with the name of that particular date. Please let me know if it is possible. Thank you so much in advance sir.
How would you pull entire columns though? For example enter all my data into columns A & B of 3 different excel sheets and have them updated into 1 master sheet? Can you please help. The video is good, and is a good start for the coding but I'm confused on how I would need to tweak the code to make it do what I want.
Hi Sir, Thanks for sharing your knowledge with us. I have saved multiple excel workboobs in one folder 📂 and I want to consolidate those excel files into one excel file, one sheet. Regards, Sandeep
These VBA solutions will guide: www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/ www.exceltrainingvideos.com/tag/get-data-from-multiple-worksheets-in-multiple-workbooks-into-master-workbook-with-vba/
Hi, great video tutorial, easy to follow. However, im stuck on a little issue. I want my worksheet to transpose more than 2 cells, say if i wanted to transpose 3-4 cells, how would it be possible to do? If you have a video could you link it in the comment or let me know how to do this. Thanks
hi dinesh sir This is amit,I work in bpo sector ,we have to show the work which we do daily in our productivity sheet and send it to the person who collate the data ,so I want a button in a productivity sheet that automatically copy the filled cell and past it in another work workbook name "mastersheet" which will be on the desktop,so that the person who collate the data will save a lot of time
Hello sir, this is amazing. Thank you very much for your video. It was very useful and very easy to u derstand. Could you please show us how to clear the cell automatically after we hit UPDATE button? I enter the data, then hit Update, but the old data is still there. Is it possible to have an empty cell after i hit update butto . Please let me know when you can. We are watching you all the ways from US. Thank you, Jack.
Thanks for the tutorial. What's the significance of adding in the word "customer" in the code? for example, in the beginning you wrote "Dim CustomerName". if we write "Dim Name" would that be enough? in the first worksheet there is no mention of the word "customer" so why do we need to add it in the code?
sir i also having a same problem to calcute employee history large amount of entries pls help me for example if once i create a entry on worksheet if its already have it must shown back example i put a staff number 123 past entry details must be shown and if i want to update i have to deside yes or no and i need with large amount of cells please help me sir thanks in advance and i thank u a lot now i know little about vb only because of you i promise honourly your a best teacher
This is a very good video and thank you for posting. Although most users have trouble understanding Excel language (VBA). Why not just show a video recording a macro, that would be a much easier way to understand VBA. A recorded macro showing step by step would be simpler and more useful for most users, that way they can do other things that they may need with excel. Just a thought.... :)
Had the same problem with only two entries and overwrite of the second on subsequent entries. you need to select the header row as your first entry point (not the blank field below). 8:12 mentions B5 but entry point as correct in screen code at 8:47 is B4. Changing thaty sorts it for me.
Sir, Very informative session I have a small issue i have copied this code and done but after second entry it is deleting the second entry and pasting the third entry on same column of second kindly advice
HELLO DINESH, I WATCHED YOUR TUTORIAL I REALLY ENJOYED IT. THE PROBLEM I HAVE IS THAT I AM WORKING ON GOOGLE ONLINE EXCEL AND FOR SOME REASON I CANNOT FIND THE DEVELOPER TAB SO I CAN CREATE AN UPDATE BUTTON. CAN YOU HELP ME FIGURE HOW I COULD FIND IT?
Hey Dinesh! I hope that you can help...I am currently working on an Excel workbook that allows for a user to input specific information pertaining to WIP product, click the command button to submit, and then data to compile in a secondary spreadsheet; however, I do not know how to get input data to the next cell of the secondary spreadsheet without clearing out the original entry. I would like to keep a running log of entries in one master location so that we may effectively gain insight into tracking our WIP. Here is the code:Private Sub cbSubmit_Click() Dim User As String, ProductScan As String, ScanFromLocation As String, ScanToLocation As String Worksheets("WIP_Finder").Select User = Range("B9") ProductScan = Range("C9") ScanFromLocation = Range("D9") ScanToLocation = Range("E9") Worksheets("WIP_History").Select Worksheets("WIP_History").Range("A2").Select If Worksheets("WIP_History").Range("A2").Offset(1, 0) "" Then Worksheets("WIP_History").Range("A2").End(xlDown).Select End If ActiveCell.Offset(0, 0).Select ActiveCell.Value = User ActiveCell.Offset(0, 1).Select ActiveCell.Value = ProductScan ActiveCell.Offset(0, 1).Select ActiveCell.Value = ScanFromLocation ActiveCell.Offset(0, 1).Select ActiveCell.Value = ScanToLocation Worksheets("WIP_Finder").Select Worksheets("WIP_Finder").Range("B9:E9").ClearContents Worksheets("WIP_Finder").Cells(Rows.Count, "A").End(xlDown).Offset(1, 0).PasteSpecial
hi sir, I work for a mnc where we have to calculate time taken to process each case so if we have any automated excel sheet where we can put the name and click on start time and once we complete click end time so that time taken to process is calculated.
I have a related question - Can I pull entire rows of data from one worksheet into another sheet based on information from one column? I need to feed all data for one department from my master spreadsheet into a separate sheet for just that department. Thank you!
Thank you. Very helpful. How do I make the data entered on sheet 1 disappear once the "update" button has been clicked. I don't want my users to have to erase over previously-entered data that has already been imported into the second sheet.
These links will help: www.exceltrainingvideos.com/tag/how-to-create-report-from-excel-data-sheet-with-vba/ www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/ Invest in this book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU Or visit www.exceltrainingvideos.com to learn more for free.
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 Or visit www.exceltrainingvideos.com to learn more for free.
Hello Sir, How are you?, sir your video is helpful for non-user of VBA, sir please help me in a matter is that, how to edit the data copied in sheet2 kindly confirm me how to do that?? Thanks in advance
Thank You for this video. It helped me with what I was trying to do. However one of my headers is DATE and it errors out on that saying complied error Expected: variable when entered DATE As String or as DATE As Date. It enters the first line perfectly but then when I enter my second vehicle number and hit update it goes to my code sheet and highlights the Worksheets("MonthEndReport").Range("B3").End(x1Down).Select. it doesn't want to let it go down to the next line. Any help?? Is there another video I can watch that might help me with this? They are very informative.
Worksheets("MonthEndReport").Range("B3").End(x1Down).Select ---> Worksheets("MonthEndReport").Range("B3").End(xLDown).Select ---> l or L for London DATE is a reserved keyword.
Hi computer club,,, Your videos are really helpful,,, i have a requirement..im working fully excel based job..i need to create a multiple sheet on single click which contans different names
Good morning sir. Sir i want to filter data from closed excel sheet with 3 criteria in dropdown list based on date range selection. So please make any vedio on this
Thank you for sharing this video. I used the thinking to copy data from two different sheets to a third for a running record of transactions.
I know it has been 7 years, but still works for me.
Glad that the video was useful.
Thank you so much for your brilliant knowledge. I am working on a worksheet comparable to this same problem and honestly didn't think I would ever find an answer. You are the greatest! Thanks again!
Please have a look at the videos on how to use a list-box on a user-form. Hope that helps.
Thank you for the upload! It is easy to follow and understand. I also work at a call center trying to improve our data collection system. Thank you!
I have seen many excel master you are also brilliant as well
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Dinesh you make this look easy...absolute genius you are. I've seen other videos on VBA but the way you teach it makes more sense. I will definitely be watching more of your videos...keep up the good work!
Thank you!
great video on this topic. Question, what would the code be, if you wanted to use a dropdown box for the customer name to update a particular sheet tied to the customer-specific?
This link will guide: www.exceltrainingvideos.com/tag/how-to-ensure-entry-of-data-only-from-combobox-drop-down-list/
You can also search this channel or my website www.exceltrainingvideos.com
Sir, ur VBA teaching is very good.
Thank u 4 sharing.
Hi Tomichan joseph
just few days back i have done the same thing for my organization.one of the easiest way to do this is by using VLOOKUP function. Add a serial number in marksheet format and a serial number in data for the mark list. the VLOOKUP function will pick up the row value corresponding to that serial number in sheet 2. Also add a SPIN BUTTON from developer tab in sheet 1. set the incremental value one and print property as false. On clicking the spin button the serial number on the sheet 1 will increase by one and VlOOKUP will automatically pick up its corrosponding values from sheet 2.
Hope this will help you
Sir ur videos are most important in my professional life thank for ur time 🙏🙏🙏🙏
Hello Mr. Dinesh Thanks for all the time you spending to teach VBA (awesome) to people like me.I am a basic learner, your videos was very helpful for me to learn. I do have similar problems on excel in different level needed help.
Did you place the headers in sheet2? Did you write xONEDown or XLONDONDown?
x1 or xl?
Dear Sir thank you for sharing your knowledge. Your ideas have made my works very simple. What I thought about writing a Macro in excel has been made easy by you.
But I would like to say that I have used it for the 1st time it worked well.
Then I added the ClearContents.
But It does not go to a next line in sheet2 & replaces the data in 2nd row.
So please kindly comment in this regard.
My code is written below:
Private Sub CommandButton1_Click()
Dim CustomerName As String, CustomerNumber As Double
Worksheets("Sheet1").Select
CustomerName = Range("B1")
CustomerNumber = Range("B2")
Worksheets("sheet2").Select
Worksheets("Sheet2").Range("A1").Select
If Worksheets("Sheet2").Range("A1").Offset(1, 0) "" Then
Worksheets("sheet2").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = CustomerName
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CustomerNumber
Worksheets("sheet1").Select
Range("B1:B2").ClearContents
Worksheets("Sheet1").Range("B1").Select
End Sub
Hello Mr Dinesh, your tip is really good to apply. I am also to apply this formula in my worksheets and it works fine. However, i am facing one issue rather than customer problem in number I have employee scores which should be in percentage. Please let me know the code for the same. Thank you so much.
Very nice ❤
The code works very well. How to extend this code to multiple entries? i.e., data in multiple cells to be transferred with one click of a button.
Really like the way, Having an issue once I create my own file for macros, If you can help. Please let me know how I can send you create data file with VB program for Macro, so that you can check and let me know the mistake why its not working on my end. If you can help I will appreciate. Thanks
Thank you. Your explanations are easy to follow.
Thank you for your reply. I managed to test it already right after i wrote that reply.
Usually i manage to find the answers when i think about them in clear-mind.
In sheet2 did you add the headers Name and Customer Problem?
Wow! I found it. This is the thing I was searching for. I think my problem will be solved. Thanks a lot.
You're welcome!
am really see this as great opportunity to acquire more on excel accounting package.God bless all d team o this site......keep it up
+adeshola oluseun Thank you!
I did place the headers, and it is X1Down (Thanks for the follow up.... really appreciate!!!)
Mr. Takyar I love your videos. I am currently trying to put together an expense database that would use User From to input the data and update itself every time I close the application. any thoughts??The fields i want to use are Date, Store or Services, Description, Category, and Amount.
JOSE AVILA Please have a careful look at all the 4 videos starting here:@4CFoPl99FpgFor more details you can view this link:@tag/database-management-using-excel-userform-with-vba/
Hello Dinesh Kumar Takyar. Your videos helped me a lot. I copied your code here from your link and tested it. It works fine, but i want to have two text cells, instead of one with text and a number. What i have to change in the code?
Sir, this works very well for a single cell to copy. However I have a range of cells which i want to copy (e.g. from J9 to J22) then what should be the changes made in the code?
This guide was fantastic! Thank you!
Is there any way to have it delete the information on the first sheet after it copies it over to the second sheet?
Dinesh. Your code works great. I am trying to update not only one sheets but two with the same data.. How would I use this code to update two sheets with the same information?
Pedro Robles Use the code for the other sheet also!
You are really doing great!! I love your tutorials
Glad you like them! Please share the Excel VBA tutorial with your friends too.
I find this very informative. How ever I am trying to solve this delima.
I have a workbook with multiple worksheets. Each sheet is setup to display information for each individual.
I would like each individual to see/read only their sheet.
Example sheet 1 Mary. Sheet 2 Ann sheet 3 Betty
Can I send the workbook to all of the, but how can I make so they can only see their sheet?
Sirji you post really informative videos. 1 que. Sir Hi,
I have my Excel file which is updated through an odbc connection. But how can I sync that Excel data into Google Sheets that could be updated automatically in periodic intervals but without using 3rd party connectors. Any Solution?
Thanks
Thank you Sir! After hours spending on help everywhere else, your virtual class helped me with my problem.
Hi Dinesh, your videos are very helpfull and have excellent knowledge in Excel, could you please load some more videos for VBA please for beginners like how to code in VBA please.
This was exactly what I have been looking for :) If I wanted to delete the data previously entered once returning to cell C4 on sheet1 is there another step I can enter?
Hello, thank you for your video and explanation!
How it works if I have hundrends of worksheets? Then how to link certain cells from each sheet to cells in a master sheet? So far I didn't find a way to do tad, so I hope that you could help me with this! Thank you in advance!
Try it out! If you have hundreds of sheets then use arrays.
Thanks You so much Dinesh ji, Your efforts made Our lives Easier... Thanks Alot for Sharing such Knowledges.
Thank you kindly Dinesh. A most helpful video indeed. I am currently looking for your code to refresh Sheet 1.
thanks for always doing such great videos. how would i transfer specific data from cells in a workbook to 3 sheets instead of 2 . using a button?
Own 4 rental properties. what I like to see is how each property is functioning/doing on a single sheet. I have for each property taxes, insurance, maintenance, yearly total rental, management fees. I like to see these on a single sheet with 4 different named properties. In addition, like to see the total loss and profit for all the 4 rentals. I also like to see bar graph next to each property for comparison. Thanks
ive put many names beside the NAME and PROBLEMS, ive encounter run time overflow 6, what will i do?:))this video really helps.
Mr. Dinesh your the man thanks for the tutorial.
Very nicely done with every detail. Thank you for sharing the knowledge with us sir. I have just got a job in a hotel construction site as supervisor on Fire sprinkler system . I need to make daily , weekly & monthly report with inventory, of pipes, sockets,tees, sprinkler head etc, + on progress report how many meters of different pipe on level and blocked have been made. Sir is there any way you help me. Thanking you , I appreciate your help!
Truly
Neal
+Neon Thaiba This link might help: www.exceltrainingvideos.com/inventory-management-in-excel/
This was very helpful. It would also be helpful to use this on a master sheet, which then updates subsequent sheets depending on which sheet it belongs to. I want to use this to create a workbook that assigned responsibilities to different people who have a sheet assigned to them. When I put the data in the master list, I want it to populate the data in the correct person's sheet so that when they click their sheet, they only see their data, and not the other person's. Do you have a way to do that?
This link will help: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
You can also search my website www.exceltrainingvideos.com for many more solutions related to your query.
Hi Dinesh
great video as i am a newbie to excel. I have 2013, it all works well bar when i click the command button the info copies over the last entry. could you please advise
If Worksheets("receipt").Range("A6").Offset(1, 0) "" Then
Worksheets("receipt").Range("A6").End(xlDown).Select
I have used "L" and not "1"
This video is so helpfull ... Can i put a button in sheet 2 and the name of button is back ??that way its became easy to back on sheet 1???
+lordwin crescencio Yes.
how ?can you show to me pls?
Click on File. Select Options. Click on Customize Ribbon. On the right-hand side under Main Tabs check Developer.
Thank you Mr.Dinesh.. wonderful tips..
Always welcome! Please share the VBA tutorials with your friends.
By the way - this material has been invaluable!!
Hello Dinesh,
Thank you for your videos, they are very helpful.
I have a monthly report that I have to send out. There are 3 sheets of data and then a summary page. How could I make a template, where copy and paste the new files, and update the summary page. I use formulas now, but it is very tedious update each cell.
Thank you!
+Kristal Butts Have a look at the link below or do a search on the website:www.exceltrainingvideos.com/how-to-generate-reports-in-excel-using-vba/
Thank you!! I truly appreciate it!
That was the problem.. I changed the 1 for an L... and did works OK!!!! Thanks!!!!
Great job Dinesh. I was able to utilize your code and it works great! Now, I also would like to create a Command Button on Sheet2 that allows me to move all the data in one row back to the the sheet1 form. The decision of which row to move back should be determined by me manually selecting the first cell on the row (lets say I would highlight the name and then press the button). Could you help me with this?
Thanks in advance!
Carlos Rodriguez This link might help. Instead of 'change' property you could use the 'selection property:@archive-excel-data-using-vba/
Dinesh Kumar Takyar Thank you for the info. I was also wondering if you know why the macros enabled file is messing my computer's date. When the file is open my computer's date is changing to a past date and I don't know why I'm having this issue? Could you help?
Can you show me the line of code you are using with the DATE function?
Did you place headers in sheet2 as I've shown in the video?
hi sir Dinesh,
thanks for this very useful tips. i have further question regarding transfer of data from one worksheet to another worksheet using a particular template. i've been using one of your formula in recording all issued check and it really helps, but the formula doesn't work well to record our check vouchers transaction...
1. in a voucher i would need to save the:
voucher no.
voucher date
payee
particulars
amount
account name and its amount
the question is, what if the voucher i prepare contains multiple account name and the template (check disbursement book) where im going to save the datas was set like having a sandries column. this is to avoid longer horizontal recording.
template to save my data have 21 columns for accounts names includes voucher no, date, payee,particulars, ref. no., and amount of voucher. all accounts name that was not listed in 21 columns should be recorded in the 22th column which was the sandries. hope you help me sir. million thanks in advance.
Sir, I like your vedios so much... easy to understand and easy to use..
Thanks Sir, did learn From your Useful Videos a lot.
thanks
You are welcome. Please share with your friends also.
Excellent video, you explained it so even a novice an code in VBA. I have a problem I am trying to solve. I have about 700 excel files that I want to collate the information from into one excel document. Could you post a video showing how to write a VBA code that would sequentially go through an series of consecutively named files and import their data into a master document?
Thank you.
B. Patrick Murray Try this: www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
Dinesh Kumar Takyar That was exactly what I needed. Thank you!
Good job! Thanks for the clear help with a common real-world situation.
Good and really helpful which is easy to understand.
You're right! I've mentioned this in many videos and also commented. But where did you get 'downl'. It's just 'down'.
Great posting. If you don't mind though, what would the code be if there is more than 2 cells of data that needs to be transferred over to another worksheet? I think where I am running into the problem is at the ActiveCell.Offset portion. If there are 3 cells of data to transfer, would you use ActiveCell.Offset(0,0,1).select? I am guessing not because it doesn't work properly when entering this. Any help would be greatly appreciated!! Thanks again!
Watch my latest video. In the code set Transpose to FALSE.
Nice One......,
Your detailing was awesome,,,,,,,
Thanks for the video! I have one a similar questions but it should save in a separate spreadsheet. Is that possible ?
www.exceltrainingvideos.com/how-to-transfer-data-from-one-excel-workbook-to-another-automatically/
Or search www.exceltrainingvideos.com
Very detailed video and more of teaching how to do than just show how itz done. :)
I was looking for this but I have more than 1 cell to capture at the same time. ex in this scenario would be.. capturing 10 customers name at once and putting them int he other sheet. Trying to figure out how to do that. But thanks for this vid. Appreciate it.
You can capture as much data as is in your worksheet and transfer it to another worksheet to a relevant address. You just need to define the range!
Also, please do a search on my channel to find the topic or solution of your interest. You may also like to visit my website: www.exceltrainingvideos.com
Thanks.
Dinesh Kumar Takyar
Thanks for the quick response. I'm tried changing the Range ("C4") to lets say Range ("C4:C14") but somehow it doesn't capture all. Lame way would be to copy paste the same forumula multiple times and just change the cell name.
One more thing I need to do is make that Update click button automatic. Like it should click automatically every 2 mins instead of me clicking Update.
Thanks for the website link. Very interesting and browsing through right now. :)
Have a look here: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
Hi Dinesh,
I want to use the basic principle you are using here. The difference is I have invoice number, address, then I have the break down of the job which includes all the costing. When the job is complete I want to send it to another sheet (Lets call it All Jobs) that keeps records of all jobs and removes from active sheet to add a new job in its place. The active sheet is only to have "Current jobs" in progress, so as I hit the "Update" button those jobs completed can be stored to All Jobs. The sheet i'm referring to has a lot of formulas in it to calculate tax, cost etc etc. So I'm hoping I can highlight a row then hit update to send it on. The other question I have, will the "All Jobs" sheet automatically file the date in a available row, as I will be using the same row over and over again in the "Current jobs". Hope this makes sense ...Thanks Again Heath
*Will it automatically file the data in a available row in the "All Jobs" sheet ?
thanks for your video review, I did have a question, say I waned to transer a "date" from a DATA SHEET, to automatically post to a UPDATE SHEET, how do we write a date?
How do I add more columns? The video was super helpful but i have 5 rows in sheet1 that need to be transferred to 5 rows in sheet2. Thanks!
+Breanne L This link might help: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
Hi there.
Thanks alot for det video. This is not automaticly bcz I have to klick on the Botton to transfer the data.
Hi Dr,
i appreciate the hard work you put in for this video. I under stood the whole programming but i want to do it using Range reference. Could you please help me on this Thanks in advance....
Regards,
Essa khan
you are right that is because the correct is
End(xlDown)
and not
End(x1Down)
Thank you sir for sharing this tutorial, i wonder how to transfer data between two workbook, i mean from sheet in workbook A to sheet in workbook B, could it be possible ? thanks in advance sir.
risdo wilson Nainggolan www.exceltrainingvideos.com/how-to-transfer-data-from-one-excel-workbook-to-another-automatically/www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Very good video. Well explained, Thanks.
Hi Dinesh Sir, Please help me out in this issue. I work in an MNC. I need to take count of every individual emp by EOD or next day morning. I need a Macro to simplify my work. Basically I want a macro to be developed for the following situation,
Every Individual emp will have their own excel files with their individual names at one folder with the folder name: DAILY COUNT
They will save their data in that sheet in the following manner
In A1 column DATE heading
In A2 Emp will press ctlr+: to update date (i.e. Current Date)
In B2 Emp will paste all the Ticket# processed on that day (our targets range from 0 to 120 per day), as follows,
Ticket#1
Ticket#2
.
.
.
Ticket#120
Then emp will save it and close.
• Now I want to have a Master file in folder (DAILY PRODUCTION)
• Master file name DAILY
• In that Master file I need a Macro(button) after running a macro
o A new excel file should be opened
o All the data of every individual emp should be pasted in it with the individual subtotal(grouped individually) and total at the end Total production = sum of all individual counts
o After running macro data in a new opened excel file should look like follow,
Name | Ticket# -- as Heading
Emp1 | Ticker#
Emp1 | Ticket#
Emp1 | Ticket#
+ Emp1 Count | count of the above Ticket#
Emp2 | Ticker#
Emp2 | Ticket#
Emp2 | Ticket#
+ Emp2 Count | count of the above Ticket#
GrantCount | Sum of all individual counts
*Above shown + symbol denotes all the count is displayed as grouped
Now I manually will save this file with the name of that particular date.
Please let me know if it is possible.
Thank you so much in advance sir.
How would you pull entire columns though? For example enter all my data into columns A & B of 3 different excel sheets and have them updated into 1 master sheet? Can you please help. The video is good, and is a good start for the coding but I'm confused on how I would need to tweak the code to make it do what I want.
Hi Sir,
Thanks for sharing your knowledge with us.
I have saved multiple excel workboobs in one folder 📂 and I want to consolidate those excel files into one excel file, one sheet.
Regards,
Sandeep
These VBA solutions will guide:
www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
www.exceltrainingvideos.com/tag/get-data-from-multiple-worksheets-in-multiple-workbooks-into-master-workbook-with-vba/
Hi, great video tutorial, easy to follow. However, im stuck on a little issue. I want my worksheet to transpose more than 2 cells, say if i wanted to transpose 3-4 cells, how would it be possible to do? If you have a video could you link it in the comment or let me know how to do this. Thanks
hi dinesh sir
This is amit,I work in bpo sector ,we have to show the work which we do daily in our productivity sheet and send it to the person who collate the data ,so I want a button in a productivity sheet that automatically copy the filled cell and past it in another work workbook name "mastersheet" which will be on the desktop,so that the person who collate the data will save a lot of time
Hello sir, this is amazing. Thank you very much for your video. It was very useful and very easy to u derstand. Could you please show us how to clear the cell automatically after we hit UPDATE button? I enter the data, then hit Update, but the old data is still there. Is it possible to have an empty cell after i hit update butto . Please let me know when you can. We are watching you all the ways from US. Thank you, Jack.
Thanks for the video, I would like to do this but for multiple rows how would I do this?
Thanks for the tutorial. What's the significance of adding in the word "customer" in the code? for example, in the beginning you wrote "Dim CustomerName". if we write "Dim Name" would that be enough? in the first worksheet there is no mention of the word "customer" so why do we need to add it in the code?
sir i also having a same problem to calcute employee history large amount of entries pls help me for example if once i create a entry on worksheet if its already have it must shown back example i put a staff number 123 past entry details must be shown and if i want to update i have to deside yes or no and i need with large amount of cells please help me sir thanks in advance and i thank u a lot now i know little about vb only because of you i promise honourly your a best teacher
This is a very good video and thank you for posting. Although most users have trouble understanding Excel language (VBA). Why not just show a video recording a macro, that would be a much easier way to understand VBA. A recorded macro showing step by step would be simpler and more useful for most users, that way they can do other things that they may need with excel. Just a thought.... :)
Had the same problem with only two entries and overwrite of the second on subsequent entries. you need to select the header row as your first entry point (not the blank field below). 8:12 mentions B5 but entry point as correct in screen code at 8:47 is B4. Changing thaty sorts it for me.
Sir, Very informative session
I have a small issue
i have copied this code and done but after second entry it is deleting the second entry and pasting the third entry on same column of second
kindly advice
HELLO DINESH, I WATCHED YOUR TUTORIAL I REALLY ENJOYED IT. THE PROBLEM I HAVE IS THAT I AM WORKING ON GOOGLE ONLINE EXCEL AND FOR SOME REASON I CANNOT FIND THE DEVELOPER TAB SO I CAN CREATE AN UPDATE BUTTON. CAN YOU HELP ME FIGURE HOW I COULD FIND IT?
Excellent Method of teaching.
xl = XL and NOT x1 = XONE
Hey Dinesh! I hope that you can help...I am currently working on an Excel workbook that allows for a user to input specific information pertaining to WIP product, click the command button to submit, and then data to compile in a secondary spreadsheet; however, I do not know how to get input data to the next cell of the secondary spreadsheet without clearing out the original entry. I would like to keep a running log of entries in one master location so that we may effectively gain insight into tracking our WIP. Here is the code:Private Sub cbSubmit_Click()
Dim User As String, ProductScan As String, ScanFromLocation As String, ScanToLocation As String
Worksheets("WIP_Finder").Select
User = Range("B9")
ProductScan = Range("C9")
ScanFromLocation = Range("D9")
ScanToLocation = Range("E9")
Worksheets("WIP_History").Select
Worksheets("WIP_History").Range("A2").Select
If Worksheets("WIP_History").Range("A2").Offset(1, 0) "" Then
Worksheets("WIP_History").Range("A2").End(xlDown).Select
End If
ActiveCell.Offset(0, 0).Select
ActiveCell.Value = User
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ProductScan
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ScanFromLocation
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ScanToLocation
Worksheets("WIP_Finder").Select
Worksheets("WIP_Finder").Range("B9:E9").ClearContents
Worksheets("WIP_Finder").Cells(Rows.Count, "A").End(xlDown).Offset(1, 0).PasteSpecial
thanks very much but i have a question if i want to take a cell value and merge it in middle of text cell
hi sir,
I work for a mnc where we have to calculate time taken to process each case so if we have any automated excel sheet where we can put the name and click on start time and once we complete click end time so that time taken to process is calculated.
Great. It was clearly explained. Thanks a lot.
I have a related question -
Can I pull entire rows of data from one worksheet into another sheet based on information from one column? I need to feed all data for one department from my master spreadsheet into a separate sheet for just that department. Thank you!
Thank you. Very helpful.
How do I make the data entered on sheet 1 disappear once the "update" button has been clicked. I don't want my users to have to erase over previously-entered data that has already been imported into the second sheet.
These links will help:
www.exceltrainingvideos.com/tag/how-to-create-report-from-excel-data-sheet-with-vba/
www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/
Invest in this book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
Or visit www.exceltrainingvideos.com to learn more for free.
Superb! it can also be done much simpler by using the record macro and assign the macro to a shape object (button).
Check this video out: ua-cam.com/video/XTdohWvRqeY/v-deo.html
nice tutorial sir, can you make example using value of 1,0 or true ,false instead of command button. thanks!
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
Or visit www.exceltrainingvideos.com to learn more for free.
this was awesome, I have been doing regular roll-ups huge time saver thank you
thnx sir,very useful,could you plz help me out to filter data in excel sheet based on listbox above the data e.g by country data selection.
first thanks a lot, Yes but in short form like in sheet1 Cofficent of performance and in sheet2 it's name is COP. is it fine or wrong?
Hello Sir, How are you?, sir your video is helpful for non-user of VBA, sir please help me in a matter is that, how to edit the data copied in sheet2 kindly confirm me how to do that??
Thanks in advance
Thank You for this video. It helped me with what I was trying to do. However one of my headers is DATE and it errors out on that saying complied error Expected: variable when entered DATE As String or as DATE As Date. It enters the first line perfectly but then when I enter my second vehicle number and hit update it goes to my code sheet and highlights the Worksheets("MonthEndReport").Range("B3").End(x1Down).Select. it doesn't want to let it go down to the next line. Any help?? Is there another video I can watch that might help me with this? They are very informative.
Worksheets("MonthEndReport").Range("B3").End(x1Down).Select ---> Worksheets("MonthEndReport").Range("B3").End(xLDown).Select ---> l or L for London
DATE is a reserved keyword.
Hi computer club,,,
Your videos are really helpful,,,
i have a requirement..im working fully excel based job..i need to create a multiple sheet on single click which contans different names
Good morning sir. Sir i want to filter data from closed excel sheet with 3 criteria in dropdown list based on date range selection. So please make any vedio on this