VBA to BROWSE & COPY Data from SELECTED File in Excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/vba-select-file

[@ronvds4713]

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.

[@LeilaGharani]

I'm glad I can still surprise you :)

[@Matt-zp9jg]

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!

[@danieljosiahquijano5659]

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.

[@Matt-zp9jg]

@@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

[@johnhayse7147]

@@danieljosiahquijano5659 Did you figure it out? I'm have the same problem :/

[@danieljosiahquijano5659]

@@johnhayse7147 I did, when you are recording a macro, under "Store macro in", select dropdown "This Workbook". it is a very specific code.

[@johnhayse7147]

@@danieljosiahquijano5659 Thanks!

[@djdisasterjames]

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!

[@LeilaGharani]

I'm glad the tutorials are helpful for you James.

[@merbouni]

Well, in less than a year, you've become the number one as reference ... Everything is useful in your KZbin channel..😍

[@LeilaGharani]

Thank you, that makes me very happy :)

[@Fatfingertunes]

Thanks! Always very helpful!

[@shabbirkanchwala-abwaab6263]

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!!!

[@lephterisp]

A whole new(amazing) world hidden behide Excel .Learning VBA is the next target for me.Thanks again for all you give to us .

[@MrKallemyran]

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" 💡

[@LeilaGharani]

I know exactly what you mean Thomas. Been in the corporate world myself for quite a while trying to navigate around the IT guidelines :)

[@MrKallemyran]

@@LeilaGharani ha ha Don't say it so loud ;) anyway... I have enrolled.. Now I have to find some time ⏳

[@mellowkoopa]

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.

[@LeilaGharani]

You're very welcome. I'm glad to have you here.

[@andyoverington712]

Thank you. Finally someone who explains this stuff very well?

[@coffeecuppepsi]

If someone doesn't like spreadsheets its just that they haven't seen this channel

[@ckokse]

So true!

[@wuphysics87]

Or because spreadsheets are the wrong tool for the job most of the time

[@suresha2622]

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).

[@craiggibson8547]

I’m doing the VBA course at the 😆moment. Very impressive, well structured and well explained stuff. Great job Leila 👍

[@LeilaGharani]

That's great Craig. I'm glad you like the course. Many thanks for the feedback!

[@Thurmanatr16]

Never a dull video! 🙂Thank you for sharing your expertise with Analysts across the Corporate world. 😃

[@LeilaGharani]

I'm glad if my videos are helpful in the corporate world :)

[@buradagunta1]

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

[@shrutiagarwal936]

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.

[@vishalpatil4102]

Thank you for the valuable information, i have purchased all of your courses from udemy. They are so very helpful

[@Poland805]

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.

[@matthewmusselwhite591]

You are truly amazing and a very gifted teacher. Thank you for all you do!!

[@olibolivar7223]

I really learn new things every time I watch your videos. Thanks a lot! :D

[@leonallen6159]

I’m getting into excel and love this stuff. Thank you.

[@LeilaGharani]

Glad you enjoy it!

[@Jihad-f3e]

Very nice explanation, bless your efforts

[@NikunjDM]

हर बार कुछ नया ,धन्यवाद लीला

[@LeilaGharani]

I'm trying my best :)

[@dhruvakerkar6956]

Wow I was actually using "Path" option in excel all this long...this really help...Thanks

[@alexpspa60]

amazing!!! do you have a video on how to import multiple files?! 😊

[@ruteshrudra6320]

Thanks for this video and it is really helpful but how to create data should select and copy data dynamic

[@twgrisham]

Well done. Very easy to follow and understand. How would handle a dynamic range in OpenBook?

[@ExcelExciting]

Great Video 👍🏻.. thanks for sharing

[@LeilaGharani]

Thanks for the lightning fast comment Faraz :)

[@arifhk007]

I like your videos from other youtube videos...Coz your teaching style is very helpful and clear! Thank you, Mam!

[@LeilaGharani]

I'm very glad to hear that :)

[@rodrigodavalle5738]

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?

[@alexofrhodes]

Try using: Range(cell("address")

[@gutobrasiliabrasil]

Great!!! It is exactly what I was looking for!!! You are the best!!!

[@lifeinfluenceramitkachalia]

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.

[@GrahamSchroder]

Your channel is brilliant! I learn so much. Thanks!

[@LeilaGharani]

Happy to hear that!

[@smohanv]

This is a kind of VBA idea i was searching for. Thanks. I have subscribed to this channel as well. Thanks a million.

[@LeilaGharani]

Glad to help. It's great to have you here.

[@mhmdseadawy8228]

Thanks for this useful lesson, you are the best 👏👏

[@thomasfetter]

you are just awesome

[@Things-Recycleing]

Thanks for usual support in Excel every day learn new topic

[@civilworks3591]

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.

[@arere9948]

TYSM for such an informative instruction, help me a lot 🥰

[@vengamanaidu996]

You are my teacher madam, i learned lot from you

[@francoismarsau6412]

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.

[@kurtauerbach5883]

Thanks this really helped me out. Clear and not boring!

[@vaishalikhedlekar3733]

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.

[@prakharsrivastava7795]

Thank you so much!! Extremely helpful video!!

[@badreddineessahi6457]

Hi there, thank you for the video, I did exactly what you explained but I'm getting an error 9 saying index out of range, could you please help with a workaround for this error

[@antoniomusso1608]

All you have to do is change the code from: Worksheets(“SelectFile”) to: Sheets(1)

[@samirmohapatra8582]

Very useful ❤. Thank you so much 😊

[@FanJapanStudios]

Best 👏 Channel 👏 EVER!!! 👏

[@hddevani]

As always, excellent Top-Notch Pure explanation...

[@wayneedmondson1065]

Hi Leila. Great tutorial! Thanks for sharing this :)) Thumbs up!!

[@LeilaGharani]

Thank you! Cheers!

[@michelacosta9886]

Very good Leila!!! than ks for this video!!!

[@jonathanjesusdelvillarrebo7429]

Hello good night your video is very well explained, I just wanted to ask a question: How can I make a filter inside the files that opened and copy the data from the filter result?

[@spunjbom]

Awesome job, Leila! I can see myself using this in the future.

[@LeilaGharani]

That's good to hear Bobby.

[@stephanie_ong]

Thanks! I was able to use your method perfectly :)

[@anomolyreport]

And here I thought openfiledialog and filesystem objects are the only ways to get file names.. Thank you!!

[@mc24bk]

Great video, very easy to understand. How to copy the entire column A & B without setting a range number?

[@robinvermeulen8710]

I get a Runtime Error 9: "Subscript out of range" error when I try this. This is my line of code that includes the error: "ThisWorkbook.Worksheets("SelectFile").Range("F4:H43").PasteSpecial xlPasteValues". I know the error is generated because I refer to a sheet that 'doesn't exist', but my code is exactly as in the video I think... What am I missing here?

[@matteopeguri3294]

Hi Robin, I also have the same problem, did you understand the problem?

[@stevel7888]

Hey, you need to change the worksheet name from "SelectFile" to whatever the name of your worksheet is where you want to data to be copied to. For example -ThisWorkbook.Worksheets("My Data").Range("F4:H43").PasteSpecial xlPasteValues"

[@robinvermeulen8710]

@@stevel7888 I know, someone else said this also, but thanks anyway!

[@robinvermeulen8710]

@@matteopeguri3294 yes, I did! I needed to change the name of the sheet I am referring to. In my code I have "SelectFile" as the sheet name. However, it's name was different. So in this position should be the name of the sheet you want to paste the values in. Good luck!

[@matteopeguri3294]

i@@stevel7888 Hi Steve, it doesn't work anyway. But now i have another error "Object not found". Do you have any solution? thx

[@Ca7a1n]

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!

[@sasavienne]

Excellent.. Thank you very much for sharing your valuable knowledge with us Leila. 👍 👏 🌟

[@LeilaGharani]

You're very welcome. Glad you like it.

[@junangeles]

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

[@totallycybertechnologies1631]

Fantastic video. Exactly what I need. After the user selects the file, is there a way for the user to select which data he/she would like to copy into cell A10?

[@swapnilsakpal2504]

Awesome video ma'am..much needed. iTs working for me. Thanks a lot. going one step ahead, I've a small huddle. I need to use index match formula within the same code, I tried but failed. Instead of defining the cells from source file. i need macro to use index match function and get the result in active sheet. Could you please assist me. Thank you so much in advance

[@abdallah.kandiel]

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 ♥

[@LeilaGharani]

First or not I'm always happy to see your comments :)

[@sachinkumbhar4097]

Thanks leila ,, but what if we want to copy the data from multiple excel

[@ramavtarsingh146]

Very useful thanks a lot, Keep it up.

[@elactecnology]

Hello Leila. Thank very much for your very useful tutorial. I tried this and it actually worked, not quite well but it did. When I try to copy a huge amount of data from another workbook before it gets passed, Excel shows a message which says "There is a large information on the clipboard ...." And I am all the time supposed to click Yes, so that Excel can copy it properly!! How would I work out?

[@EnigmaProduction]

I'm sure you have moved on past this issue. But if you add a line at the end of the code that copies a single cell after all the data has been pasted, the popup window will not appear.

[@enriqueblanco8784]

Thanks for your excel related content. I was wondering, can Google sheets open (and run) excel files containing macros (buttons, for instance)?

[@edgovan1]

Is there any way of skipping rows? For example, conditional on whether data in a column of the range in the source data has "skip" written in it. Thank you for an excellent video - subscribed.

[@sarfarajkureshi6569]

Nice tutorial mam. God bless you

[@aelina1588]

Hi Leila! Thanks so much for this video. I followed this pretty much step by step, but I'm getting a 'subscript out of range' error. I'm looking at the documentation to understand this error in this context, but I'm not comprehending. Do you have resources or tips on how to debug this?

[@augustodemelo915]

It is great!!! Thank you so much for this very valuable information.

[@jimneely100]

1st Thank You for these Videos. This video was exactly what I was looking for. Simply, I need to open a CVS file from my excel and copy the CVS file to my excel file. However, I am finding this code does not work at thisworkbook.worksheets(). I have tried every way and still get the same. It opens the CVS but instead of coping to my workbook, it creates a new workbook.

[@jimneely100]

got it fixed this is great. Again Thanks

[@viniciusfaria9355]

Jim, how did you correct the error? I'm getting the same problem

[@yulinliu850]

Great teaching of VBA. Thanks Leila!

[@LeilaGharani]

You're very welcome. Glad you like the tutorial.

[@vinaynimje97]

I m following your channel since long. I have learnt a lot. Thank U . I have one question .. i want to update data if condition met i.e. ID, If ID Exists data should be updated but if ID doesn't exist the data should be save in last row of database. Kindly help.

[@spneerajmishra]

Great help thank you 😊

[@hadiasghari2266]

u r my excel hero

[@smith28c]

Hi Leila, thanks for the great video's I generally watch all of them, I have a small problem, I get Excel files from different members of which I open and then import into my Excel data, the problem is they have a verity of extensions such .xls, .xlsx,.xlsm is there a way to be able to use with an open workbook, your comments would be much appreciated Regards..Don.

[@JuzSayWooo]

Thank you so much! I literally learn VBA from you and I have no background before this. Can I ask, what does the “title” argument actually do? I don’t get that part...

[@vpt262]

Thank you so much for this video. How can I adopt this script for importing a complete csv file and pasting it in a specific cell?

[@Time_utilize]

Please could you provide this vba workbook post course?

[@johnborg5419]

Wish I could do that :) :) I am enrolling to your course for sure.

[@tha2irtalib343]

beautiful mind , and good teacher , thank you leila .

[@craiggibson8547]

@tha2ir Talib beautiful teacher, and good mind, thank you leila .

[@rahulbakshi285]

Thanks Leila, I got your VBA videos.

[@LeilaGharani]

That's great Rahul. Thanks for your support.

[@JG-fg1ye]

excel-lent as always, Leila is stunning 👍🏻

[@ImranKhattak-me1lw]

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

[@ashwind3966]

Hi, Thanks it is really useful. But I am getting message box saying"There is a large amount of information on the clipboard. Do you want to paste this information....... " How to escape this message and run the macro smoothly.

[@AdrenalinnVapor]

Add this line after the paste line "Application.CutCopyMode = 0"

[@gosasan]

@@AdrenalinnVapor Thank you for that! :)

[@farizpatah4081]

Finally got this macro.. Thanks a lot...

[@LeilaGharani]

Glad to help :)

[@jayeshjamtani924]

Thank you #LeilaGharani #ExcelQueen for sharing this wonderful way to open files. I am a big fan of yours ( more than of Excel )

[@LeilaGharani]

I'm happy to hear that :)

[@everythingandanything4102]

Hi Leila, great vids as always, however, i have a question, I am getting a run time error 9, 'subscript out of range' on line 9 ThisWorkbook.worksheets("Selectfile").range("A10").pastespecial xlpastevalues what could be the problem?

[@ericw.3250]

Hi, you need to replace the name "SelectFile" by the name of the Sheet you want to paste the data in

[@leasairanen6603]

Hello Leila, thanks for this video. I wonder, could you help me? I want to learn how to move information from one excel to another. I have data in one excel and want to move it to another; in first empty cell. How to do it?

[@LeilaGharani]

The easiest would probably be Power Query. You could have a look at this one for inspiration: kzbin.info/www/bejne/nHmphptses11mdk

[@ashrafgharsan553]

Very useful and practical. Thank you for the great material!

[@shaheershujaat2223]

Wow Leila you are looking very nice beautiful and amazing video well done 😊😊❤️❤️👍👍

[@LeilaGharani]

Thank you, glad you like it :)

[@shaheershujaat2223]

My pleasure Leila yup a lot 😊😊❤️❤️👍👍

[@Interphaseable]

Wonderful video!

[@jeandaniel521]

Amazing, this saved me so much time :)

[@LeilaGharani]

Great to hear!

[@nevermind4780]

another creative ideas ☺👍👌

[@youzaist]

You are the best ever.... life saver oh my gosh

[@samirawati1975]

Wouderful video madam, can you please give this file for practice in description

[@gummadinagalakshmi4083]

Thanks it's help me somehow.but I am seeing for same sheet I need to count the value having NA and seperate the count to another sheet.can you advise.. Thanks 😊

[@briangilbride]

is there a video that details how to edit this further? I'm looking to open a file, filter a pivot table in that file, copy the filtered data, then paste that into the original workbook

[@ianh1998]

Hi Leila, Love your channel and the quality of your presentations and lectures, I always learn something new. Could you possibly help Mac users, like myself, by quoting shortcut keys for both Windows and MacOS? Also, the specific file selection/access techniques you refer to in this video don't work on MacOS Catalina due to Apple's sandboxed architecture. Could you suggest an alternative approach that would be platform independent? Thanks in advance...

[@LeilaGharani]

Sorry Ian. I don't have any Mac in my office.

[@joecross5335]

Helpful. I didn't know that method existed.