Easiest Ways to Copy and Paste Cells with Excel VBA (copy, pastespecial, resize & offset)
HTML-код
- Опубликовано: 18 апр 2018
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Discover the power of Excel's VBA with our tutorial on Copy and Paste Special methods. Perfect for those looking to automate and enhance their Excel tasks, this video covers essential techniques, including resizing ranges and excluding headers.
⬇️ Download the workbook here: pages.xelplus.com/vba-copy-re...
🔑 What You'll Learn:
▪️ Essential VBA Methods: Explore two vital methods in Excel VBA - Copy and Paste Special.
▪️ Variable Size Range Copying: Learn how to copy ranges of varying sizes, a handy skill for dynamic data sets.
▪️ Using Resize Property: Understand how to resize a range before copying, ideal for situations like excluding headers in a current region.
🎓 Get access to the full Excel VBA Course here 👉 www.xelplus.com/course/excel-...
This tutorial shows you how you can copy and paste with VBA. We use the copy method as well as the pastespecial method.
The copy method in VBA, copies data, as well as formatting, formulas, comments etc - with the pastespecial method, you have more control over your paste options. For example, you can paste formatting only - or choose to paste values as well as number formatting.
I also show you how to use the resize property, so you can change the size of the current region before you copy. This is good for cases where you want to copy the data but exclude the header. Here we add in OFFSET as well.
★★ Links to related videos: ★★
Referencing Ranges with VBA: • Excel VBA: Referring t...
Learn about Visual Basic Editor: • Excel VBA tutorial for...
Recording macros: • Excel VBA: Copy Data f...
Properties & methods in VBA: • Learn How to Use Prope...
★ My Online Excel Courses ★ ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #vba
Grab the file I used in the video from here 👉 pages.xelplus.com/vba-copy-resize-file
Let me be very frank with you Leila. I am an ardent viewer of many videos on VBA. if ever there is somebody who does the explanations in such a marvelous way, It's YOU...No other people. Man, you are just awesome. Hats off, God bless you.
Me two. I love her work
Leila, has anyone told you lately how awesome you are? You are the best! It's the first time I've understood the resize range expression. Your explanation is so clear. Thank you very much.
.
(Love your hands & nails, and this color is the best for them 💖💖💖💖)
.
Im glad I discovered your channel. Am a programmer (COBOL, Java, C++, you name it), but only now am I needed for Excel/VBA projects, so I'm watching all of your videos.
.
hi leila(excelwoman),as usual, your simple method learning is very usefull. thanks for sharing your excel knowlege.looking forward your next lectue.
Thank you Mohammad. I'm happy you like the teaching style. Next VBA video will be up next week :)
love your tutorials so much, indeed you are an an expert
Wow! Another great tutorial, thanks Leila!
I find myself digging into your videos more often than before. And thank you for this tutorial, I was able to shorten the macro I recorded. 😀
Glad I could help!
@@LeilaGharani kindly is there excel formula to copy special values from one cell to another?, in such that when the initial cell is updated, the copied cell doesn't change
@@LeilaGharani hi leila, kindly is there excel formula to copy special values, without using vba?
Its looking so simple in this video than actually it would be..thanks a lot leila..will waiting for more on this..
Thanks Ajay. It does get easier the more we practice :)
Hi Leila , you make it so simple, thanks for the short video, very keen to learn more VBA, thanks
Happy to hear that!
Great Video Leila- really enjoying these videos! Great info :)
Very well explained.Thank you so much mam🙏🙏🙏🙏🙏
Very helpful and useful.
Thank you.
Leila u teach so well😭 im ur big fan
I'm glad to hear that.
One more thing
U concentrate on the points thar matters
And usefull to daily tasks
Thanks for making excel peace of cake
Thanks leila
Thank you! Really appreciate your support of all the videos.
Leila vba é outro nível, show de conteúdo.
Thanks for the Copy Paste VBA Fun!!
Thank you very much Mike :)
I always learn a lot from your videos..Thank you so much.I have a
question about currentregion,is there a way to copy paste just a few rows or columns that acts like currentregion "to be dynamic"
you're the best
Magic! So well explained!
a very instructive tutorial, thanks
You are most welcome!
Thanks a lot Madam, Keep up the good work.
Helpful
Thanks for the vedio Leila
Thanks for the video ma'am....nicely explained...loved it! 😊
You're welcome Mohit :)
Hello Leila thanks a lot for the video, i wonder if you can do copy from multiple worksheets and pastespecial values to a single worksheet
Explained very good. Thanks!
You're very welcome. Glad you like the explanation.
Hi Leila ! Your video is great. I need your help on how to copy paste data into a table format. I need to add it at the end of the table and with your method it always return an error...
thank you so much Ms. Leila you are great appreciated
You're very welcome Ismail.
When I copy with paste special, it deletes my conditional formatting (color coding). Am I doing something wrong?
What if we want to copy paste with cell dimensions (Row Height, Col Width, Merge, Picture etc) as we do mannually using fomat painter on clip board
Great Video... thumps up!
You made it so simple. That's great
I'm glad to hear that :)
Leila...great tutorial.I have a question.On sheet1 I have a table W8:AA450.I want to bring data from this table to sheet2 in a fixed range A4:A8,one row at a time.Because for each row of data from sheet1 table ,I'm doing some calculation on sheet2.Can you make a video or reply here thx.
Thanks leila. This was very interesting :)
You're welcome John :)
LEILA, U R GR8!
thank you, its very helpfull for me
Hi Leila is there a non-vba solution were you can convert formulas to values in cells which are associated with dates past a certain date?
Thank you very much.
Thanks for helping me with work 👍
🙏
Thanks for received in your valuable tricks .
You're welcome Sivarama.
What if you have formulas in the range and are using iferror to make them blank? I'm trying to only copy the cells with text from the formulas and this method works but it's copying all the cells with formulas in them.
Thank you very much!
Could you please give the code for copy-paste a table that to a specific cell on another sheet (i.e.: Print?
NB: That latter sheet has information such as Name and signature, and date of order. This sheet has to be printed
Hi I have a list of names in a column I need to apply filter for each names and copy the related data of names ,how to do that in VBA code pls share the code
How to auto squeeze weekends column up to 0.8 width for all year? The date is 11/05/2021 on the top the column. I need squeeze like ## size for them.
Hi Leila, need a small help, actually i need to copy data from one worksheet to another only when the headers of both worksheets match. i have written a code which is actually copying the entire range from one worksheet to another but actually the situation is, once the headers match, the data gets copied and then next time the data should get copied skipping one row in destination worksheet. please help
Crystal clear!
Any way to actually find all cells in a sheet with same value and repliacte the cell formatting across all whenever its changed on any of those cells.
THANK YOU!!!
So how do you copy a range from one workbook to another using Control C to copy the range, then go to the sheet you want to paste into and click a paste button that does a PasteSpecial on the values? it seems I can make this work with an unlocked file, but when I lock it down, it will not let me paste through the macro button.
Hi,
If I run VBA of Paste special method. The pasted column Turns into a text.
Could you Please help me to covert the pasted column into number format in VBA itself?
Hie Leila
The video was awesome
In the same way can we copy (fixed range) to paste special (in dynamic) only after giving command.
Hi, how do I resize a chart width using offset and counta with vba? Pleas help me
Hi Ms. Leila. I would just like to ask if it's possible to copy paste data on a spreadsheet even if the file is in a view mode only? My teammates and I are having a hard time working using this file because we have to manually look at the items, copy and paste it on a separate sheet so we cannnot vlookup the items. We manually click ctrl + f then ctl + c & p instead. Your helo would be very valuable to us. Thank you!
What if the data has broken rows i.e if the data is spread in different rows and few rows are blank in between. Will current range work?
hi can you tell me how can we to print userform fit to paper ..pleas
Leila, I enrolled myself for your VBA course today. Meet you there.
I hope you find a lot of value in the course for your work! Thank you for your support.
Welcome.
Hi Leila,
Need you help, it is possible to export entire excel file with same data and formats but not formulas. i have a file has charts, pivots, report dashboard with sources data but i dont want to share this file with formulas.
Hello Leila, I am wondering if you can help me with the following task: I have two work books, the first one is to create invoice with customer information and purchasing details , the second workbook is to save dynamically the data including the invoice number , date, total price ..etc. extracted form the first workbook . At the end of the day I am going to save both workbooks and shut down the computer . what I would like to have the next day is when I open the first workbook , I want to open the second workbook using a command button on the first workbook , also I want to be able to get the last invoice number saved in the second workbook to be transferred to the first workbook with adding one to it . the invoice numbers are saved in column A2 :A in the second workbook. the invoice cell number is L11 in the first workbook .
I have created a command button on the first workbook to open the second workbook , that is successful , but I am not able to write the correct VBA code to transfer the last row cell value of column A from second workbook to the first workbook .
Can you please help me ?
thanks
Issam
Please share how we can copy only the rows that fulfil a certain criteria.. thanks
Hello
I am trying to find an vba code to copy and paste every range of rows like(A1:C19).copy and paste in word file as image then copy from (A20:C39) and paste in the same word file and so on for the rest of the table in excel. they have told me that I need to use the looping in excel but I don't know exactly how to do it …. please help if you can
how can i use a formula to a table without loop in vba , for example i want to replace a array 1 ,5 , 6, 8 by the same array + 4 : 5, 9, 10, 12
Hey, In your Video you didn't manage how can I Past all Tablle + Color about the table but as a Value. SO I want to keep the color and point as a Value
Hi Ma'am, I have a question. How can we copy paste a data which has data+images.??
If we are working in one Excel and there is data+ images ( just like BOQ or quotation) so how can we copy or paste the some data ? Because sometimes what happens that the images do not copy well in format. So need solution please.
I want to copy data from multiple sheets in a workbook and paste it to one sheet in other workbook. It would be great if anyone can help
Thanks!!!!!!!!!!!!!!!
I need code to collect data from different workbooks books in folder and copy each relevant sheet in master file.
I have three different file in folder and have 3 works sheets in each file. I need to loop through each file and copy relevant sheet to relevant master file. Than data from next file to master workbook. Waiting for reply, please Naeem
can you show how to loop the selection
How do I copy a cell into another cell if its blank using VBA macro?
If you have please recommend the video, about Excel Vba paste special value if looking or finding Todays value . 💚💛❤️
Thank you :)
That's very kind, thank you!
Thanks
Thank you!
How to auto repeat paste special at each interval of time
well done
Hi leila,
Is there a way to copy and paste missing information from 1 column to another by match.??
Example: colm1 column 2
123 a 342
342 c 123
Automatically copy missing carctrs a & c wher they belong.
Terima kasih.
Thank you! Glad our content is helpful!
How to browse a folder or files via Excel VBA, copy the name and paste it in a specified 'word' file??
Thanks!
Thank you so much!
I need your help about VBA, will you do please, when you free time ?
awesome
how to copy bulk records with copy and paste specials with macros, its only copying 900 records maximums
Thanks mam Nice Video
You're very welcome Khalid.
best of the best..........
Thank you! Glad you like it :)
its a great tutorial, but what if, i want to copy and paste my data to a template that doesn't allow copy paste, well in such a case we have to link name ranges, or cell references, isnt it? or is there any good way then this
If the sheet is protected, you can un-protect in the macro before pasting and then re-protect. If you are using named ranges, you can reference that directly in the macro. This video shows different ways of writing to ranges: ruclips.net/video/acGJb9Oojho/видео.html
Hi Leila, can you please tell me how can I paste in only UN HIDE cells in excel (the visible ones)? Alt; only works for copy the visible cells not for paste in visible ones. Thank you!
Does anyone know the answer?
Hi Leila thank you for your videos. Do you have or have any plans on how to migrate data from one excel workbook that is in one carpet to another workbook in another carpet ? Let's say that I have monthly reports and I want to consolidate them all in just one sheet.
Thanks !
Yes - I cover these in my VBA course. I will try and add one of the videos to RUclips as well in the next weeks...
Leila Gharani awesomeee, please let me know when you upload it.
Thanks!
Very interesting lesson. I would like to ask you if you can prepare tutorial - how to use excel formulas in VBA?
Glad you like it. Ok - will add this to my list. Thank you for your suggestion.
thanks thanks thanks
Your way of teaching is amazing madam thanks for given such good VBA videos class and teaching.
You're very welcome Praveen. I'm happy you like the videos and the teaching :)
Merci
how to copy apply after auto filter only particular rows.
ex. apply autofilter only first 4 rows copy.
Please give me answer.
To copy only visible rows in the autofilter, you can use this code: ActiveSheet.AutoFilter.Range.Copy
How can this be applied from a different sheet?
What needs to be modified? I'd like to cut the datasource and paste it to a different sheet.
The worksheet name needs to be added before the range reference. This is something we cover in detail inside the course - I'll add it to my list to put out some videos on this topic for RUclips.
dear sir
I woukd like to learning about excel vba sumif function .
please sir.
What about a copy and paste button
How to copy paste data with specific header name
When I run ?Range("A4").CurrentRegion.Offset(1,0) in the immediate window, I get Run-time error 13: type mismatch.
The line of code, "Range("A4").CurrentRegion.Offset(1,0)" is showing RED in the VBE.
I also tried running "Application.Workbooks ("Book1").Worksheets("Sheet2").Select" in Workbook "Book1" and I got a runtime error: Run-time error 9: Subscript out of range. Could this be a related issue?
Any idea what is going on?
Thank you.
Robert
Hi Robert, you need .address in the end.
Leila Gharani THANK YOU!
You're welcome.
One very important questions raises here!!
Is it possible to copy different ranges from different sheets and paste them in a specific range?!
Hi Abdullah - yes - you just have to put the sheet name before the range - you can do this for example by Worksheets("Source").range("A4").currentregion.copy and paste it in another sheet, by mentioning the sheet name before the range. You can also use the code name of the sheet instead of referring to it through the sheets collection.
Leila Gharani I tried that before but I had to copy and paste two or three times. My question was:
Is it possible to copy two different ranges from two sheets and paste those two ranges in a specific range in code. I hope I made it clear. Thank you so much for your cooperation.
I see - If all your areas are one sheet, you could do it in one line like this: Range("A4:B7,A28:B32,A40:B42").Copy and then paste in one cell. But if your areas are in different sheets, you could use variables for the different areas and then do a loop to copy paste them in the loop. You'll need a variable for the next available row as well.
👌
how can I download the workbook for my practice?
I see - the link is missing - I'll update it in a bit...will let you know.
Thanks.
The workbook is now available here: www.xelplus.com/vba-copy-paste/ - just scroll down to the bottom of the post.
Thanks a ton Leila.
👍👍👍
For the first time, I was confused by your lesson?
1. In VBA code, what are arguments to Copy-Paste following:
a. “Äll merging conditional formats” option showing in paste special dialogue box;
b. paste link;
c. Row heights;
d. fixed picture &
e. linked picture;
f. use of camera to copy & paste pictures, images.
Hi Sandeep - here it's best to use the macro recorder and test each one. This gives you a quick glossary of all the options available - you can copy and paste the code and adjust for your projects.