The Ultimate Guide to Copying Data using Excel VBA

  Рет қаралды 71,605

Excel Macro Mastery

Excel Macro Mastery

Күн бұрын

Пікірлер: 111
@vpires70
@vpires70 2 жыл бұрын
The amount of knowledge shared in this vídeo for free is insane! It is sad I can only press like once!!
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks. Glad you like it so much!
@CollDott
@CollDott 2 жыл бұрын
You could use vba to generate as many accounts as you like and press like however time you need to satisfy that urge!!
@albireocygnus559
@albireocygnus559 9 ай бұрын
I'm always impressed with your crisp clear explanations and always can learn something new. Simply excellent!
@tomharrington1453
@tomharrington1453 2 жыл бұрын
I have to watch these videos in 10 minute segments. There is so much valuable information I can't take it all in. I can see direct application for all of it.
@FulvioGaggioli
@FulvioGaggioli 2 жыл бұрын
Your Excel knowledge is amasing!
@jimfitch
@jimfitch 2 жыл бұрын
Your best tutorial yet on this topic! Thanks!
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Glad you liked it Jim.
@frikduplessis8849
@frikduplessis8849 2 жыл бұрын
Paul thank you for a brilliant tutorial
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
You're welcome Frik
@rahul7rock
@rahul7rock 2 жыл бұрын
Beautifully Explained Sir🙂
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Rahul.
@adrisanme2010
@adrisanme2010 2 жыл бұрын
Thank you very much for the extremely powerful code
@adrisanme2010
@adrisanme2010 2 жыл бұрын
Extremely useful powerful code, thank you!!!
@grahamparker7729
@grahamparker7729 2 жыл бұрын
Loved this one, keep them coming!
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
You're welcome Graham.
@joaocustodio2094
@joaocustodio2094 2 жыл бұрын
Great as always. Thanks vert much, Paul.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
You're welcome
@reng7777
@reng7777 Жыл бұрын
I've been following you since long time ago, and all your videos have a great and professional content !!! I can say compare to others VBA youtubers you are the best to know more each of the concepts applied in VBA with clear examples. my hat off to you amigo!!!!
@Excelmacromastery
@Excelmacromastery Жыл бұрын
Thanks Rene
@freeliberalminds
@freeliberalminds 2 жыл бұрын
Wow! Awesome tutorial. Thanks a lot for this video tutorial for free sir Paul.Very Helpful tips from You.
@armotxa124
@armotxa124 2 жыл бұрын
Thank you Paul. Your codes are just lovely 👍 fast. I use them often. 👌
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Armo
@hadibq
@hadibq Жыл бұрын
Simply awesome!! Thanks Paul!
@rmscrisostomo
@rmscrisostomo 2 жыл бұрын
You're the best, Phill Collins. kidding, I'm really a fan of your work.
@mdbm500
@mdbm500 2 жыл бұрын
Thanks. my colleagues and I did it ineffectively, but now I know how to do it correctly.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Glad you found it useful
@neronjp9909
@neronjp9909 6 ай бұрын
Thx so much!! Great video
@giulianopierucci2903
@giulianopierucci2903 2 жыл бұрын
Really very interesting, thanks Paul
@rsnowdozer
@rsnowdozer 2 жыл бұрын
I didn't know about the ”ADO" method. It looks pretty simple. That's cool. 👍
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Glad you like it.
@francoismarsau6412
@francoismarsau6412 2 жыл бұрын
Great video. I learn more every day from this channel. In this video you mentioned that the down side to the advanced filter is it gets slow the more frequently you use it. Why is this? Is there a solution? Please assist as I love using advanced filters.
@cvdung06
@cvdung06 2 жыл бұрын
Thanks a lot, this video is stunning.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks John.
@maverickxyph239
@maverickxyph239 2 жыл бұрын
Thank you. These are lovely codes :-).
@michaelsvenson2456
@michaelsvenson2456 Жыл бұрын
I´m not a programmer in any way, just doing some stuff for my archeryclub, but this thing you do is amazing. I will try to use some of your code if I can make it to run as I like. Great videos.... 🙂
@idaeinjaw2139
@idaeinjaw2139 2 жыл бұрын
best tutorial ever.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Ida
@Suissair
@Suissair 2 жыл бұрын
Everybody learning vba should see this video.. Thank you very much!! I would like to add the filter results to a listbox and then sort my Lb(column 'x') (a-z) ?
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Suissair
@jerzykepinski
@jerzykepinski 6 ай бұрын
this was so good
@mswordexpert
@mswordexpert 2 жыл бұрын
Waaaaawwww I wish I could do something more than just a Like👍👍 You are a phenomenon.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks😀
@AdautoAraujo
@AdautoAraujo Жыл бұрын
Simply amazing!!
@MrJbasinger
@MrJbasinger 2 жыл бұрын
Nice video. I have been searching for some code that when I select a row of data in one sheet (any cell of a row) it would first clear specific cells in a second sheet and then fill it with the data of the newly selected row (like data from a table into a form). Can you help? Also, do you recommend any books or forums that I could use a reference on VB coding language.
@benkyree2272
@benkyree2272 2 жыл бұрын
Hi, thanks so much for sharing your knowledge, really useful! Can I just ask what you meant by not being able to alter and append data for advanced filter? I didn’t quite get that part. Thanks!
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Hi Ben, 1. Append - when you run it a second time it would add to the existing data. There isn't an option to do this. 2. Alter data - e.g. If you want to perform some calc before writing put the results.
@big1975E
@big1975E 2 жыл бұрын
I’ve learned so much from your videos!! One question… how would I use ADO to update existing records in another workbook and add new records from data in the active workbook in one query?
@josealvesferreira1683
@josealvesferreira1683 2 жыл бұрын
Very good.
@nadermounir8228
@nadermounir8228 2 жыл бұрын
Thank you very much for this very informative video. A quick question: why do use tempdatarow = 1 to 1. Why the row is 1 in the array parts? Minute 15 of the video ? thank u so much
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
I copy a row of the array to the temp array which is one row. Then I write the entire row to the worksheet. This runs faster than writing each cell to the worksheet.
@nadermounir8228
@nadermounir8228 2 жыл бұрын
@@Excelmacromastery what confuses me is that if tempdatarow has 1 row of the data then how come it will be able to write all the rows in the worksheets ? In other words, tempdatarow will consist of one row of data only and so it will spit out that first row of data only in the sheet and not the whole rows of data ? Isn't?
@pratipbanerjee68
@pratipbanerjee68 Жыл бұрын
Great Video. I have used the FOR loop. The data gets copied to the new sheet but the formatting of the original source is not retained. Also the cell borders are blank. How to solve these two issues pse?
@pedrovischi6208
@pedrovischi6208 2 жыл бұрын
Tks Paul to share another Great tutorial. Just one point here about the last method, SQL, the excel file, meaning the sheet to query data, shall be structured as database, in case not, errors might happen.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Yes. Must be in record format.
@aydeediaz2307
@aydeediaz2307 2 жыл бұрын
Hi! thanks for the great video! what if 1. I want to copy the data UNTIL query is refreshed or 2. ONLY if there are not blank cells and do a loop until the data es fill
@mr.write1433
@mr.write1433 Жыл бұрын
i cant stop thanking broooooo my sheet is so fast now mygooodnesss
@logic3686
@logic3686 2 жыл бұрын
Great video. I've used the ADO method before generally use the power query to pull the data from another file. Your method is probably quicker, but it might make a great video comparing the speed.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks for the feedback.
@KM-co5mx
@KM-co5mx 2 жыл бұрын
Thank You for another awesome video! I always look forward to these 🤗
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Glad you liked it.
@530MadCat
@530MadCat Жыл бұрын
Interesting... I like current region. What if I need data from a bunch of different locations? My employer is using excel to collection data points but its built for the floor personnel not in data format (row/column) . I'm currently using find/findnext to location indicator data to find the KPIs. Thoughts?
@alializadeh8195
@alializadeh8195 2 жыл бұрын
Thanks
@SimpleExcelVBA
@SimpleExcelVBA 2 жыл бұрын
ADO is my favourite method to get a data from other Excel file, CSV file or data base for a long time. The speed of this method depends of the size of the file, format/extension and of the complexity of the query, but, at least for my needs, imo it is very, very fast solution.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks for the feedback
@flance4011
@flance4011 Жыл бұрын
Wonderful
@thesilverkey
@thesilverkey Жыл бұрын
John, thank you. Where does the FROM Data$ bit come from? Ahhhhh, it's the sheet name. Nevermind. is sheet.name and are are the Row1 Headers. Got it. Thanks again!
@bayurukmanajati1224
@bayurukmanajati1224 2 жыл бұрын
Well, this is great. ADODB come to rescue with complicated database thing. But personally, I would love to use VBA cleanly without too many much additional reference activate. This is also include FSO library, where I tends to use Open method rather than streaming file into FSO.
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Bayu.
@kukuhwahyurinaldi6288
@kukuhwahyurinaldi6288 Жыл бұрын
Always great n structured explanation. The only Channel that will give enhancement knowledge. Allow me to ask U Something Mr Paul.. Is there any limit when using Turn on n turn off (app. Calculation manual then automatic, app.enableevents false then True) in a entry n record data ? Thank 4 ur answer 🙏
@scotolivera8207
@scotolivera8207 2 жыл бұрын
Great content as always, it would be nice if you could cover working with custom xml parts as addition hidden storage
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Scot, I'll keep it in mind.
@rrrraaaacccc80
@rrrraaaacccc80 10 ай бұрын
💯👍
@peterwooldridge7285
@peterwooldridge7285 2 жыл бұрын
A really great vid
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Thanks Peter
@TheRaf87
@TheRaf87 2 жыл бұрын
Hey John, great video. One question, can you use advance filter to copy data to a table that has dynamic address?
@jaimesastre6393
@jaimesastre6393 2 жыл бұрын
Thank You John. Very interesting, especially the two last parts. One question concerning the ADO SQL: The form seems a little specific. For example using "&". Is there a reference to know the different syntax? Thanks 👍
@narendramishra3503
@narendramishra3503 2 жыл бұрын
Thanks for the video. I wanted to ask two doubts. 1) How do we retain background colour and other formatting when we save a new excel sheet after some conditional formatting from older sheet. 2) How to keep comparing the new sheet with older sheet so that when a new entry satisfies the condition directly comes into our new sheet and if any entry doesn't satisfy condition will get out from the sheet.
@pratipbanerjee68
@pratipbanerjee68 Жыл бұрын
I have the same doubt
@paulmacabasco7592
@paulmacabasco7592 2 жыл бұрын
Hi, i am new to VBA, thanks for uploading. your videos are great and very helpful, For For Loop & Range; i just want to ask if how can i identify and copy the first row only.
@amazingpen
@amazingpen 2 жыл бұрын
The ADO method can be used if the row data over 65k lines?
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Yes, should work fine.
@ExcelInstructor
@ExcelInstructor 2 жыл бұрын
Hi John, do you have expirience working with VBA related to SAP? I do have an issue, when i use special sap option to generate data it gets put into the excel, however i cannot get the instance or name with that workbook. Thisworkbook.Path also shows no tring or text, yet im trying to automate getting data from this report and I need to save the workbook. Do u have maybe any idea how to do it?
@robertwide6592
@robertwide6592 5 ай бұрын
nice
@june4403
@june4403 2 жыл бұрын
Hi. I have a VBA code for copying & pasting an entire row if a condition is met in one cell. I am pasting into a different worksheet in the same workbook. Everything is working except one of my cells. The formula is being copied instead of the value. I have tried the paste special (xlpastevalues) but that's isn't working. I just replaced the word paste w/ the paste special code. Do I need to add anything else to my code other than this? Thank you in advance.
@VBASqlAccessPowerApps
@VBASqlAccessPowerApps Жыл бұрын
use range("rangename ").pastespecial paste =xlpastevalues like Range("A1:B3").Copy Range("C1").PasteSpecial Paste:=xlPasteValues
@hammeedabdo.82
@hammeedabdo.82 2 жыл бұрын
Thank you for this video, Is VBA faster then Python,c++ or c# ?
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
It's not possible to say because it depends on so many factors like the code you are using, the task being completed etc. VBA is very fast for processing data and suit be fast enough for anyone's needs.
@4txx
@4txx 2 жыл бұрын
I have used ADO method for a long time now. It is very useful but it always bugged me due to two things. Text values will be cutoff at 255 characters, and you can not really cast column format. It sometimes turns numbers to text, and just prints blank column as it tries to turn text into numbers. Did anybody have workarounds to these two things?
@4txx
@4txx 2 жыл бұрын
Also SQL used is from Access SQL which is very old and has limited number of basic commands.
@hedigson2586_
@hedigson2586_ 2 жыл бұрын
Hi Mr. Its possible write code from a normal module inside a event, for example write code inside open event that object thisworkbook or write code inside event selectionChange the sheet object... all than since standar module... Thanks Hola amigo, saludos desde venezuela. Es posible escribir codigo desde un modulo normal dentro del evento de un objeto como por ejemplo el evento Open del objeto thisworkbook o en el Evento SelectionChange del objeo sheet. todo desde un Modulo... Yo he logrado hacerlo, creando el evento desde el módulo. lo que no he logrado hacer es escribir o validar si el evento ya esta creado o no... Saludos..!
@shivangi24177
@shivangi24177 2 жыл бұрын
I am copying the filtered data from one workbook to another using arrays.I have used the following line in j loop: ThisWorkbook.Worksheets("Data").Cells(outRow,j)= data(i,j) but the data is not being copied in my current workbook Please help!
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/ I hope you enjoy my latest video. Make sure to download the source code from the link in the description and let me know in the comments which part you found most interesting.
@big1975E
@big1975E 2 жыл бұрын
I tried using ADO to query from the active workbook and I can’t open the connection. I get a Cannot update. Database or object is read-only. error message. How do I fix this? The workbook isn’t opened in Read Only mode.
@shivangi24177
@shivangi24177 2 жыл бұрын
I have 2 workbooks Source workbook- Sheet1 contains sample data Destination Workbook- Sheet2 is empty It has a button ,which when clicked should filter the data from Sheet 1 to Sheet 2 I am getting error: 438 While debugging,it is giving error in the following line: Set sheet =book.Workbook("Sheet1") Please help!!!!
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Book.Worksheets("sheet1")
@shivangi24177
@shivangi24177 2 жыл бұрын
@@Excelmacromastery Thankyou I am copying the filtered data from one workbook to another using arrays.I have used the following line in j loop: ThisWorkbook.Worksheets("Data").Cells(outRow,j)= data(i,j) Please help!
@АнтуанДеляМоль
@АнтуанДеляМоль 2 жыл бұрын
I wish i could to join to the webinar, but our goverment, visa and mastercard make it impossible. Thanks for this video.
@jbinmd
@jbinmd 2 жыл бұрын
Is ADO the only one that won't run on Mac?
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
ADO is the only one that uses an external library.
@scotolivera8207
@scotolivera8207 2 жыл бұрын
Link for source code is not working
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
Hi Scot. I checked the link and it works fine. What issue are you having? Ensure that you are not using any pop-up blockers.
@hammeedabdo.82
@hammeedabdo.82 2 жыл бұрын
Thank you Mr. Paul, 1- Can the Excel add-in be programmed in another programming language such as C#, C ++ , Python or only through VBA? 2- Can we replace VBA in Excel with C#? If it is impossible, why not?
@Excelmacromastery
@Excelmacromastery 2 жыл бұрын
You can use C#/C++ with VSTO(Visual Studio). You can use Python but it requires third-party tools to connect with Excel. VBA comes with Excel - no extra tools required.
@jamesdarylvelarde9343
@jamesdarylvelarde9343 2 жыл бұрын
Office script 🥺
@TheGhurabah
@TheGhurabah Жыл бұрын
lost me - needs to be more simple
@Ron-el6zh
@Ron-el6zh 7 ай бұрын
Please ignore my request for help, I worked out the issue, I used the wrong sheet name.
@hasanmougharbel8030
@hasanmougharbel8030 2 жыл бұрын
Hello dear, God bless your efforts. I am a totally newbie vba learner. I come to play with that code and have a simple enquiry. How could i amend this code so that i can return all generated workbooks, inside a folder by which the code is run. Thanks a lot Sub test() Dim rg As Range, i As Long, wb As Workbook Dim vNames As Variant, v As Variant Set rg = Workbooks("All raw data.csv").Worksheets("All raw data").UsedRange 'get unique names With CreateObject("Scripting.Dictionary") For i = 2 To rg.Rows.Count .Item(rg.Cells(i, 1).Value) = Empty Next i vNames = .keys End With For Each v In vNames ThisWorkbook.Worksheets("Sheet1").Copy Set wb = ActiveWorkbook wb.SaveAs v, 51 rg.AutoFilter 1, v rg.Offset(1).Columns("A").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 1) rg.Offset(1).Columns("U").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 2) rg.Offset(1).Columns("Q").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 3) wb.Save Next v End Sub
@neronjp9909
@neronjp9909 6 ай бұрын
Thx so much!! Great video
@neronjp9909
@neronjp9909 6 ай бұрын
Thx so much!! Great video
How to get the Last Row in VBA(The Right Way!)
15:41
Excel Macro Mastery
Рет қаралды 169 М.
How to make your Excel VBA code run 1000 times faster
16:55
Excel Macro Mastery
Рет қаралды 365 М.
Арыстанның айқасы, Тәуіржанның шайқасы!
25:51
QosLike / ҚосЛайк / Косылайық
Рет қаралды 700 М.
We Attempted The Impossible 😱
00:54
Topper Guild
Рет қаралды 56 МЛН
Beat Ronaldo, Win $1,000,000
22:45
MrBeast
Рет қаралды 158 МЛН
Try this prank with your friends 😂 @karina-kola
00:18
Andrey Grechka
Рет қаралды 9 МЛН
10 Years of VBA Array Knowledge in 40 Mins
41:57
Excel Macro Mastery
Рет қаралды 25 М.
How To Master Arrays In Excel VBA + FREE MACROS & CHEAT SHEET
29:09
Excel For Freelancers
Рет қаралды 10 М.
VBA Advanced Filter - The FASTEST way to Copy and Filter Data
18:33
Excel Macro Mastery
Рет қаралды 288 М.
Watch these 28 minutes if you want to become an Advanced VBA user...
29:01
Excel Macro Mastery
Рет қаралды 57 М.
How to Design and Code an Excel VBA Application Like a Pro
42:27
Excel Macro Mastery
Рет қаралды 221 М.
VBA Macro to Copy Data from Another Workbook in Excel
13:39
Excel Campus - Jon
Рет қаралды 659 М.
VBA to BROWSE & COPY Data from SELECTED File in Excel
10:00
Leila Gharani
Рет қаралды 339 М.
Excel vba class module tutorial 2024
46:45
Sean Johnson
Рет қаралды 2,1 М.
Арыстанның айқасы, Тәуіржанның шайқасы!
25:51
QosLike / ҚосЛайк / Косылайық
Рет қаралды 700 М.