To control the order of the sheets after sheet duplication: Try changing this line of code: Sheets("Test").Copy After:=Sheets(2) To this: Sheets("Test").Copy After:=Sheets(Worksheets.Count - 1) Or this: Sheets("Test").Copy After:=Sheets(Worksheets.Count) Worksheets.Count tells us the number of sheets in the workbook. By using Worksheets.Count in the brackets, we are telling Excel to copy the sheet in after the last sheet in the workbook. See this video for more on the topic: th-cam.com/video/tfcrIa3AR0w/w-d-xo.html
Hi Chris, I just started the first lesson for post-beginners today and I'm very happy to continue your lessons, that every time make me discover more and more interesting things about VBA. Congratulations again for your excellent teaching method and how you have structured this great course! Thanks again and see you soon.
Really enjoying these lessons - very helpful and clear. As an engineer, I often do a lot of work with spreadsheets and I have for a long time wanted to learn VBA. These videos are the start of that journey for me.
Tom - great to hear and I think you are thinking about it the right way, as a learning 'journey'. Good luck and I hope you'll keep coming back to the channel as you improve.
Hi Christ I am so glad I began my VBA journey with you. I enjoyed your lessons and for the very first time regaining my faith in learning VBA programming. Excellent teaching !!!
I used this method for printing every sheets of my excel file and it was easier by clicking one single button to print out multiple sheets at once! Thank you for this video 👏
I am so happy that I found your tutorials. They are very information and easy to follow. I know that Excel can do what I want it to do but I will have to teach myself VBA first. Wish me luck with my monster spreadsheet!!
Awesome Chris! So incredibly helpful and easy to understand. I really enjoy your delivery, you get right to the point and break it down to its simplest concept. It really makes it digestible to a beginner like myself. Thank you!
Very impressed Chris. Your explanations and teaching style make this easy to understand and memorise what is a very complex part of Excel. I'll only be using Tiger Excel for VBA tution (and all aspects of Excel). 10/10
Why is it that once I added the button when I click it to duplicate the sheet, the button is on the duplicated sheet, but it doesn't appear to be on yours. (This is on Dep 4)? Thanks in advance! I'm trying to learn VBA. We need to do something just like this for work!
Hey, Jenny. The most likely reason you are seeing the button in all copies, is that you probably added the button in on the first sheet which is the source for the copies. In the video, he places the button in one of the copy sheets, not on the first. So, when it drew from the source in his video, it drew from a sheet without the button. If you placed it on the first sheet in your workbook, the copies are being drawn to replicate that entire sheet including the newly added button. Hope this is relevant!
Hello Tiger, I like your videos. I am having trouble following you sometimes though. I downloaded the random data ss, but I don't see Dep instead of Sheet 1. Thanks for your help!
Hi Orrinette - thank you. I have just checked the download file for the random data video and it does appear to be the correct file. Send me an email if you're still having problems - email address on the website.
Hey Chris! Thanks a lot for your awesome videos. I just started learning VBA today and decided to go all the way with your instructions. I have a question on this video though: How come each time you press the button, the second sheet does not get replaced? Because usually I expect a loop to be in there and change the sheet number! Many thanks.
Hessam - we cannot change the sheet index number (only by re-ordering the sheets in the file). This is how the mechanism works because it excludes sheets using the index number.
Hi Chris, I have one question. Everyday i open near about 5-6 excel workbooks which ultimately has 5-8 different worksheets in each workbook. What i do is, everyday i open those one by one and copy the data in Master workbook. How do i automate this? The data is completely different in all worksheets so i am just confused. Thanks for your prompt attention in advance.
I like your presentation. You did a step by step. I will keep following your videos as I need to do some routine at work. It's basically I want our SAP data base management system(list of parts withe pricing) to generate an excel file. Do you have this topic. Again thank you.
Hi Arnel. Thank you! No, I don't currently have a video about that particular situation, but the VBA for beginners playlist will definitely help you in a more general sense. Good luck!
In my macro code, there was an extra line... what does it mean? Sub DublicateSheet() ' ' DublicateSheet Macro ' ' Sheets("Sheet1").Select Application.CutCopyMode = False Sheets("Sheet1").Copy After:=Sheets(2) End Sub " Application.CutCopyMode = False" why this line appear? thanks
Hi Mohammed - good question. This line of code exits cut / copy mode. It means that Excel clears the clipboard of anything that has been cut or copied. It also means that the dashed lines around anything that you have cut / copied disappear too.
Please help me. I followed the steps and it already appears here "stop recording" and then when I tried to move or copy and clicking ok, "This will close the workbook without saving. Some macro modules will be lost. Do you wan to move the selected sheets anyway?" will pop up. I don't know what to do.
First of all, thank you and admire your work. I have a question. When I run the macro is copying the button as well. How could I fix that ? I want it copied without the button. The code is the same, but...
Try changing the settings on the button. If you right click on the button, somewhere you can find an option 'don't move or copy with sheets' - this should mean the button does not copy over. Try Googling it for precise instructions - good luck!
Hello Tiger, I really like your way of teaching. Thumbs up! I am new learner of VBA, i have command on excel and some know how about macros(through recording) but knows nothing about VBA language. Can you please guide should i keep watching your videos to learn execution of macros through VBA or i should learn VBA first?? Thank you!!
Abid - great question! I think the only way to learn VBA is by trying to do VBA. I have received similar queries in the past - asking for a list or a glossary of VBA terms. Such things are available on line, but for me they should be used for reference only, and not for guiding learning. VBA is a skill and you can only improve by trying to do it. You could read many books about basketball, watch all the games, study the great players; ultimately, it would not really improve your game. You can only improve your game by practising on the court. I recommend the same approach to computer programming. You'll make a ton of mistakes as you go along, but these should be accepted and indeed embraced as part of the learning process. I still make mistakes today as you can see in the videos - I include all the mistakes (I do not really 'edit' the videos) so that viewers can appreciate the importance of mistakes in the learning process. I hope this helps and good luck.
Try changing this line of code: Sheets("Test").Copy After:=Sheets(2) To this: Sheets("Test").Copy After:=Sheets(Worksheets.Count - 1) Or this: Sheets("Test").Copy After:=Sheets(Worksheets.Count) Worksheets.Count tells us the number of sheets in the workbook. By using Worksheets.Count in the brackets, we are telling Excel to copy the sheet in after the last sheet in the workbook.
In duplicate sheets(video 2/6) when you move or copy for a duplicate sheets in the Move or Copy box its showing (Macro to duplicate sheets with Code.xlsm) but when I Move or copy its just the simple make a duplicate copy....Please clear this ....Thanks
let's say the workbook 1 contains the grade 8 max recode and workbook 2 contains the grade 11 max record, in this want is possible to be running the marco on the workbook 1 in workbook 2 , thank you for your time sir...
Hi there - it's possible for a macro to call on different workbooks. We have a series called 'How To Work With Other Workbooks In Excel VBA', or similar - check out that series ...
Hi Chris When i write the code i get this code Sub copysheet() Sheets("Sheet1").Select Application.CutCopyMode = False Sheets("Sheet1").Copy After:=Sheets(1) End Sub Can I skip this part -- Application.CutCopyMode = False
Hi Chris , since i am beginner i don’t understand Application.CutCopyMode = False . Without that also my code works. But since i record the steps i get this step. What does this means ? Application.CutCopyMode = False
Hi there - it's an interesting idea for the future. Are you working on something in particular? You can email me via the website if you like. Good luck with the VBA!
Every time I attempt to duplicate a sheet using your practice workbook, I get an "Invalid Name Error" in the Days Service column.....it errors out on the duplicate and the original sheet, what could be causing this? Thanks, I enjoy your videos!~
Matthew Wright Hi Matthew - glad to hear you like the videos! Hhmm, I would compare your code to the code I provide, and look for differences. It sounds like you may have deleted the value in the cell that the Days Service formula refers to. If all else fails, send the file to me via the contact page on the website. Good luck!
love your vids mate! So pretending one of my sheets I was duplicating was named 'Data1', to name the sheet specifically instead of a numerical reference, would the necessary code have looked like this ... .sheets("Data1")
Yes - that should work. So you have two main referencing techniques - one using a name and the other an index number, both useful in specific situations. Plenty more vids to work through!
Hi Chris thnx for this videos, Could you do please a video on how do we can pass an a variable throught macros when its trigerred by a button. means different buttons triggers the same function but with different argument each time thanks
+Mehmetcan Muslu Good question. Try changing this line of code: Sheets("Test").Copy After:=Sheets(2) To this: Sheets("Test").Copy After:=Sheets(Worksheets.Count - 1) Or this: Sheets("Test").Copy After:=Sheets(Worksheets.Count) Worksheets.Count tells us the number of sheets in the workbook. By using Worksheets.Count in the brackets, we are telling Excel to copy the sheet in after the last sheet in the workbook.
So do you work for tiger? Just joking. Good videos, but I wish you explained it from more of a programming perspective, but that's just because I have other programming experience. excel using instead of != is ridiculous BTW.
Some constructive criticism: We actually don't need to see you so to have your frame so big and in front of the excel window is really annoying. And things in excel can be hard to see in videos so to have that window so small and in back is also very frustrating.
To control the order of the sheets after sheet duplication:
Try changing this line of code:
Sheets("Test").Copy After:=Sheets(2)
To this:
Sheets("Test").Copy After:=Sheets(Worksheets.Count - 1)
Or this:
Sheets("Test").Copy After:=Sheets(Worksheets.Count)
Worksheets.Count tells us the number of sheets in the workbook. By using Worksheets.Count in the brackets, we are telling Excel to copy the sheet in after the last sheet in the workbook.
See this video for more on the topic:
th-cam.com/video/tfcrIa3AR0w/w-d-xo.html
thanks
Hi Chris, I just started the first lesson for post-beginners today and I'm very happy to continue your lessons, that every time make me discover more and more interesting things about VBA. Congratulations again for your excellent teaching method and how you have structured this great course! Thanks again and see you soon.
Many thanks Mauro - keep it up!
Really enjoying these lessons - very helpful and clear. As an engineer, I often do a lot of work with spreadsheets and I have for a long time wanted to learn VBA. These videos are the start of that journey for me.
Tom - great to hear and I think you are thinking about it the right way, as a learning 'journey'. Good luck and I hope you'll keep coming back to the channel as you improve.
Chris, thank you for your efforts to help us, the public, to use and understand the potential of VBA. You are a TIGER.
Harold - thank you so much! I hope you've felt the power and potential yourself ...
I can't believe you created this whole series! I can't wait to see what I will learn! Thanks again Chris, your awesome!
Thanks Brian - plenty more series on the channel after this one!
Hi Christ I am so glad I began my VBA journey with you. I enjoyed your lessons and for the very first time regaining my faith in learning VBA programming. Excellent teaching !!!
Hello - good to hear your faith in VBA has been restored. The effort is worth it - good luck!
Thank you, these tutorials are a lifesaver. The materiel provided by my school isn't very thorough so I'd be lost without this.
Good to hear :-)
I used this method for printing every sheets of my excel file and it was easier by clicking one single button to print out multiple sheets at once! Thank you for this video 👏
Aziz - it's a pleasure and good to hear that the video helped you. Let me know what else you would like to see on the channel.
@@TigerSpreadsheetSolutions will do. You are my idol 😍
@@azizazhar Aziz - very kind of you! Do check out the other videos on the channel.
I am so happy that I found your tutorials. They are very information and easy to follow. I know that Excel can do what I want it to do but I will have to teach myself VBA first. Wish me luck with my monster spreadsheet!!
Shelley - thanks for the comment and good luck with the spreadsheet! Let us know how it goes...
it's amazing how you make it seem so simple to a seemingly overwhelming VBA coding
Many thanks Everly - with the right approach it is possible to make sense of it!
Great course. I am new using VBA language and your teaching techniques are indeed very helpful. Thanks a lot. Keep posting videos like these, please.
Many thanks Augusta - plenty more videos to come!
The great teacher I've ever seen on TH-cam. Hope there will have more lessons from you.
Thank you - plenty more videos on the channel!
This learning package actually gave me a great start in learning MS Excel VBA......Thank you very Much
Awesome Chris! So incredibly helpful and easy to understand. I really enjoy your delivery, you get right to the point and break it down to its simplest concept. It really makes it digestible to a beginner like myself. Thank you!
Very good. Very pleasant voice and personality - just right for instruction!
Many thanks! Appreciate the comment.
Very impressed Chris. Your explanations and teaching style make this easy to understand and memorise what is a very complex part of Excel.
I'll only be using Tiger Excel for VBA tution (and all aspects of Excel). 10/10
Thank you very much - see you in another video on the channel!
You are an outstanding teacher. It is so nice to follow your courses. Thank you very much - I really appreciate to watch the movies.
Henrik - good to hear, plenty more videos to come so I hope you'll keep following the channel
Appreciate all your time to put these together - Very Helpful!
Appreciate the comment! Please do share with others.
This is exactly what I needed, thank you
Thanks Nicole - plenty more VBA videos on the 'VBA for Beginners' playlist, please do share the videos if you think they're helpful! Good luck.
Why is it that once I added the button when I click it to duplicate the sheet, the button is on the duplicated sheet, but it doesn't appear to be on yours. (This is on Dep 4)? Thanks in advance! I'm trying to learn VBA. We need to do something just like this for work!
Hey, Jenny. The most likely reason you are seeing the button in all copies, is that you probably added the button in on the first sheet which is the source for the copies. In the video, he places the button in one of the copy sheets, not on the first. So, when it drew from the source in his video, it drew from a sheet without the button. If you placed it on the first sheet in your workbook, the copies are being drawn to replicate that entire sheet including the newly added button. Hope this is relevant!
I'm binge watching this channel during lockdown rather than binge watching Netflix
Could there be a better way to spend lockdown? :-)
You are Awesome!! You are a great instructor. I love your videos. Thank you so much for posting.
Patricia - it's a pleasure and thank you for the comment! Do check out the other video series on the channel.
Fantastic tutorials!!! Thank you very much for creating these videos.
Thanks Jay! Please do share them with somebody else whom you think might benefit...
Hello Tiger, I like your videos. I am having trouble following you sometimes though. I downloaded the random data ss, but I don't see Dep instead of Sheet 1. Thanks for your help!
Hi Orrinette - thank you.
I have just checked the download file for the random data video and it does appear to be the correct file. Send me an email if you're still having problems - email address on the website.
Hi Christ, what should the code be like if I want to add each new sheet to the very end of all sheets?
Hi Edward - use activeworkbook.sheets.count where you specify the sheet number to copy after. Good luck!
Thank you! Your videos are really helpful.
Thanks Hannah! Please do share the videos if you like them. Good luck!
Hey Chris! Thanks a lot for your awesome videos. I just started learning VBA today and decided to go all the way with your instructions. I have a question on this video though: How come each time you press the button, the second sheet does not get replaced? Because usually I expect a loop to be in there and change the sheet number! Many thanks.
Hessam - we cannot change the sheet index number (only by re-ordering the sheets in the file). This is how the mechanism works because it excludes sheets using the index number.
Really a perfect teacher ......
Hi Chris, I have one question. Everyday i open near about 5-6 excel workbooks which ultimately has 5-8 different worksheets in each workbook. What i do is, everyday i open those one by one and copy the data in Master workbook. How do i automate this? The data is completely different in all worksheets so i am just confused. Thanks for your prompt attention in advance.
Hi there - check out some of our videos on working with multiple files, we have just covered this topic in Members' Monday too. Good luck.
I like your presentation. You did a step by step. I will keep following your videos as I need to do some routine at work. It's basically I want our SAP data base management system(list of parts withe pricing) to generate an excel file. Do you have this topic. Again thank you.
Hi Arnel. Thank you! No, I don't currently have a video about that particular situation, but the VBA for beginners playlist will definitely help you in a more general sense. Good luck!
In my macro code, there was an extra line... what does it mean?
Sub DublicateSheet()
'
' DublicateSheet Macro
'
'
Sheets("Sheet1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Copy After:=Sheets(2)
End Sub
" Application.CutCopyMode = False" why this line appear?
thanks
Hi Mohammed - good question. This line of code exits cut / copy mode. It means that Excel clears the clipboard of anything that has been cut or copied. It also means that the dashed lines around anything that you have cut / copied disappear too.
Please help me. I followed the steps and it already appears here "stop recording" and then when I tried to move or copy and clicking ok, "This will close the workbook without saving. Some macro modules will be lost. Do you wan to move the selected sheets anyway?" will pop up. I don't know what to do.
Hhhmmm, I am not sure. Make sure the workbook is saved as a macro-enabled workbook.
First of all, thank you and admire your work. I have a question. When I run the macro is copying the button as well. How could I fix that ? I want it copied without the button. The code is the same, but...
got it :)
great news when people fix their own problems, well done
I would love to learn Excel VBA with you.. thanks
My duplicated sheets have the macro buttons too . How to make sure that just the original sheet has the macro button?
Try changing the settings on the button. If you right click on the button, somewhere you can find an option 'don't move or copy with sheets' - this should mean the button does not copy over. Try Googling it for precise instructions - good luck!
Hello Tiger,
I really like your way of teaching. Thumbs up!
I am new learner of VBA, i have command on excel and some know how about macros(through recording) but knows nothing about VBA language.
Can you please guide should i keep watching your videos to learn execution of macros through VBA or i should learn VBA first??
Thank you!!
Abid - great question!
I think the only way to learn VBA is by trying to do VBA.
I have received similar queries in the past - asking for a list or a glossary of VBA terms. Such things are available on line, but for me they should be used for reference only, and not for guiding learning.
VBA is a skill and you can only improve by trying to do it.
You could read many books about basketball, watch all the games, study the great players; ultimately, it would not really improve your game. You can only improve your game by practising on the court.
I recommend the same approach to computer programming. You'll make a ton of mistakes as you go along, but these should be accepted and indeed embraced as part of the learning process. I still make mistakes today as you can see in the videos - I include all the mistakes (I do not really 'edit' the videos) so that viewers can appreciate the importance of mistakes in the learning process.
I hope this helps and good luck.
Thank you for your feedback, i will get back to you once i tried things you mentioned.
hi sir , your teaching skills is awesome .
+ravi yadav thank you Ravi - hope the videos help you
yes sir...thank you ..!
Thank you much Chris, really appreciable. How about doing the other way, that is deleting the sheets?
Hi there - we do cover this too, later in the series, enjoy!
Hi how would manipulate the code so that the new sheet would be added to the end each time instead of a position ?
Try changing this line of code:
Sheets("Test").Copy After:=Sheets(2)
To this:
Sheets("Test").Copy After:=Sheets(Worksheets.Count - 1)
Or this:
Sheets("Test").Copy After:=Sheets(Worksheets.Count)
Worksheets.Count tells us the number of sheets in the workbook. By using Worksheets.Count in the brackets, we are telling Excel to copy the sheet in after the last sheet in the workbook.
In duplicate sheets(video 2/6) when you move or copy for a duplicate sheets in the Move or Copy box its showing (Macro to duplicate sheets with Code.xlsm) but when I Move or copy its just the simple make a duplicate copy....Please clear this ....Thanks
Quick question, what happens if a module isn't being created once I stop recording the macro?
Ace - is the file saved as a macro-enabled workbook?
very helpful. Big thanks!
let's say the workbook 1 contains the grade 8 max recode and workbook 2 contains the grade 11 max record, in this want is possible to be running the marco on the workbook 1 in workbook 2 , thank you for your time sir...
Hi there - it's possible for a macro to call on different workbooks. We have a series called 'How To Work With Other Workbooks In Excel VBA', or similar - check out that series ...
Hi Chris
When i write the code i get this code
Sub copysheet()
Sheets("Sheet1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Copy After:=Sheets(1)
End Sub
Can I skip this part -- Application.CutCopyMode = False
Looks fine to me Deepali. Is the name of the sheet ("Sheet1") the problem?
Hi Chris , since i am beginner i don’t understand Application.CutCopyMode = False . Without that also my code works. But since i record the steps i get this step.
What does this means ? Application.CutCopyMode = False
Great great videos!!!!
Thanks Eric!
Hi your videos is awesome, I'm learning a lot. can you create a tutorial for Inventory Management using VBA?
Hi there - it's an interesting idea for the future. Are you working on something in particular? You can email me via the website if you like. Good luck with the VBA!
Every time I attempt to duplicate a sheet using your practice workbook, I get an "Invalid Name Error" in the Days Service column.....it errors out on the duplicate and the original sheet, what could be causing this? Thanks, I enjoy your videos!~
Matthew Wright Hi Matthew - glad to hear you like the videos! Hhmm, I would compare your code to the code I provide, and look for differences. It sounds like you may have deleted the value in the cell that the Days Service formula refers to. If all else fails, send the file to me via the contact page on the website. Good luck!
love your vids mate!
So pretending one of my sheets I was duplicating was named 'Data1', to name the sheet specifically instead of a numerical reference, would the necessary code have looked like this ... .sheets("Data1")
Yes - that should work. So you have two main referencing techniques - one using a name and the other an index number, both useful in specific situations. Plenty more vids to work through!
Hi Chris thnx for this videos, Could you do please a video on how do we can pass an a variable throught macros when its trigerred by a button. means different buttons triggers the same function but with different argument each time thanks
THANK YOU SO MUCH BRO. YOU LOOKS LIKE JAMIE VARDY DUDE :))
You're welcome! Usually people say Harry Maguire ..
@@TigerSpreadsheetSolutions i think your LEICESTER CITY fan :))
Is it possible to increase the volume
Hi Akash - I just checked and I do think the volume level is reasonable. The audio quality should be improved in more recent videos, however.
Thank you for your wonderful videos. This is my start from scratch
Good to hear Saad and very happy to have you on the channel
why did it create the sheets out of order
Hi there - I deal with that question in this follow-up video, around 7 minutes:
th-cam.com/video/tfcrIa3AR0w/w-d-xo.html
These videos are awesome
Thanks Jon - please do spread the word!
Tiger Spreadsheet Solutions witch program is the easiest for beginners?
java, C++, VBA, Python
What should i do if i wanna duplicate the sheets always to end?
+Mehmetcan Muslu Good question.
Try changing this line of code:
Sheets("Test").Copy After:=Sheets(2)
To this:
Sheets("Test").Copy After:=Sheets(Worksheets.Count - 1)
Or this:
Sheets("Test").Copy After:=Sheets(Worksheets.Count)
Worksheets.Count tells us the number of sheets in the workbook. By using Worksheets.Count in the brackets, we are telling Excel to copy the sheet in after the last sheet in the workbook.
So do you work for tiger? Just joking. Good videos, but I wish you explained it from more of a programming perspective, but that's just because I have other programming experience.
excel using instead of != is ridiculous BTW.
Interesting that my Days Service
column all changed after making the copy action to (#name?).....i wonder what is happening
Trying lining up the Windows and stepping through the code to establish when and what happened. Good luck!
Grazie!
You're welcome, Roberto!
Thanks
Awesome
Thank you - I hope you enjoy the rest of the series
I wish I was locked down in a darkroom 0:13
So smart I don't care what he looks like, and so gorgeous I don't care what he's saying, ha
Lol. You made me blush :-)
But one sheet is plenty
Some constructive criticism: We actually don't need to see you so to have your frame so big and in front of the excel window is really annoying. And things in excel can be hard to see in videos so to have that window so small and in back is also very frustrating.
Appreciate the feedback but 100k + views suggests this is not a problem for most
Thanks Chris Dean AUstralia