Beautiful. Exactly what I was looking for. Very well explained and presented. The way you have walked us through the code makes it easy to imagine how building upon the looping will accommodate more sheets. I hope. Thank you very much! Update: confirming. More sheets are easy to add. Changing the number of required columns is not difficult. This is also quite easy to change from a range of columns to a single column by replacing ´Range’ with ´Cells’ Again, thank you. 10/10
Excellent explanation in this video. What would change if data transfer from sheetX to sheetY depended on matching multiple criteria? Want to transfer data from sheetX to sheetY per these rules: 1) transfer data from SheetX row # column X only if a name is present in previous row column Z 2) data must copy to sheetY row-column that matches sheetX worksheet name (a date dd-mm-yy) 3) note: there is a maximum of twenty-eight scores to transfer 4) note: row 5 column X cell value belongs to name in row 4 column Z -- and so forth 5) note: all worksheets are in same Workbook 6) could ask user to input date that values will copy to in SheetY row instead of capturing worksheet name SheetX (this worksheet name is "dd-mm-yy" format) Column X Y Z row 4 Bakshi row 5 ..................... 88 . row 20 Patel row 21 ..................... 75 . row 36 Raju row 37 .................... 60 . SheetY (this worksheet name is "Roster") Column A B C D ... Z Patel Raju Kalita Bakshi ... row x dd-mm-yy _____ _____ ______ ... _____ ... row y dd-mm-yy _____ _____ ______ ... _____ ... row z dd-mm-yy _____ _____ ______ ... _____ ... .
Hello Sir, this is really great.. i just want a bit more help on this ..in this video the macro is used to copy and past a static range like B , C or D . i would like to know if we could get this done based on headers example in sheet1 phone is B and Sheet2 D so instead of D can the range be defined based on the header "phone" i am asking for this because the reports that i receive same headers but not in same order .. hope my question is understandable
Hello Mr. Takyar , it was very helpful this vba code . lets suppose instead of name i have vendor code which is listed under column B and as you noticed i have more than one vendor code therefore how can do the same in this case because what i need to go to another vendor code . Thank you and best regards.
Hello sir. I have a project plan (Sheet 1) and want to move tasks to another tab (Sheet 2), in the same worksheet, if the tasks are indicated as "Closed" or "Complete". Can this code be used to do this? What additional coding should be entered to remove the blank rows from Sheet 1? Thank you.
These 2 links to the relevant VBA tutorials will help: www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/ www.exceltrainingvideos.com/how-to-generate-reports-in-excel-using-vba/
Hi Dinesh, Can you help me with another scenario of Data transfer. My sheet1 is the invoice page which i use it for me to input relevant data and print out the invoice to my customer. I have another sheet i.e. sheet2 which is used for keeping a record of all the invoices that i have issued out to my customer. In the sheet1 (invoice page), It contends data such as Invoice No. , Customer Name, Total (excluding GST) , GST @ 6% and Total (including GST) which are important for me to keep a record in my sheet2 whenever i issue an invoice. I need to have a command button on the sheet1 (invoice page) for me to transfer those data from sheet1 to sheet2. Appreciate your kind assistance on this.
What would I do if the columns were not consistent? As in, instead of being in column B and F, copying the data, and extracting it, is it possible to copy/extract data from a column that has a consisten label such as "Tools"?
Sir can you please guide me to transfer data in other condition. I have a similar arguments but doesnt have any name written in sheet 2 if i have “jason” as name written in sheet 1 and entire row of that data shall paste to sheet 2 of that “jason” name . Can you write the code for that please
Hello sir ... Your video helped me a lot to transfer Matching data. I want transfer unmatched data from one worksheet to another based on condition. How it will be done?
Hi Sir! I hope you can help me with a solution, this is nearly what I am looking for. However, I only want to transfer a specific cell value from sheet to another based on 2 conditions met.
Thank you for your superb, brilliant and precise explanation, making it easy for me (non coder) to follow and use your example. your code worked great in one of the file for me BUT i got stuck with Column A (myname) which has some blank cells within the data set. Code gives an error @ myname = Sheets("sheet1").Cells(i, "A").Value. Please help to work around this, source can have blanks cells but code should continue to match further. Thanks in Advance
Dear Dinesh Kumar, I see you have great knowledge on vba and just wondering if I could use to solve my problem. I am trying to build up a vba sub to copy data in from different columns from one sheet amd past this data in another sheet in different columns as well. Both sheets the data in each column is organized by date. When running the sub the user will have to put the startdate and the end date to specify the range. Then from each specified column data will be copied from this sheet and past into another sheet in specified columns within the same date range. I could email you a macro that works but this only copies all the data from one sheet to the other based on startdate and enddate. However, I need now to specify which columns I need to copy and which columns I want to past this data. WOuld you be able to give a hand?
Thank you very much. This was very helpful Sir! I think this will solve my problem. The only issue that I might have is the memory used which migh make the macro to be slow. The main goal will be to make the macro to transfer data from two different sheets to one master sheet. This operation will be conducted 10 times due to the specific case I have. I hope the final macro won't be very heavy. Thank you
I have written the macro however I believe that due to the number of .select and .activate operations I am getting the error "Run time 1004" method range of object worksheet failed sometimes I also get the run time error 13. DO you know what could be possibly happening? I can send you the code if you want. Thanks
Dear Dinesh, the macro is great. How can I identify if in sheet1 has to same name entries (with different details etc. phone number), and transfer it to sheet2 but each one in a new row?
Run a 'for next' loop with an 'IF' condition. This link will help: www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/ Or search www.exceltrainingvideos.com
Excellent tutorial, thank you!! I hope one day you can do a tutorial with merged cells. I have this table that someone else created where -using your table from the video as example - the name 'Jacob' would be in a range of merged cells (A2:A10) instead of just being in "A2" cell. I dont like when ppl use merged cells :/
Hi Dinesh, can you help me with another scenario? I have a workbook (a) that logs a ticket when it comes in and updates to another central workbook (b) with this information. I'm then trying to go back to workbook (A) and input other field values about the outcome of the logged job, once this has finished I then need it to update the original Central workbook (b) with it's information. but to do this I need the VBA code to lookup job number and post code and then copy the information from workbook (a) into workbook (b). I really hope you can help me out with this request Thanks Phil
I have a folder named Patient folder. In the folder excel files with different patient names. I need to transfer the name of the patient, in a specific cell, and totals on 12 sheets named each month of the year. The data is going to transfer to a master excel spreadsheet with one sheet. How can I do this?
Hello Mr. Dinesh. Every Month I need to copy a range of data (e.g. D4:D32). The column is based on the month so for 1-4-2018 it is D4:D32 and for 1-5-2018 it is E4:E32 and so on.... Then I need to paste this range into another workbook where also the column is based on the Month so e.g. 1-4-2018 is M4:M32 and 1-5-2018 is N4:N32 etc... The Months are from 1-4-2018 till 1-4-2021. So I need a code which can copy a range of data based on the Month and also past in another workbook again based on the same Month. So I first select a Month and then I want to run the code. Can you help me with this complex case please.?
Hello sir Can you please help me with the query where there are multiple different excel workbooks with different headers different trade lines how can we copy paste the data automatically in a excel file with standard format header
Hi there, this is super helpful so thank you! I do have a question though, if instead of the range that you have copied from B to F, what if we only want the last column and it changes from time to time? I know I should use last column somehow however it doesn't seem to work for me. Any help would be great..
These links will help: www.exceltrainingvideos.com/tag/find-last-column-automatically/ www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/ Or search www.exceltrainingvideos.com
Hi Sir, I need your help. I have a closed workbook in a network directory and I have written a code to open file. That workbook contains 7 worksheets and each worksheet has a different name. The worksheet name will always remain same ; however, there is possibility of Case(upper or lower). I want to create a macro which will open a closed workbook saved in a network directory, open it in background, crosscheck if the sheet exists and then copy data from columns from B6 to Q6 till last row. If any of the sheet doesn't exist, it should give a message box letting us know that "which" sheet is unavailable ; however, still copy data from remaining sheets. Pls help
Hi sir, Nice tutorial. One Question : If I wanna stop at ActiveSheet.Paste without the afterwards loop ( since I only have one vaule in myname comparison), how should I edit? Thx so much
Sir, your,s posted all videos are very useful and Thanks I need some doubts if it's any possible to upload excel data to excel tools as well as get PDF format? Kindly provide the video Thanks you sir
This link will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/ You can also search www.exceltrainingvideos.com
Hello! What if I just have one input in one workbook to use, and match it against a column of data in another workbook? After which, I copy specific cells from the 1st workbook and paste it in specific cells in the other workbook.
Hello, i need your help kind Sir. My problem is that i created code to that is creating 7 sheets however in original sheet there is lot of data with specific name i would like by clicking the button i can transfer data to specific list
These Excel VBA tutorials will help: th-cam.com/video/uiY0xwp9BVU/w-d-xo.html www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/ You can also search this channel or our website: www.exceltrainingvideos.com
Can you help Ive watched over and over and just can't seem to get it Master list on sheet 1 and each column has 6 items (master list has many columns I don't need) that i want to transfer
Hi Mr.Dinesh,I did the same thing but I got error 9my master file is really big and I change the range I & j to CC & CN even if used the same range I & J gives me the same error which is out of range can you help pleasethanks
Mr. Takyar, Thank you for this video. It is very helpful. One question, I have a duplicate value in MyName, though the information in each row is different. How can I pull in the next row after the first is found?
sir i want to create a type of report sheet, where i have 2 sheet one data entry sheet and other is reporting sheet. And i want to show selected range form a column.
These links will help: www.exceltrainingvideos.com/automating-new-report-creation-from-updated-raw-data/ www.exceltrainingvideos.com/how-to-generate-reports-in-excel-using-vba/ www.exceltrainingvideos.com/how-to-create-report-from-excel-data-sheet-with-vba/ Or search my website www.exceltrainingvideos.com or this TH-cam channel goo.gl/5Jx1NP
Sir, can pls explain I have query I.e., if two sheets are available in each sheet has many different columns. But in both the sheets first columns will be same . I need to combine the 2 sheets as per the first column. and remaining columns should come automatically based on first column. Can pls help sir
Can you elaborate? In the meantime you can refer to these tutorials: www.exceltrainingvideos.com/how-to-copy-column-data-into-another-workbook/ www.exceltrainingvideos.com/automatically-highlight-column-header/ www.exceltrainingvideos.com/find-last-row-column-in-data-with-blanks-using-vba/
I am trying to use this for worksheets that have 25 columns. It is failing at the ActiveSheet.Paste command. Here is what I have ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, lastcolumn)). There are also several hundred rows in each spreadsheet, I am not sure if that is affecting is as well. How can I correct this? Is ActiveSheet.Paste not usable after so many columns?
Hello , Your tutorial was helpful. I want to extend this code and write if a particular row in my name between both the sheets doesnot matches it should show an error message.Can you help me with the code?
hello sir this video help me a lot i just want a favor that i want to make function instead of button that u have made in the end plz rpl me how to make function to call macro
I need a help in creating a macro. I have 2sheets. Sheet1 has attributes in rows and the other sheet has same matching attributes as sheet1 but in different format and different orders. I want the data of sheet1 to be copy and pasted in sheet2 with there respective attributes. But the difficulty here is that the attributes of sheet1 and sheet 2 are not same nor the order of attributes. I have to first configure each and every attributes of sheet 1 with the attributes of sheet2. How to do that please give me the link of any video related to it.
Sir thank you so much for your reply but this copy and paste command will not work here. Since in sheet1 I have around more then 200 attributes with their respective data. And I want to first configure those attributes of sheet 1 with the attributes of sheet 2 since the naming of both the sheets attributes are not same. I want some technic that if I run macro in sheet 1 the attributes in sheet 2 should automatically get filled up.
Hello,Quick question - instead of copying the activated sheet1 columns as a range ( columns B-F), how can I copy individual columns that are not in a direct range (For example - B, E, L, Q)? Thank you!
This link will help: www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba Or do a search here: www.exceltrainingvideos.com
Hi Bro, I need the help of writing macros with below conditions. I have to send email of pending order list to suppliers. The excel data having approx 350 rows and 10 columns. And approx 40 suppliers, each supplier have different data Range to be auto select based on supplier name. Email address to be auto pick which is maintained in sheet 2. Please help🙏
Hi Dinesh. Thanks for putting this up its great. When I run this macro I get a "run time error 2004" Using F8 Debug it happens after the copying command and just at pasting. The problem line is "Sheets("Sheet2").Range(Cells(y, "C"), Cells(y, "F")).Select". This throws up the error.I have checked the syntax in the whole script and seems OK. Any assistance greatly appreciated.
This link with a sample file to download will help: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/ If it still doesn't work don't hesitate to comment.
Sir,Can you please help me a project? I want to extract data from different sheets in a workbook based on date. The data's are string and integers. Each sheet has 4 columns. These columns are same in all sheets. I want to extract those data's based on date in report sheet in same workbook.
Hi,Can u help me to prepare a report from master sheet to different(report) workbook that contains different sheets in which each sheet specifies the conditionally filtered value from main sheet.Means main worksheet contains 3000 items and corresponding 40 parameters.I have to extract data such that conditionally filtered each coloumn and corresponding items are to be copied to anothersheet of (report)work book which contains 40 sheets,each sheet contains data of one conditionally filtered data of one coloumn of main workbook.The names of the (report ) workbook sheet may be the coloumn names of main workbook.
Hi sir, I would like to ask on how to transfer data from rows to columns in other sheet with the same value and formula from sheet one and also based in conditions if Thanks!
thank you , but If I have tow sheets and I want to copy only the different data in sheet 2 to sheet 1 ( sheet 2 has sheet 1 and more ) in Excel usually I do advance filter between the two sheets and then I highlight the identical in sheet two , I filter the highlighted cells and copy to sheet one . thanks
This link will help: www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/ Or search www.exceltrainingvideos.com
sir,I am having a small labour work doing industries. In there we doing work in the labour hour based. 1.a,b,c. come in a day in different time we first one as first but the work not complete, b's work is 2nd work and I stat work a'swork. so the a's work first one hrs that is 10 to 11 and the work done in evening 3.30 to 6.00. I want to bill for the "a"by the hrs Base 1hrs ×150
Check out this link: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/ Or do a search at www.exceltrainingvideos.com
hi sir, please help me i want to transfer specific data (with its complete row) to another sheet. for example: in sheet one, there are a lot of rows that contains a word "john" in "NAME" coloumn and i want : when found "john" transfer the whole row . can you help me how to do this?
Dear Sir, thank you sooo much for the help. but it would be great if you can also tell me how can i make a button on sheet1 "UPDATE" so when i enter new data on sheet 1 it also automate
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 , very kind of you to reply I check the mentioned link I am a complete noob when it's regarding VBA So my motive here is I exactly want the same thing what is in this video but it just should be from workbook to another workbook. Is there some edit in the code for this video that I can perform to emulate it to workbooks ?
thank you very much for your videos cuz there are useful and interesting but i ask you for help me about how i can transfer data from sheet to another between two dates please . i will thank you
+Rami Emad Let's assume we have 'names of employees' in Range A and 'date of joining' in Range B. You can use the following code to transfer data from one worksheet to another based on data criteria: Sub mydates() Dim lastrow As Long, erow As Long, i As Long Dim mydate As Date lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow mydate = Cells(i, 2) If mydate >= "14 - mar - 2012" And mydate
Hello Sir, If we have to combine the excel files from different workbook to one workbook with same column name then how will we write vba code for that.
sir when i run this same programme i am getting runtime error 9 subscript out of range and when i debug it it shows yellow line in lastrow1 = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row please help
hello sir, Your videos give a lot of information and I really appreciate the clarity of the content. Thanks a lot!! it helps a lot. I need your suggestion in this scenario:- I want the master data to be split into 2 or N number of tables based upon certain conditions and the rows of the tables or columns are dynamic , so the code should have the capability to handle that.. is there a way to do that in macro.
Use 'AND' with 'IF'. A sample is demonstrated in the link to this Excel tutorial: www.exceltrainingvideos.com/send-emails-to-multiple-persons-with-multiple-attachments/ You can also search using 'multiple' here www.exceltrainingvideos.com
this is good thanks, can you please help little bit more..! I need to copy multiple rows from sheet1 to sheet2, sheet3, sheet4 and sheet5 based on a cell value in range M column..? pls guide me with it ASAP. REPLY
This topic has been described earlier. Search www.exceltrainingvideos.com Example: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Hi Sir, I have 21 worksheets in a single excel file and I need to copy a specific range of data from each sheet and paste it to other sheet for consolidation. It wwill be a great help if you can show us an example for that.
Dear Sir I have a worksheet that contains name of new joiners. Date Joined | Name | Date to be enrolled | Email Address | Set Reminder I have an if statement in the Set Reminder column that if "date to be enrolled' is equal to todays date, then to flag that cell in red and put the text "Send Reminder Email" in the Set Reminder column. Once all dates are identified I need to copy the email addresses from the Email Address field and add them to the To: field in a blank outlook email. I also need to send a copy of the email to myself Then in the body of the email I need to copy all the highlighted rows in excel and paste into outlook body. Could you please let me how to do this. I have the following code: Sub SendReminderMail() 'Start with the definition of the variables to be used 'Dim means Definition Dim OutLookApp As Object Dim OutLookMailItem As Object Dim iCounter As Integer Dim MailDest As String 'Create and launch the Outlook application and open new blank email Set OutLookApp = CreateObject("Outlook.Application") Set OutLookMailItem = OutLookApp.CreateItem(0) 'capture the email addresses from excel spreadsheet that has "send reminder mark" against them With OutLookMailItem MailDest = "" 'because we have text in email address we will use counta function and the email addresses are in column 4 For iCounter = 1 To WorksheetFunction.CountA(Columns(4)) If MailDest = "" And Cells(iCounter, 4).Offset(0, -1) = "send email" Then MailDest = Cells(iCounter, 4).Value 'the value next to the email address, which is on the left, if that is in the same row, but 1 column to the left, if that value is assigned "send email" 'if the mail destintation is not blank, insert a semi colon after the first address and then the code will add the remaining email addresses. Separate with semi colon. ElseIf MailDest "" And Cells(iCounter, 4).Offset(0, -1) = "send email" Then MailDest = MailDest & ";" & Cells(iCounter, 4).Value End If Next iCounter 'You will now add subject line, bbc , body of email..etc. .BCC = MailDest .Subject = "Pension Enrolment Notification.." .Body = "Reminder: Your enrolment into the company pension scheme is now due. Please ignore if you have already advised us if you do not wish to enrol." '.Display .Send
End With Set OutLookMaiItem = Nothing Set OutLookApp = Nothing End Sub I can't work out how to copy the highlighted rows and paste into outlook. I also want each email to appear separately and I need to check before sending. your help is greatly appreciated. Thank you Sue
Hi, Sir I really appreciate your work for spreading the knowledge and your experiences with out cost... I have a question which I am struggling to resolve. I am working for Saudi Base Company in Hr department we have 900 employees out of that monthly employees go for Vacation where we have to pay the fees to Govt. when ever I am paying certain amount to define employee then I have to reimbursement from our finance dept. Now my I need such sheet where all employee data is stored in sheet 1 and i need to transfer some information from sheet1 to sheet2 with unique code which is our employee number. Hope it will be answered by You Many Thanks
The solution described here could solve your problem quickly and easily: www.exceltrainingvideos.com/extract-data-from-one-excel-worksheet-to-another-using-advanced-filter-with-vba/ Alternatively, you can search at: www.exceltrainingvideos.com
Dear Sir, Thank you very much for that link but it is not my solution.. I will rephrase my query This is sheet1 (Master data) unique code is Employee Number Employee No. Employee Name CostCenter 103000 عادل اورنجيب Adeel Aurangzeb 10D-SLMTKR 102999 شاكيل انجوم ألفي Shakeel Anjum Alvi 6300-ITSAP 102998 يوسف على محمد المحمدي yousef Ali Mohamme 11D-SLMTKR 103002 منتظر علي لسكافي Muntathir Ali H Aliskaf 40D-SLMTKR 103005 مسفر رجاء الله الردادي Mesfer R Bin Sale 10D-SLMTKR 103006 عيسى محسن احمد الهلال Essa Mohsen A Alhe 40D-SLMTKR 102997 احمد عيسى المسعود Ahmed Essa AL Masood 41D-SLMTKR 102996 عبدالله كريري Abdullah Kariri 6300-HCSD 102990 خالد سالم باعبدالله KHALID SALEM BA ABDU 10D-SLMTKR 102988 ريم احمد القرني REEM AHMAAD ALQARNE 6300-HCRED 102977 ابراهيم علي الشميطلي IBRAHIM ALI ALSHEMA 99-COR-GRP 102971 خالد ناصر جرجير KHLEED NASEER JRJEER 30D-SLMTKR 103013 محمد ابراهيم النهاري MOHAMMED IBRAHIM NH 30D-SLMTKR 103018 مصطفى حسن ال دوريش MUSTAFA HASSAN ALDARW 40D-SLMTKR 103019 احمد عادل الشهاب AHMED ADIL AL SHIHAB 41D-SLMTKR 103021 حس محمد ال داوود HASSAN MOHAMMED ALDAWOO 40D-SLMTKR 103023 حسن عبدالله البشراوي HUSSAN ABDULLAH ALB 40D-SLMTKR 103025 يزيد محمد بايزيد Yazeid Mohameed Bayazei 99D-SLFS 103026 طراد سمير عبدالباري Toraad samerr Abdulb 99D-SLSC 103034 سلطان مبارك الجوهي Sultan Mubarak Obadal 30D-SLMTKR 103035 طارق موسى الخواجي Tariq Mousa Alkhawaji 10D-SLMTKR 103039 عمران محمد الهندي Omran Mohammed ALhindi 6300-HC 103040 احمد محمد حوباني Ahmed Mohammed Hawbani 10D-SLMTKR 103043 محمد مورتزا جونيد Mohammed Murtaza Junai 12D-SLMTKR 103044 دايناش ممتاز علم DANISH MUMTAZ ALAM 10D-SLMTSS Sheet2 Name+A1:H28 of Claimant : عبدالفتاح خضر رزق اسـم المـوظف: ID: 103222 A/C # NOTE Posted by NO. ID Name اســـــم DIV/LOC Description الوصف Charge To تحمل على المبلغ Amount Total المجموع 1 رخصة عمل ثلاجات 100 2 رخصة عمل ثلاجات 100 3 رخصة عمل ثلاجات 100 4 رخصة عمل ثلاجات 100 5 رخصة عمل ثلاجات 100 6 رخصة عمل ثلاجات 100 7 رخصة عمل ثلاجات 100 8 رخصة عمل ثلاجات 100 9 اصدار جديد ثلاجات 650 10 اصدار جديد ثلاجات 650 11 اصدار جديد ثلاجات 650 12 اصدار جديد ثلاجات 650 13 رخصة عمل ثلاجات 2500 14 تجديد اقامة ثلاجات 650 15 تجديد اقامة ثلاجات 650 16 رخصة عمل ثلاجات 200 17 خروج وعودة ثلاجات 200 18 خروج و عودة ثلاجات 200 Support Services Supervisor Total SR. Like wise i want to just type the employee number on id it should automatically appear the Employee Name and the cost-center as mention in sheet 1. Can you help me..
Hi I want to transfer data from sheet 1 to sheet 2 by clicking update i have the file i wrote the code correctly but i don't know why it doesn't working
Sir I have an invoice format and in that format i have invoice no. Date, customer name, addreas, contact no., state, gst no, items, hsn code, qty, unit, price, amount, cgst, sgst, igst, other charges and total amount columns How we can transfer and save the data of above columns in other sheet table, keeping in mind that in our invoice format we have 15 items and when we are transferring the data from invoice sheet to other sheet these 15 items should save also Pls help me to save this data as u explain in your video....thanks a lot
This VBA tutorial will help: th-cam.com/video/IVRl2BguSPE/w-d-xo.html or search this channel. There are many videos on invoices. You can also search my website www.exceltrainingvideos.com
Beautiful. Exactly what I was looking for. Very well explained and presented. The way you have walked us through the code makes it easy to imagine how building upon the looping will accommodate more sheets. I hope. Thank you very much! Update: confirming. More sheets are easy to add. Changing the number of required columns is not difficult. This is also quite easy to change from a range of columns to a single column by replacing ´Range’ with ´Cells’ Again, thank you. 10/10
Hope somebody can share the files sample. When I try follow one by one my code doesn't paste anything. Thank you.
I would like to thank you and to tell you your teaching are very good for novice persons like me that wants to learns.
Glad to hear that.
You da man! Saved me 3 days of work. Was done in 1h with this.
Glad to hear that!
Thank u sooo much ...by referring this video I have completed 3 hours manual work in just 5 mins 🙏🙏🙏🙏
Great 👍 Please share with your friends too.
Sir, you are a gem. Many thanks. God bless.
Wow sir, you made my day ...
This helps alot to my work
Thanks.
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Hats off to Dinesh Sir, very good teacher
Thank you! Please share the Excel VBA Tutorial with your friends.
Sir very nice teaching lots of thanks.
So nice of you. Please share the Excel VBA tutorial with your friends too.
Thumbs up to your great tutorial for beginners like me. It helped me so much in my work where i needed to get excel data to be better managed.
Very useful for working professionals.Thanks for knowledge sharing.
Thank you so much...! For sharing your videos. It help me so much to solve my problem.. I'm so happy that know your chanel...
Thanks Sir, it's very helpful
Thank you Dinesh for this video it is a life saviour for my school project
Excellent Job.... Keep your Good work going.... Thanks....!!!!!!!
just fantastic - thank you!
Glad you enjoyed it! Please share with your friends.
thank you my friend, helpfull and with correct code
Glad it helped
very good information.
Great sir, Dhanyavadagalu sir.
Amazing trick! this will help me a lot :)
Excellent explanation in this video.
What would change if data transfer from sheetX to sheetY depended on matching multiple criteria?
Want to transfer data from sheetX to sheetY per these rules:
1) transfer data from SheetX row # column X only if a name is present in previous row column Z
2) data must copy to sheetY row-column that matches sheetX worksheet name (a date dd-mm-yy)
3) note: there is a maximum of twenty-eight scores to transfer
4) note: row 5 column X cell value belongs to name in row 4 column Z -- and so forth
5) note: all worksheets are in same Workbook
6) could ask user to input date that values will copy to in SheetY row instead of capturing worksheet name
SheetX (this worksheet name is "dd-mm-yy" format)
Column X Y Z
row 4 Bakshi
row 5 ..................... 88
.
row 20 Patel
row 21 ..................... 75
.
row 36 Raju
row 37 .................... 60
.
SheetY (this worksheet name is "Roster")
Column A B C D ... Z
Patel Raju Kalita Bakshi ...
row x dd-mm-yy _____ _____ ______ ... _____ ...
row y dd-mm-yy _____ _____ ______ ... _____ ...
row z dd-mm-yy _____ _____ ______ ... _____ ...
.
Very helpful thank you Dinesh! :)
Nice work and nicely explained :-)
Hello Sir, this is really great.. i just want a bit more help on this ..in this video the macro is used to copy and past a static range like B , C or D . i would like to know if we could get this done based on headers example in sheet1 phone is B and Sheet2 D so instead of D can the range be defined based on the header "phone" i am asking for this because the reports that i receive same headers but not in same order .. hope my question is understandable
Thank You so much .. This is awesome
Nice vdo thank you so much
Thanks.
What if I want two conditions that if name and mobile number matches then only pull data. Can it be done?
you are awesome thanks so much
GREAT SIR
Thanks. Please share this Excel VBA tutorials channel with your friends too.
Hello Mr. Takyar ,
it was very helpful this vba code .
lets suppose instead of name i have vendor code which is listed under column B and as you noticed i have more than one vendor code therefore how can do the same in this case because what i need to go to another vendor code .
Thank you and best regards.
Hello sir.
I have a project plan (Sheet 1) and want to move tasks to another tab (Sheet 2), in the same worksheet, if the tasks are indicated as "Closed" or "Complete". Can this code be used to do this? What additional coding should be entered to remove the blank rows from Sheet 1? Thank you.
These 2 links to the relevant VBA tutorials will help:
www.exceltrainingvideos.com/transfer-specific-data-from-one-worksheet-to-another-for-reports/
www.exceltrainingvideos.com/how-to-generate-reports-in-excel-using-vba/
Hi Dinesh, Can you help me with another scenario of Data transfer. My sheet1 is the invoice page which i use it for me to input relevant data and print out the invoice to my customer. I have another sheet i.e. sheet2 which is used for keeping a record of all the invoices that i have issued out to my customer. In the sheet1 (invoice page), It contends data such as Invoice No. , Customer Name, Total (excluding GST) , GST @ 6% and Total (including GST) which are important for me to keep a record in my sheet2 whenever i issue an invoice. I need to have a command button on the sheet1 (invoice page) for me to transfer those data from sheet1 to sheet2. Appreciate your kind assistance on this.
What would I do if the columns were not consistent? As in, instead of being in column B and F, copying the data, and extracting it, is it possible to copy/extract data from a column that has a consisten label such as "Tools"?
Sir can you please guide me to transfer data in other condition. I have a similar arguments but doesnt have any name written in sheet 2 if i have “jason” as name written in sheet 1 and entire row of that data shall paste to sheet 2 of that “jason” name . Can you write the code for that please
Sir Will this work on different workbooks also will this fetch only additional(new data) that is entered in the master workbook
Hello sir ...
Your video helped me a lot to transfer Matching data.
I want transfer unmatched data from one worksheet to another based on condition.
How it will be done?
Search my channel using the keyword unique.
Hi Sir! I hope you can help me with a solution, this is nearly what I am looking for. However, I only want to transfer a specific cell value from sheet to another based on 2 conditions met.
Thank you for your superb, brilliant and precise explanation, making it easy for me (non coder) to follow and use your example. your code worked great in one of the file for me BUT i got stuck with Column A (myname) which has some blank cells within the data set. Code gives an error @ myname = Sheets("sheet1").Cells(i, "A").Value. Please help to work around this, source can have blanks cells but code should continue to match further. Thanks in Advance
Also, i would like to lock the cells in sheet1, of whatever data is transferred. Thanks in advance for your guidance and help.
Dear Dinesh Kumar,
I see you have great knowledge on vba and just wondering if I could use to solve my problem.
I am trying to build up a vba sub to copy data in from different columns from one sheet amd past this data in another sheet in different columns as well. Both sheets the data in each column is organized by date.
When running the sub the user will have to put the startdate and the end date to specify the range. Then from each specified column data will be copied from this sheet and past into another sheet in specified columns within the same date range.
I could email you a macro that works but this only copies all the data from one sheet to the other based on startdate and enddate. However, I need now to specify which columns I need to copy and which columns I want to past this data. WOuld you be able to give a hand?
This link will guide you: www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/
Thank you very much. This was very helpful Sir!
I think this will solve my problem.
The only issue that I might have is the memory used which migh make the macro to be slow. The main goal will be to make the macro to transfer data from two different sheets to one master sheet. This operation will be conducted 10 times due to the specific case I have. I hope the final macro won't be very heavy. Thank you
I have written the macro however I believe that due to the number of .select and .activate operations I am getting the error "Run time 1004" method range of object worksheet failed sometimes I also get the run time error 13. DO you know what could be possibly happening? I can send you the code if you want.
Thanks
This is excellent, thank you very much. How does one loop though the entire dataset (all columns in sheet 1) looking for myname (strings)? Thank you.
Good question! You can start with this Excel tutorial: www.exceltrainingvideos.com/automation-using-do-while-loop-in-excel-vba/
Dear Dinesh, the macro is great. How can I identify if in sheet1 has to same name entries (with different details etc. phone number), and transfer it to sheet2 but each one in a new row?
Run a 'for next' loop with an 'IF' condition. This link will help: www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/
Or search www.exceltrainingvideos.com
Excellent tutorial, thank you!! I hope one day you can do a tutorial with merged cells. I have this table that someone else created where -using your table from the video as example - the name 'Jacob' would be in a range of merged cells (A2:A10) instead of just being in "A2" cell. I dont like when ppl use merged cells :/
Great suggestion!
Use .resize method along with Range use specified. And you will be good to go. 😀
Hi Dinesh, can you help me with another scenario? I have a workbook (a) that logs a ticket when it comes in and updates to another central workbook (b) with this information. I'm then trying to go back to workbook (A) and input other field values about the outcome of the logged job, once this has finished I then need it to update the original Central workbook (b) with it's information. but to do this I need the VBA code to lookup job number and post code and then copy the information from workbook (a) into workbook (b).
I really hope you can help me out with this request
Thanks Phil
I have a folder named Patient folder. In the folder excel files with different patient names. I need to transfer the name of the patient, in a specific cell, and totals on 12 sheets named each month of the year. The data is going to transfer to a master excel spreadsheet with one sheet. How can I do this?
Hi sir, how to copy two excel sheets and merge the data in consolidated sheet?
This tutorial will help: th-cam.com/video/eOBAzI9u1_g/w-d-xo.html
Hello Mr. Dinesh. Every Month I need to copy a range of data (e.g. D4:D32). The column is based on the month so for 1-4-2018 it is D4:D32 and for 1-5-2018 it is E4:E32 and so on.... Then I need to paste this range into another workbook where also the column is based on the Month so e.g. 1-4-2018 is M4:M32 and 1-5-2018 is N4:N32 etc... The Months are from 1-4-2018 till 1-4-2021. So I need a code which can copy a range of data based on the Month and also past in another workbook again based on the same Month. So I first select a Month and then I want to run the code. Can you help me with this complex case please.?
Hello sir
Can you please help me with the query where there are multiple different excel workbooks with different headers different trade lines how can we copy paste the data automatically in a excel file with standard format header
This link will help: www.exceltrainingvideos.com/create-report-with-headers-at-specific-positions-using-excel-vba/ or seacrh.
hi Dinesh. Can you share sample files for this tutorial. Thank you very much.
Hi there, this is super helpful so thank you! I do have a question though, if instead of the range that you have copied from B to F, what if we only want the last column and it changes from time to time? I know I should use last column somehow however it doesn't seem to work for me. Any help would be great..
These links will help: www.exceltrainingvideos.com/tag/find-last-column-automatically/
www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/
Or search www.exceltrainingvideos.com
Hi Sir,
I need your help.
I have a closed workbook in a network directory and I have written a code to open file. That workbook contains 7 worksheets and each worksheet has a different name. The worksheet name will always remain same ; however, there is possibility of Case(upper or lower).
I want to create a macro which will open a closed workbook saved in a network directory, open it in background, crosscheck if the sheet exists and then copy data from columns from B6 to Q6 till last row.
If any of the sheet doesn't exist, it should give a message box letting us know that "which" sheet is unavailable ; however, still copy data from remaining sheets.
Pls help
Hi sir,
Nice tutorial.
One Question :
If I wanna stop at ActiveSheet.Paste without the afterwards loop ( since I only have one vaule in myname comparison), how should I edit?
Thx so much
TurboAftershave Don't use a looping process! Just copy and paste using the VBA code if you have only one value to deal with.
Sir, your,s posted all videos are very useful and Thanks I need some doubts if it's any possible to upload excel data to excel tools as well as get PDF format? Kindly provide the video
Thanks you sir
This link will help:
www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
You can also search www.exceltrainingvideos.com
Hello! What if I just have one input in one workbook to use, and match it against a column of data in another workbook? After which, I copy specific cells from the 1st workbook and paste it in specific cells in the other workbook.
Can this be done with a closed workbook? If so How?
This link will guide: th-cam.com/video/atUhDMkn0hw/w-d-xo.html
I am new to VBA and I’m not sure how I would combine this this video with the one in the hyperlink you sent.
Hello, i need your help kind Sir.
My problem is that i created code to that is creating 7 sheets however in original sheet there is lot of data with specific name
i would like by clicking the button i can transfer data to specific list
Search www.exceltrainingvideos.com
Hello Sir Your all videos are very useful. i have 1 query i am working on data validation i want use data validation font in marathi language.
Sir can we transfer data from one excel file to anotherr excel file on the basis of cell valie
These Excel VBA tutorials will help:
th-cam.com/video/uiY0xwp9BVU/w-d-xo.html
www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
You can also search this channel or our website: www.exceltrainingvideos.com
hi sir i would like to know if it is possible to copy the cells to another sheet if the value is yes
www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/
For more info do a search at: www.exceltrainingvideos.com
Can you help
Ive watched over and over and just can't seem to get it
Master list on sheet 1 and each column has 6 items (master list has many columns I don't need) that i want to transfer
This link will help: th-cam.com/video/qT7svnhA2ck/w-d-xo.html
Hi Mr.Dinesh,I did the same thing but I got error 9my master file is really big and I change the range I & j to CC & CN even if used the same range I & J gives me the same error which is out of range can you help pleasethanks
Mr. Takyar, Thank you for this video. It is very helpful. One question, I have a duplicate value in MyName, though the information in each row is different. How can I pull in the next row after the first is found?
This link will lead to a solution: www.exceltrainingvideos.com/counting-and-displaying-totals-automatically-using-excel-vba/
sir i want to create a type of report sheet, where i have 2 sheet one data entry sheet and other is reporting sheet. And i want to show selected range form a column.
These links will help:
www.exceltrainingvideos.com/automating-new-report-creation-from-updated-raw-data/
www.exceltrainingvideos.com/how-to-generate-reports-in-excel-using-vba/
www.exceltrainingvideos.com/how-to-create-report-from-excel-data-sheet-with-vba/
Or search my website www.exceltrainingvideos.com or this TH-cam channel goo.gl/5Jx1NP
Sir, can pls explain I have query I.e., if two sheets are available in each sheet has many different columns. But in both the sheets first columns will be same . I need to combine the 2 sheets as per the first column. and remaining columns should come automatically based on first column. Can pls help sir
Can you elaborate? In the meantime you can refer to these tutorials:
www.exceltrainingvideos.com/how-to-copy-column-data-into-another-workbook/
www.exceltrainingvideos.com/automatically-highlight-column-header/
www.exceltrainingvideos.com/find-last-row-column-in-data-with-blanks-using-vba/
I am trying to use this for worksheets that have 25 columns. It is failing at the ActiveSheet.Paste command. Here is what I have ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, lastcolumn)). There are also several hundred rows in each spreadsheet, I am not sure if that is affecting is as well. How can I correct this? Is ActiveSheet.Paste not usable after so many columns?
Hello , Your tutorial was helpful. I want to extend this code and write if a particular row in my name between both the sheets doesnot matches it should show an error message.Can you help me with the code?
Please search my TH-cam channel or my website www.exceltrainingvideos.com/
hello sir this video help me a lot i just want a favor that i want to make function instead of button that u have made in the end plz rpl me how to make function to call macro
can you do this for another workbook?
This VBA tutorial will help find a solution: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
I need a help in creating a macro. I have 2sheets. Sheet1 has attributes in rows and the other sheet has same matching attributes as sheet1 but in different format and different orders. I want the data of sheet1 to be copy and pasted in sheet2 with there respective attributes. But the difficulty here is that the attributes of sheet1 and sheet 2 are not same nor the order of attributes. I have to first configure each and every attributes of sheet 1 with the attributes of sheet2. How to do that please give me the link of any video related to it.
Try paste special: www.exceltrainingvideos.com/paste-special/ and www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
Sir thank you so much for your reply but this copy and paste command will not work here. Since in sheet1 I have around more then 200 attributes with their respective data. And I want to first configure those attributes of sheet 1 with the attributes of sheet 2 since the naming of both the sheets attributes are not same. I want some technic that if I run macro in sheet 1 the attributes in sheet 2 should automatically get filled up.
I am creating similar macro. VBA gives compile error : Sub or function not defined??
thank you sir.... its very helpfull but i want to copy and paste data from a challan, receipt, memo or voucher based on students or customers id.
Hello,Quick question - instead of copying the activated sheet1 columns as a range ( columns B-F), how can I copy individual columns that are not in a direct range (For example - B, E, L, Q)? Thank you!
This link will help: www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba
Or do a search here: www.exceltrainingvideos.com
@@Exceltrainingvideoshow do I get the completed code? Thanks it's great.
Hi Bro,
I need the help of writing macros with below conditions.
I have to send email of pending order list to suppliers.
The excel data having approx 350 rows and 10 columns.
And approx 40 suppliers, each supplier have different data
Range to be auto select based on supplier name.
Email address to be auto pick which is maintained in sheet 2.
Please help🙏
Noted.
Hi Dinesh. Thanks for putting this up its great. When I run this macro I get a "run time error 2004" Using F8 Debug it happens after the copying command and just at pasting. The problem line is "Sheets("Sheet2").Range(Cells(y, "C"), Cells(y, "F")).Select". This throws up the error.I have checked the syntax in the whole script and seems OK. Any assistance greatly appreciated.
This link with a sample file to download will help: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
If it still doesn't work don't hesitate to comment.
Sir,Can you please help me a project? I want to extract data from different sheets in a workbook based on date. The data's are string and integers. Each sheet has 4 columns. These columns are same in all sheets. I want to extract those data's based on date in report sheet in same workbook.
This link will help: www.exceltrainingvideos.com/generate-report-based-on-dates/
Sir, What pdf should I read to understand the coding.
Plz, send link.
How to transfer single row data from one sheet to another based on condiiton and input is taken fron input box. please help.Thank You
Hi,Can u help me to prepare a report from master sheet to different(report) workbook that contains different sheets in which each sheet specifies the conditionally filtered value from main sheet.Means main worksheet contains 3000 items and corresponding 40 parameters.I have to extract data such that conditionally filtered each coloumn and corresponding items are to be copied to anothersheet of (report)work book which contains 40 sheets,each sheet contains data of one conditionally filtered data of one coloumn of main workbook.The names of the (report ) workbook sheet may be the coloumn names of main workbook.
Hi sir,
I would like to ask on how to transfer data from rows to columns in other sheet with the same value and formula from sheet one and also based in conditions if
Thanks!
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
hiwhat if I have two sheets , and I want to bring only different data from sheet 2 to sheet1?
Just copy and paste from sheet2 to sheet1. My next video will be on this topic.
Check this video: www.exceltrainingvideos.com/methods-to-transfer-data-from-excel-worksheet-with-vba/
thank you , but If I have tow sheets and I want to copy only the different data in sheet 2 to sheet 1 ( sheet 2 has sheet 1 and more )
in Excel usually I do advance filter between the two sheets and then I highlight the identical in sheet two , I filter the highlighted cells and copy to sheet one .
thanks
Hi Sir, how do I transfer data from one excel to another excel, please share it will be so helpful to me
th-cam.com/video/f3v_pIaGrhU/w-d-xo.html
www.exceltrainingvideos.com/methods-to-transfer-data-from-excel-worksheet-with-vba/
Hi sir
Could you please share video,how to consolidate multiple excel workbook data to master sheet workbook for daily transactions reporting.
This link will help: www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/
Or search www.exceltrainingvideos.com
thank sir
would you plz share the same Excel file? I am getting erros with given code. Thanks
The link to this Excel VBA tutorial will help: www.exceltrainingvideos.com/transfer-specific-excel-worksheet-data-range-from-one-worksheet-to-another/
sir,I am having a small labour work doing industries.
In there we doing work in the labour hour based.
1.a,b,c. come in a day in different time we first one as first but the work not complete, b's work is 2nd work and I stat work a'swork.
so the a's work first one hrs that is 10 to 11 and the work done in evening 3.30 to 6.00.
I want to bill for the "a"by the hrs Base 1hrs ×150
How can I do the exact same thing but move the data within different workbooks. Can you please help. Thanks
Check out this link: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
Or do a search at www.exceltrainingvideos.com
hi sir,
please help me i want to transfer specific data (with its complete row) to another sheet.
for example: in sheet one, there are a lot of rows that contains a word "john" in "NAME" coloumn and i want :
when found "john" transfer the whole row .
can you help me how to do this?
www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-vba/
Dear Sir, thank you sooo much for the help. but it would be great if you can also tell me how can i make a button on sheet1 "UPDATE" so when i enter new data on sheet 1 it also automate
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 is transferring data from one worksheet from another but what I need is to transfer from one workbook to another ?
How do I do that ?
This link will help:
www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
Hello sir , very kind of you to reply
I check the mentioned link I am a complete noob when it's regarding VBA
So my motive here is I exactly want the same thing what is in this video but it just should be from workbook to another workbook.
Is there some edit in the code for this video that I can perform to emulate it to workbooks ?
@@Exceltrainingvideos the video is different do you have a way with the same function but with workbooks
thank you very much for your videos cuz there are useful and interesting but i ask you for help me about how i can transfer data from sheet to another between two dates please .
i will thank you
+Rami Emad Let's assume we have 'names of employees' in Range A and 'date of joining' in Range B. You can use the following code to transfer data from one worksheet to another based on data criteria:
Sub mydates()
Dim lastrow As Long, erow As Long, i As Long
Dim mydate As Date
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
mydate = Cells(i, 2)
If mydate >= "14 - mar - 2012" And mydate
Hello Sir,
If we have to combine the excel files from different workbook to one workbook with same column name then how will we write vba code for that.
This link will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
how do you copy newly entered company names based on dates from a master sheet of a different closed workbook without opening the workbook
sir....sheet1 me B column or sheet2 me D column to match karenga data ke nahi..???
Same problems
Yes
sir
when i run this same programme i am getting
runtime error 9
subscript out of range
and when i debug it
it shows yellow line in
lastrow1 = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
please help
+Lennon stigols Use this code: lastrow=sheet1.Cells(Rows.count, 1).End(xlUp).Row
+Dinesh Kumar Takyar Is your sheet1 really sheet1 or did you rename the sheet?
hello sir,
Your videos give a lot of information and I really appreciate the clarity of the content. Thanks a lot!! it helps a lot. I need your suggestion in this scenario:-
I want the master data to be split into 2 or N number of tables based upon certain conditions and the rows of the tables or columns are dynamic , so the code should have the capability to handle that.. is there a way to do that in macro.
How if i ihave two condition and find the result with two condition?
Use 'AND' with 'IF'. A sample is demonstrated in the link to this Excel tutorial: www.exceltrainingvideos.com/send-emails-to-multiple-persons-with-multiple-attachments/
You can also search using 'multiple' here www.exceltrainingvideos.com
this is good thanks,
can you please help little bit more..!
I need to copy multiple rows from sheet1 to sheet2, sheet3, sheet4 and sheet5 based on a cell value in range M column..?
pls guide me with it ASAP.
REPLY
This topic has been described earlier. Search www.exceltrainingvideos.com
Example: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Hi Sir, I have 21 worksheets in a single excel file and I need to copy a specific range of data from each sheet and paste it to other sheet for consolidation. It wwill be a great help if you can show us an example for that.
This link will help: www.exceltrainingvideos.com/merge-data-from-worksheets-into-master-worksheet/
Or search www.exceltrainingvideos.com
Dear Sir I have a worksheet that contains name of new joiners. Date Joined | Name | Date to be enrolled | Email Address | Set Reminder
I have an if statement in the Set Reminder column that if "date to be enrolled' is equal to todays date, then to flag that cell in red and put the text "Send Reminder Email" in the Set Reminder column.
Once all dates are identified I need to copy the email addresses from the Email Address field and add them to the To: field in a blank outlook email.
I also need to send a copy of the email to myself
Then in the body of the email I need to copy all the highlighted rows in excel and paste into outlook body.
Could you please let me how to do this.
I have the following code:
Sub SendReminderMail()
'Start with the definition of the variables to be used
'Dim means Definition
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim iCounter As Integer
Dim MailDest As String
'Create and launch the Outlook application and open new blank email
Set OutLookApp = CreateObject("Outlook.Application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
'capture the email addresses from excel spreadsheet that has "send reminder mark" against them
With OutLookMailItem
MailDest = ""
'because we have text in email address we will use counta function and the email addresses are in column 4
For iCounter = 1 To WorksheetFunction.CountA(Columns(4))
If MailDest = "" And Cells(iCounter, 4).Offset(0, -1) = "send email" Then
MailDest = Cells(iCounter, 4).Value
'the value next to the email address, which is on the left, if that is in the same row, but 1 column to the left, if that value is assigned "send email"
'if the mail destintation is not blank, insert a semi colon after the first address and then the code will add the remaining email addresses. Separate with semi colon.
ElseIf MailDest "" And Cells(iCounter, 4).Offset(0, -1) = "send email" Then
MailDest = MailDest & ";" & Cells(iCounter, 4).Value
End If
Next iCounter
'You will now add subject line, bbc , body of email..etc.
.BCC = MailDest
.Subject = "Pension Enrolment Notification.."
.Body = "Reminder: Your enrolment into the company pension scheme is now due. Please ignore if you have already advised us if you do not wish to enrol."
'.Display
.Send
End With
Set OutLookMaiItem = Nothing
Set OutLookApp = Nothing
End Sub
I can't work out how to copy the highlighted rows and paste into outlook. I also want each email to appear separately and I need to check before sending.
your help is greatly appreciated.
Thank you
Sue
This link will help: www.rondebruin.nl/win/s1/outlook/bmail2.htm
Hi, Sir
I really appreciate your work for spreading the knowledge and your experiences with out cost... I have a question which I am struggling to resolve.
I am working for Saudi Base Company in Hr department we have 900 employees out of that monthly employees go for Vacation where we have to pay the fees to Govt. when ever I am paying certain amount to define employee then I have to reimbursement from our finance dept. Now my I need such sheet where all employee data is stored in sheet 1 and i need to transfer some information from sheet1 to sheet2 with unique code which is our employee number.
Hope it will be answered by You
Many Thanks
The solution described here could solve your problem quickly and easily: www.exceltrainingvideos.com/extract-data-from-one-excel-worksheet-to-another-using-advanced-filter-with-vba/
Alternatively, you can search at: www.exceltrainingvideos.com
Dear Sir,
Thank you very much for that link but it is not my solution.. I will rephrase my query
This is sheet1 (Master data) unique code is Employee Number
Employee No. Employee Name CostCenter
103000 عادل اورنجيب Adeel Aurangzeb 10D-SLMTKR
102999 شاكيل انجوم ألفي Shakeel Anjum Alvi 6300-ITSAP
102998 يوسف على محمد المحمدي yousef Ali Mohamme 11D-SLMTKR
103002 منتظر علي لسكافي Muntathir Ali H Aliskaf 40D-SLMTKR
103005 مسفر رجاء الله الردادي Mesfer R Bin Sale 10D-SLMTKR
103006 عيسى محسن احمد الهلال Essa Mohsen A Alhe 40D-SLMTKR
102997 احمد عيسى المسعود Ahmed Essa AL Masood 41D-SLMTKR
102996 عبدالله كريري Abdullah Kariri 6300-HCSD
102990 خالد سالم باعبدالله KHALID SALEM BA ABDU 10D-SLMTKR
102988 ريم احمد القرني REEM AHMAAD ALQARNE 6300-HCRED
102977 ابراهيم علي الشميطلي IBRAHIM ALI ALSHEMA 99-COR-GRP
102971 خالد ناصر جرجير KHLEED NASEER JRJEER 30D-SLMTKR
103013 محمد ابراهيم النهاري MOHAMMED IBRAHIM NH 30D-SLMTKR
103018 مصطفى حسن ال دوريش MUSTAFA HASSAN ALDARW 40D-SLMTKR
103019 احمد عادل الشهاب AHMED ADIL AL SHIHAB 41D-SLMTKR
103021 حس محمد ال داوود HASSAN MOHAMMED ALDAWOO 40D-SLMTKR
103023 حسن عبدالله البشراوي HUSSAN ABDULLAH ALB 40D-SLMTKR
103025 يزيد محمد بايزيد Yazeid Mohameed Bayazei 99D-SLFS
103026 طراد سمير عبدالباري Toraad samerr Abdulb 99D-SLSC
103034 سلطان مبارك الجوهي Sultan Mubarak Obadal 30D-SLMTKR
103035 طارق موسى الخواجي Tariq Mousa Alkhawaji 10D-SLMTKR
103039 عمران محمد الهندي Omran Mohammed ALhindi 6300-HC
103040 احمد محمد حوباني Ahmed Mohammed Hawbani 10D-SLMTKR
103043 محمد مورتزا جونيد Mohammed Murtaza Junai 12D-SLMTKR
103044 دايناش ممتاز علم DANISH MUMTAZ ALAM 10D-SLMTSS
Sheet2
Name+A1:H28 of Claimant : عبدالفتاح خضر رزق اسـم المـوظف: ID: 103222 A/C # NOTE
Posted by
NO. ID Name اســـــم DIV/LOC Description الوصف Charge To تحمل على المبلغ Amount Total المجموع
1 رخصة عمل ثلاجات 100
2 رخصة عمل ثلاجات 100
3 رخصة عمل ثلاجات 100
4 رخصة عمل ثلاجات 100
5 رخصة عمل ثلاجات 100
6 رخصة عمل ثلاجات 100
7 رخصة عمل ثلاجات 100
8 رخصة عمل ثلاجات 100
9 اصدار جديد ثلاجات 650
10 اصدار جديد ثلاجات 650
11 اصدار جديد ثلاجات 650
12 اصدار جديد ثلاجات 650
13 رخصة عمل ثلاجات 2500
14 تجديد اقامة ثلاجات 650
15 تجديد اقامة ثلاجات 650
16 رخصة عمل ثلاجات 200
17 خروج وعودة ثلاجات 200
18 خروج و عودة ثلاجات 200
Support Services Supervisor Total SR.
Like wise i want to just type the employee number on id it should automatically appear the Employee Name and the cost-center as mention in sheet 1.
Can you help me..
Try this: www.exceltrainingvideos.com/automatically-search-excel-data-display-print-using-vba/
or do a search at: www.exceltrainingvideos.com
Hi
I want to transfer data from sheet 1 to sheet 2 by clicking update i have the file i wrote the code correctly but i don't know why it doesn't working
Check your code.
Sir
I have an invoice format and in that format i have invoice no. Date, customer name, addreas, contact no., state, gst no, items, hsn code, qty, unit, price, amount, cgst, sgst, igst, other charges and total amount columns
How we can transfer and save the data of above columns in other sheet table, keeping in mind that in our invoice format we have 15 items and when we are transferring the data from invoice sheet to other sheet these 15 items should save also
Pls help me to save this data as u explain in your video....thanks a lot
This VBA tutorial will help: th-cam.com/video/IVRl2BguSPE/w-d-xo.html or search this channel. There are many videos on invoices. You can also search my website www.exceltrainingvideos.com
nice
Thanks