Hi Andrew, thank you for your wonderful video tutorial , i am new in VBA. I am wondering what if i want to copy data into each sheet name of those files in folder ( example, copy sheet April of Movie 1 , April of Movie 2... copy sheet may of Movie1 , may of movie 2 ... ) . what the code should be ? thank you
Hi there! If you're just getting started in VBA I'd recommend our introduction online course www.wiseowl.co.uk/online-training/excel-vba/ Module 2 contains lessons on referring to worksheets and workbooks which I think you'll find helpful!
Awesome content had the same question too. Not related but how did you set your windows 10 taskbar to be the old style with separate buttons for each open program?
Thanks David, I'm glad you found that one useful! As for the taskbar: 1) Right-click the taskbar and choose "Taskbar settings" 2) Find the "Combine taskbar buttons" option 3) Choose "Never" I've tried to get along with the combined button style but I just miss the at-a-glance labels too much. Perhaps I'm getting old!
Hi, I am in need of a VBA code to create multiple workbooks from main file and copying specific rows to each of the woorbook with name of first cell of copying data and copying from row 1 to 75 to one workbook and 76 to 150 to another workbook and so on till data of main workbook is finished. Actually i have to mail the data to different people from main file and i need this to create different workbooks with their respective data and i have a code to send mails with attachments to different people.
The example in the video assumes that the My Files folder is in the same folder as the Excel workbook which contains your code. You can put your folder of files wherever you like and use the ChDIr method to point to this folder. ChDir "C:\My folder\" I hope it helps!
Awesome content! If one would like to run through all moviefiles, but only copy data from one sheet per file (a specific month for example), what needs to be added to the code? Thanks in advance!
Hi Benjamin! You'd take away rather than add to the code to achieve that. There's no need to loop through the worksheets collection if you want to copy only one of them so you could just reference that worksheet directly. I hope it helps!
Thanks verry much for the tutos... I saw almost all those about Excel and VBA. I hope you can help me with this question. I'm trying to consolidate data from multiple sheets (différent number) in another workbook, but to long to do this manualy especialy when we have 50 or more sheets to consolidate. How to do this with VBA ?.... Thanks for your help.
Hi Andrew if you could really help me, how do I specify if I have multiple workbooks where the name changes due to date and secondly I would like only to paste certain columns data from source workbooks into master workbook. 🙏🏼
Thanks for the Video. I need one more favor please. Can you please teach me how to copy multiple sheet or all sheet of a workbook and past in individual workbook considering sheet name to different location by macro
Hey! Really hoping you could help out with a bind I'm in. I'm working with multiple sheets of data, and I'm struggling to figure out how to apply multiple criteria to one sheet because it relies on data from another sheet; For context, I need to validate the subject choices of some students, but the validation is based on the degree they're studying (which is in another sheet). Is it possible to link student IDs to their respective degree programmes? Thanks in Advance!
Hi there! You can certainly link one table to another but exactly how you do that will depend on what information you have recorded and how you have designed your tables. As a simple example let's say you have a table of Students - each student has one row in that table - there is a column in that table which records the ID of the degree programme they are on. You have a second table of DegreeProgrammes - each programme has one row in that table - there is a column which records the unique ID of the programme. You can join these tables in a query similar to this: SELECT * FROM [Students] JOIN [DegreeProgrammes] ON [Students].[DegreeProgrammeID] = [DegreeProgrammes].[DegreeProgrammeID] It's difficult to give more advice without knowing how your tables are structured but I hope that points you in the right direction!
Hi , sorry for delay , because i have seen few hours ago Your explanation was Great never see that type of explanation, so one doubt : without header columns we wrote the code , how will we write the code with columns name ? if possible please explain me.
Thanks Andrew for this wonderful video tutorial!! I hope you don't mind if I ask you another question here: Say in the example you use, if I have the source data created by the movie languages, French, English, Japanese (in different sheets) and the destination file header, I only want to use Film ID, Title, then Languages (FR, EN, JP...). The source file does not have the same headers as in the destination file. How could I modify the code so that I could consolidate the selected data from those files based on their language criteria? I appreciate if you could drop a line here when you have time. Thanks a lot!
Thank you for the video! I am facing a slight issue with closing the workbook. I am trying to copy the sheets present in multiple workbooks from an input folder into a master workbook which is in output folder using a .xlsm file which contains the macro for doing the same. ``` While myFile "" Set AssessmentFile = Workbooks.Open(FileName:=myPath & myFile) For Each ws In AssessmentFile.Worksheets ActiveSheet.Name = Replace(myFile, myExtension, "") Next ws
If strFileExists = "" Then 'getting an automation error in this step AssessmentFile.SaveAs FileName:=outputFile, FileFormat:=xlWorkbookNormal Else If IsFileOpen(outputFile) Then GoTo CloseTheOpenOutputFile Else: Kill outputFile DoEvents AssessmentFile.SaveAs FileName:=outputFile, FileFormat:=xlWorkbookNormal End If DoEvents End If``` Please help if you can! Have scoured the internet for the solution. This is the closest i've come ;-;
Hi Andrew, thanks again for a lovely video. I intend to use the learnings from this video with some customization to automate same task. I started writing the code. At step where you showed opening and closing the workbooks, I have an issue. Each workbook it opens, prior to closing it, I get a prompt if I want to save changes? How to get rid of that? I tried: Workbooks.Open (Filename:=FieldSheet, ReadOnly:=True) FieldSheet.Close SaveChanges:=False FieldSheet = Dir But it keeps prompting me if I want to save changes to each and every worksheet. Please be informed that the workbooks I want to copy date from are *.xlsm workbooks. As a test, I created a dummy folder with some dummy workbook1.xlsx, workbook2.xlsx and workbook3.xlsx. No prompt. Then I added workbook1.xlsm, workbook5.xlsm and workbook6.xlsm. No prompt. So the issue is with the actual macro enables sheets I want to take data from. How to solve that? Regards,
Hi Zubair! It's difficult to say for certain without seeing the files you're working with and the code you've written. It's possible that the xlsm files you're closing have code in the BeforeClose event of the workbook which makes changes to the file which triggers the prompt asking you to save.
Hi Youssef, I don't know the answer to this but here are some links that might help you! www.excelforum.com/excel-programming-vba-macros/1247391-vba-to-send-sms-to-mobile-number.html stackoverflow.com/questions/48922232/send-sms-from-excel-vba
Hi Andrew Sir Thanks for making Informative training session. I Really enjoy and very eagerly to wait next training session. Sir I request you that how to send Bulk Messages using What’s up to Excel Sheet Using VBA.
Thank you for the suggestion Panner. I've had Access on my ToDo list for a long time but it's quite far down the list of priorities. We don't get much demand for Access training courses at Wise Owl these days - the last time I taught an Access course was in May 2016!
Knowladge and the Teaching level is high class. Thank you sir.
Thank you Rohith!
Thanks Andrew. Lots of good questions and thorough explanation with great examples.
Thanks Kevin! Happy to hear that you're continuing to enjoy the answer videos!
Thank you very much Andrew. Your video was awesome!
You're very welcome Maneet!
Another awesome video. Thank you so much for accepting my LinkedIn Connection.
You're very welcome Ravi, my pleasure! Thanks for your support!
Hi Andrew, thank you for your wonderful video tutorial , i am new in VBA. I am wondering what if i want to copy data into each sheet name of those files in folder ( example, copy sheet April of Movie 1 , April of Movie 2... copy sheet may of Movie1 , may of movie 2 ... ) . what the code should be ? thank you
Hi there! If you're just getting started in VBA I'd recommend our introduction online course www.wiseowl.co.uk/online-training/excel-vba/
Module 2 contains lessons on referring to worksheets and workbooks which I think you'll find helpful!
Awesome content had the same question too. Not related but how did you set your windows 10 taskbar to be the old style with separate buttons for each open program?
Thanks David, I'm glad you found that one useful!
As for the taskbar:
1) Right-click the taskbar and choose "Taskbar settings"
2) Find the "Combine taskbar buttons" option
3) Choose "Never"
I've tried to get along with the combined button style but I just miss the at-a-glance labels too much. Perhaps I'm getting old!
@@WiseOwlTutorials awesome thank you! I still consider windows XP the greatest of all
@@dmbalzer 😀 then we are both old! (but you're correct!)
brilliant videos thanks
Glad you like them Wojciech, thanks for the support!
Hi, I am in need of a VBA code to create multiple workbooks from main file and copying specific rows to each of the woorbook with name of first cell of copying data and copying from row 1 to 75 to one workbook and 76 to 150 to another workbook and so on till data of main workbook is finished. Actually i have to mail the data to different people from main file and i need this to create different workbooks with their respective data and i have a code to send mails with attachments to different people.
Hi Azhar! Is there a specific part of this that you need help with?
How do you link to the file of where ur excel files are? Do they have to be on desktop in a folder labeled with the name?
The example in the video assumes that the My Files folder is in the same folder as the Excel workbook which contains your code. You can put your folder of files wherever you like and use the ChDIr method to point to this folder.
ChDir "C:\My folder\"
I hope it helps!
Awesome content! If one would like to run through all moviefiles, but only copy data from one sheet per file (a specific month for example), what needs to be added to the code? Thanks in advance!
Hi Benjamin! You'd take away rather than add to the code to achieve that. There's no need to loop through the worksheets collection if you want to copy only one of them so you could just reference that worksheet directly.
I hope it helps!
@@WiseOwlTutorials Ah of course! Thank you for the answer :)
Thanks verry much for the tutos... I saw almost all those about Excel and VBA.
I hope you can help me with this question. I'm trying to consolidate data from multiple sheets (différent number) in another workbook, but to long to do this manualy especialy when we have 50 or more sheets to consolidate. How to do this with VBA ?.... Thanks for your help.
Hi, this will probably help ua-cam.com/video/Fsrw0X9hI18/v-deo.html
Thanks for your answer but this is not what I was talking about. I meant the consolidation from data-consolidate menu.
Brilliant!
Thanks Richard!
Hi Andrew if you could really help me, how do I specify if I have multiple workbooks where the name changes due to date and secondly I would like only to paste certain columns data from source workbooks into master workbook.
🙏🏼
Thanks very much, Andrew. It's a great video. Please could you share how to automate Mail Merge from VBA.
Thanks! I'll add mail merge to my list of questions!
Thank you
Very awesome. Thank you very much, my tutor.
Thank you Yasser!
Thanks for the Video. I need one more favor please. Can you please teach me how to copy multiple sheet or all sheet of a workbook and past in individual workbook considering sheet name to different location by macro
Hey! Really hoping you could help out with a bind I'm in. I'm working with multiple sheets of data, and I'm struggling to figure out how to apply multiple criteria to one sheet because it relies on data from another sheet; For context, I need to validate the subject choices of some students, but the validation is based on the degree they're studying (which is in another sheet). Is it possible to link student IDs to their respective degree programmes? Thanks in Advance!
Hi there! You can certainly link one table to another but exactly how you do that will depend on what information you have recorded and how you have designed your tables. As a simple example let's say you have a table of Students - each student has one row in that table - there is a column in that table which records the ID of the degree programme they are on. You have a second table of DegreeProgrammes - each programme has one row in that table - there is a column which records the unique ID of the programme.
You can join these tables in a query similar to this:
SELECT * FROM [Students] JOIN [DegreeProgrammes] ON [Students].[DegreeProgrammeID] = [DegreeProgrammes].[DegreeProgrammeID]
It's difficult to give more advice without knowing how your tables are structured but I hope that points you in the right direction!
Oh bless youuu!
@@isiomaogbechie8452 You're very welcome!
Hi ,
sorry for delay , because i have seen few hours ago
Your explanation was Great never see that type of explanation, so one doubt : without header columns we wrote the code , how will we write the code with columns name ? if possible please explain me.
Hi there, this video shows how to reference tables both with and without column headers ua-cam.com/video/FNjTCwV7VhM/v-deo.html
I hope it helps!
Thanks Andrew for this wonderful video tutorial!! I hope you don't mind if I ask you another question here: Say in the example you use, if I have the source data created by the movie languages, French, English, Japanese (in different sheets) and the destination file header, I only want to use Film ID, Title, then Languages (FR, EN, JP...). The source file does not have the same headers as in the destination file. How could I modify the code so that I could consolidate the selected data from those files based on their language criteria? I appreciate if you could drop a line here when you have time. Thanks a lot!
Thank you for the video! I am facing a slight issue with closing the workbook. I am trying to copy the sheets present in multiple workbooks from an input folder into a master workbook which is in output folder using a .xlsm file which contains the macro for doing the same.
``` While myFile ""
Set AssessmentFile = Workbooks.Open(FileName:=myPath & myFile)
For Each ws In AssessmentFile.Worksheets
ActiveSheet.Name = Replace(myFile, myExtension, "")
Next ws
'AssessmentFile.Close
myFile = Dir
Loop
outputFile = outputFilePath & outputFileName
strFileExists = Dir(outputFile)
If strFileExists = "" Then
'getting an automation error in this step
AssessmentFile.SaveAs FileName:=outputFile, FileFormat:=xlWorkbookNormal
Else
If IsFileOpen(outputFile) Then
GoTo CloseTheOpenOutputFile
Else: Kill outputFile
DoEvents
AssessmentFile.SaveAs FileName:=outputFile, FileFormat:=xlWorkbookNormal
End If
DoEvents
End If```
Please help if you can! Have scoured the internet for the solution. This is the closest i've come ;-;
Hi Andrew, thanks again for a lovely video. I intend to use the learnings from this video with some customization to automate same task. I started writing the code. At step where you showed opening and closing the workbooks, I have an issue. Each workbook it opens, prior to closing it, I get a prompt if I want to save changes? How to get rid of that? I tried:
Workbooks.Open (Filename:=FieldSheet, ReadOnly:=True)
FieldSheet.Close SaveChanges:=False
FieldSheet = Dir
But it keeps prompting me if I want to save changes to each and every worksheet. Please be informed that the workbooks I want to copy date from are *.xlsm workbooks. As a test, I created a dummy folder with some dummy workbook1.xlsx, workbook2.xlsx and workbook3.xlsx. No prompt. Then I added workbook1.xlsm, workbook5.xlsm and workbook6.xlsm. No prompt. So the issue is with the actual macro enables sheets I want to take data from. How to solve that?
Regards,
Hi Zubair! It's difficult to say for certain without seeing the files you're working with and the code you've written. It's possible that the xlsm files you're closing have code in the BeforeClose event of the workbook which makes changes to the file which triggers the prompt asking you to save.
how to send SMS via Excel VBA?
Hi Youssef, I don't know the answer to this but here are some links that might help you!
www.excelforum.com/excel-programming-vba-macros/1247391-vba-to-send-sms-to-mobile-number.html
stackoverflow.com/questions/48922232/send-sms-from-excel-vba
@@WiseOwlTutorials Thank you, sir, for your quick response.
Hi Andrew Sir Thanks for making Informative training session. I Really enjoy and very eagerly to wait next training session. Sir I request you that how to send Bulk Messages using What’s up to Excel Sheet Using VBA.
Thanks Pankaj! I don't have any experience with sending bulk messages so I can't help with that one, sorry!
Actually this code is working for the first time as I close the excel file and open it again the code did not work please help 😓😓
Hi! When you say it did not work, what do you mean? What happened?
If possible can you start microsoft access vba tutorial will be helpful to many
Thank you for the suggestion Panner. I've had Access on my ToDo list for a long time but it's quite far down the list of priorities. We don't get much demand for Access training courses at Wise Owl these days - the last time I taught an Access course was in May 2016!
Hi Sir, I hope you are doing great ! Sir, I would request you to create video on filter in vba
I am doing great, thanks! I hope you are too! Thank you for the suggestion, I'll add this one to my list.
@@WiseOwlTutorials Thank you Sir ! Thanks for your prompt response ! You are true Guru 🙏🙏🙏🙏🙏🙏🙏🙏
@@jessipinkman7659 No problem, thank you for watching and for your comments!