For those wanting their copied range to be a bit more dynamic use this. OpenBook.Sheets(1).Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy The only problem with this is it stops if there are any empty rows. So make sure to check your data! LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, StartCell.Column).End(xlUp).Row LastColumn = Sheets(1).Cells(StartCell.Row, Sheets(1).Columns.Count).End(xlToLeft).Column Sheets(1).Range(StartCell, Sheets(1).Cells(LastRow, LastColumn)).Copy This code on the other hand will work and count data even if there any empty rows between data. I'm still trying to incorporate this code into her macro to work so far no luck haha!
ThisWorkbook.Worksheets("HW15").Range("A1").PasteSpecial xlPasteAll At this part, I am encountering error, that highlights this as yellow. Can somebody help? While my colleague uses the same code, but works fine.
@@danieljosiahquijano5659 Could be many things: possible your code has not selected anything to copy. Paste will fail if nothing copied. Your range is different than your paste area. Also better to use this: high lighting entire column a even Blanks. Dim rng As Range Set rng = Range("A2", Range("A" & Rows.count.End(xlup).Address) rng.Copy Range("B2").PasteSpecial xlPasteAll
These lessons are so awesome, you really dive into every little detail and explain it so clearly :) Dim as String --> False = "False". Dim as Variant --> False = False :D I was baffled by that once, no doubt others also benefited from this!
I am trying to find a VBA code that will find a file utilizing where only the first six characters in the file name are always same and the file name ends with YYYY.MM.DD.xlsx. now my file is under 2020 folder and have to create seprate folder for each monts like Jan, Feb and Mar under 2020 folder, then in the each month file we have to change and create file on daily basis like this _ Y:\C & C FEES\Suresh\anbu 2020\04_Apr 2020\InvoicesDD.MM.YY(daily need to change the file date and monthly once change the month).
This is something I'll actually use so thank you. And also You strike me as very pedagogical so I'll have a look-see if your courses are something i can take parallel to my work.. Fascinated by excels power. Mostly becaus it lets me "program" some simple things without the need to upgrade my IT security clearance in the organisation. As long as I'm within excel boarders. I'm allowed to build "smartnes" 💡
This came up in my Google news feed, like sweet nectar of information magic'd down from the heavens to solve a problem I didn't know I had. Thank you so much for your very clear and concise tutorial. Subscribed.
Happy Thursday your highness This is not fair, you publish your jewels while me at work.😅 I can't return to my firsts💪 Anyways You are the one I'll brb after watching Thanks in advance ♥
Great video, actively using it currently. I only had one question regarding the macro: is there any way to change the default file open location to be context sensitive? For clarity: is it possible running this macro to have the default window open up to where the file is currently located in a dynamic fashion? We are trying to do this because we have many different folders with various data sets. I'm trying to think of a way to have the default window open up to the local folder, but be context sensitive. So if the excel file is moved the default dialogue box will change depending on the location of the workbook. Thanks in advance for anyone that can help.
Hi, this is very helpful. would you explain considering the csv file instead xlsx file, copy data from sheet 1 of csv file opened and paste in the current workbook. Thank you
Hi Leila. This video has really helped in one of my excel project of my process flow. I sincerely appreciate you for making this video. I would have loved to share my automation made out of this information, but, considering confidentialty, not possible. Thanks again for making and sharing. Will also go through your other videos.
Great videos as always. I have signed up for some courses of yours to learm more. You are geat teacher. you mentioned that " and copy specific ranges and import them in our file will be shown later how to do". Can you give example of this or tell me lesson no in you course that explains this as I could not find it. Thank you once again for great courses and video content.
Hi, i really like how easily and precisely you explain things. I have a query though, what should i do if i want to get data by opening a link which is a link to excel sheet and not from any worksheet which is saved in my PC.
Hi Leila and thank you very much for your useful videos! I have a problem with this code - it doesn`t work on MAC. Do you have a solution for this ? Thank you! Best regards!
In column A I have file name In column B I have source folder In column C i have destination folder Need a macro To copy files of column A From source path (columnB) To destination path (columnC)
I work in a hotel as a night Auditor. Someone managed to do his job at a fraction of a time because of time. Been trying to find out how he could import the report from our opera cloud into our excel. In a form of a format. This video gives a good example but I still need help. 😮
This is what I've been looking for for my reporting! thanks so much. How about before pasting to destination sheet, a prompt or option to select the new range will be prompted or even paste data to the next blank row below the 1st copied file, is that possible? pls. thanks
Hi Leila Gharani ! i always concern your videos when i face some issue in my macro code. perfect videos highly recommend, how can i learn all the Vba techniques quickly and efficiently please guide me . Regards
This is really great !! But would like to understand how can we apply filter on the selected file based on a certain criteria and copy only the filtered data on the macro workbook.
Grab the file I used in the video from here 👉 pages.xelplus.com/vba-select-file
Most people I deal with look at spreadsheets as a glorified calculator and are amazed at what I do for them. And then you come along and I am amazed.
I'm glad I can still surprise you :)
For those wanting their copied range to be a bit more dynamic use this.
OpenBook.Sheets(1).Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
The only problem with this is it stops if there are any empty rows. So make sure to check your data!
LastRow = Sheets(1).Cells(Sheets(1).Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = Sheets(1).Cells(StartCell.Row, Sheets(1).Columns.Count).End(xlToLeft).Column
Sheets(1).Range(StartCell, Sheets(1).Cells(LastRow, LastColumn)).Copy
This code on the other hand will work and count data even if there any empty rows between data. I'm still trying to incorporate this code into her macro to work so far no luck haha!
ThisWorkbook.Worksheets("HW15").Range("A1").PasteSpecial xlPasteAll
At this part, I am encountering error, that highlights this as yellow.
Can somebody help?
While my colleague uses the same code, but works fine.
@@danieljosiahquijano5659 Could be many things: possible your code has not selected anything to copy. Paste will fail if nothing copied. Your range is different than your paste area.
Also better to use this: high lighting entire column a even Blanks.
Dim rng As Range
Set rng = Range("A2", Range("A" & Rows.count.End(xlup).Address)
rng.Copy
Range("B2").PasteSpecial xlPasteAll
@@danieljosiahquijano5659 Did you figure it out? I'm have the same problem :/
@@johnhayse7147 I did, when you are recording a macro, under "Store macro in", select dropdown "This Workbook". it is a very specific code.
@@danieljosiahquijano5659 Thanks!
Well, in less than a year, you've become the number one as reference ... Everything is useful in your TH-cam channel..😍
Thank you, that makes me very happy :)
These lessons are so awesome, you really dive into every little detail and explain it so clearly :) Dim as String --> False = "False". Dim as Variant --> False = False :D I was baffled by that once, no doubt others also benefited from this!
I'm glad the tutorials are helpful for you James.
If someone doesn't like spreadsheets its just that they haven't seen this channel
So true!
Or because spreadsheets are the wrong tool for the job most of the time
I am trying to find a VBA code that will find a file utilizing where only the first six characters in the file name are always same and the file name ends with YYYY.MM.DD.xlsx.
now my file is under 2020 folder and have to create seprate folder for each monts like Jan, Feb and Mar under 2020 folder, then in the each month file we have to change and create file on daily basis
like this _ Y:\C & C FEES\Suresh\anbu 2020\04_Apr 2020\InvoicesDD.MM.YY(daily need to change the file date and monthly once change the month).
Very useful hints
B4 I never thought that such can be done in excel
I always gain a lots of practical knowledge from your Vdo
Simply U R Gr8!!!
This is something I'll actually use so thank you. And also You strike me as very pedagogical so I'll have a look-see if your courses are something i can take parallel to my work.. Fascinated by excels power. Mostly becaus it lets me "program" some simple things without the need to upgrade my IT security clearance in the organisation. As long as I'm within excel boarders. I'm allowed to build "smartnes" 💡
I know exactly what you mean Thomas. Been in the corporate world myself for quite a while trying to navigate around the IT guidelines :)
@@LeilaGharani ha ha Don't say it so loud ;) anyway... I have enrolled.. Now I have to find some time ⏳
Never a dull video! 🙂Thank you for sharing your expertise with Analysts across the Corporate world. 😃
I'm glad if my videos are helpful in the corporate world :)
A whole new(amazing) world hidden behide Excel .Learning VBA is the next target for me.Thanks again for all you give to us .
This came up in my Google news feed, like sweet nectar of information magic'd down from the heavens to solve a problem I didn't know I had. Thank you so much for your very clear and concise tutorial. Subscribed.
You're very welcome. I'm glad to have you here.
Thank you. Finally someone who explains this stuff very well?
I’m getting into excel and love this stuff. Thank you.
Glad you enjoy it!
Very nice explanation, bless your efforts
I really learn new things every time I watch your videos. Thanks a lot! :D
You are truly amazing and a very gifted teacher. Thank you for all you do!!
I’m doing the VBA course at the 😆moment. Very impressive, well structured and well explained stuff. Great job Leila 👍
That's great Craig. I'm glad you like the course. Many thanks for the feedback!
Thank you for the valuable information, i have purchased all of your courses from udemy. They are so very helpful
Between this and ADO....these are my two faves to impress the non-technical
TYSM for such an informative instruction, help me a lot 🥰
हर बार कुछ नया ,धन्यवाद लीला
I'm trying my best :)
Your channel is brilliant!
I learn so much. Thanks!
Happy to hear that!
Thank you so much!! Extremely helpful video!!
you are just awesome
Wow
I was actually using "Path" option in excel all this long...this really help...Thanks
I like your videos from other youtube videos...Coz your teaching style is very helpful and clear!
Thank you, Mam!
I'm very glad to hear that :)
And here I thought openfiledialog and filesystem objects are the only ways to get file names..
Thank you!!
Great!!!
It is exactly what I was looking for!!!
You are the best!!!
This is a kind of VBA idea i was searching for. Thanks. I have subscribed to this channel as well. Thanks a million.
Glad to help. It's great to have you here.
Great Video 👍🏻.. thanks for sharing
Thanks for the lightning fast comment Faraz :)
Awesome job, Leila! I can see myself using this in the future.
That's good to hear Bobby.
Thanks for usual support in Excel every day learn new topic
Very good Leila!!! than
ks for this video!!!
Hi Leila. Great tutorial! Thanks for sharing this :)) Thumbs up!!
Thank you! Cheers!
Thanks this really helped me out. Clear and not boring!
Thanks for this useful lesson, you are the best 👏👏
Very useful thanks a lot, Keep it up.
Thanks! Always very helpful!
Thank you #LeilaGharani #ExcelQueen for sharing this wonderful way to open files. I am a big fan of yours ( more than of Excel )
I'm happy to hear that :)
Happy Thursday your highness
This is not fair, you publish your jewels while me at work.😅
I can't return to my firsts💪
Anyways
You are the one
I'll brb after watching
Thanks in advance ♥
First or not I'm always happy to see your comments :)
Great video, actively using it currently. I only had one question regarding the macro: is there any way to change the default file open location to be context sensitive? For clarity: is it possible running this macro to have the default window open up to where the file is currently located in a dynamic fashion? We are trying to do this because we have many different folders with various data sets. I'm trying to think of a way to have the default window open up to the local folder, but be context sensitive. So if the excel file is moved the default dialogue box will change depending on the location of the workbook. Thanks in advance for anyone that can help.
You are my teacher madam, i learned lot from you
Very useful ❤. Thank you so much 😊
As always, excellent Top-Notch Pure explanation...
amazing!!! do you have a video on how to import multiple files?! 😊
Excellent.. Thank you very much for sharing your valuable knowledge with us Leila. 👍 👏 🌟
You're very welcome. Glad you like it.
Hi, this is very helpful. would you explain considering the csv file instead xlsx file, copy data from sheet 1 of csv file opened and paste in the current workbook. Thank you
Hi Leila. This video has really helped in one of my excel project of my process flow. I sincerely appreciate you for making this video. I would have loved to share my automation made out of this information, but, considering confidentialty, not possible. Thanks again for making and sharing. Will also go through your other videos.
Good one...btw, how can someone dislike this video ?
Thanks for this video and it is really helpful but how to create data should select and copy data dynamic
Best 👏 Channel 👏 EVER!!! 👏
Thanks! I was able to use your method perfectly :)
u r my excel hero
hi, thank you for lots of tutorials you made. appreciate if you continue or added the program to paste on the last row please. TIA.
Great teaching of VBA. Thanks Leila!
You're very welcome. Glad you like the tutorial.
this is the way to increase knowledge
Glad the tutorial is useful.
beautiful mind , and good teacher , thank you leila .
@tha2ir Talib beautiful teacher, and good mind, thank you leila .
It is great!!!
Thank you so much for this very valuable information.
Amazing, this saved me so much time :)
Great to hear!
Great help thank you 😊
Thanks Leila, I got your VBA videos.
That's great Rahul. Thanks for your support.
Great videos as always. I have signed up for some courses of yours to learm more. You are geat teacher. you mentioned that " and copy specific ranges and import them in our file will be shown later how to do". Can you give example of this or tell me lesson no in you course that explains this as I could not find it. Thank you once again for great courses and video content.
Very useful and practical. Thank you for the great material!
Finally got this macro.. Thanks a lot...
Glad to help :)
Wonderful video!
Hi, i really like how easily and precisely you explain things. I have a query though, what should i do if i want to get data by opening a link which is a link to excel sheet and not from any worksheet which is saved in my PC.
Nice tutorial mam. God bless you
Thanks a lot. it is usefully for me
Wow Leila you are looking very nice beautiful and amazing video well done 😊😊❤️❤️👍👍
Thank you, glad you like it :)
My pleasure Leila yup a lot 😊😊❤️❤️👍👍
Well done. Very easy to follow and understand. How would handle a dynamic range in OpenBook?
Helpful. I didn't know that method existed.
BIG THANKS LAILA
excel-lent as always, Leila is stunning 👍🏻
Wish I could do that :) :) I am enrolling to your course for sure.
Really good!
You rock the item. Super
Amazing! Really helpful :-)
Hi Leila and thank you very much for your useful videos!
I have a problem with this code - it doesn`t work on MAC.
Do you have a solution for this ?
Thank you!
Best regards!
Great video, very easy to understand. How to copy the entire column A & B without setting a range number?
absolutely awesome thanks. Actually just what I was looking for thanks!
My pleasure Niels. Glad to help.
Very much grateful Leila.
You're very welcome Robert!
Wow thank you mam ,it's helping me on my work Thanks Once again
My pleasure, glad to help.
You are the best ever.... life saver oh my gosh
Thanks a lot.. Awesome.. for next can you update for select file with multiple file.. Thanks
In column A I have file name
In column B I have source folder
In column C i have destination folder
Need a macro
To copy files of column A
From source path (columnB)
To destination path (columnC)
Woah! Just what I needed. Thanks! :D
I work in a hotel as a night Auditor. Someone managed to do his job at a fraction of a time because of time. Been trying to find out how he could import the report from our opera cloud into our excel. In a form of a format. This video gives a good example but I still need help. 😮
Amazing as always.
Thank you so much
Thanks mam for this useful code।❤️❤️❤️
Most welcome 😊
This is what I've been looking for for my reporting! thanks so much. How about before pasting to destination sheet, a prompt or option to select the new range will be prompted or even paste data to the next blank row below the 1st copied file, is that possible? pls. thanks
Thanks for video!
Very nice video
This is great! Thank you so much. I would like to know how should I change the code in order to paste the range in the active cell?
Try using: Range(cell("address")
Excellent! Thank you :)
Hi Leila Gharani ! i always concern your videos when i face some issue in my macro code. perfect videos highly recommend, how can i learn all the Vba techniques quickly and efficiently please guide me . Regards
This is really great !! But would like to understand how can we apply filter on the selected file based on a certain criteria and copy only the filtered data on the macro workbook.
Awesome video as always....
Thanks, nice and helpfull.
Love it, thanks. Simple and effective. Used it to automate my tax calculations for GME tendies ❤