3 Ways to Copy and Paste Cells with VBA Macros Video
3 Ways to Copy and Paste Cells with VBA Macros + Video
Bottom line: Learn 3 different ways to copy and paste cells or ranges in Excel with VBA Macros. This is a 3-part video series and you can also download the file that contains the code.
Skill level: Beginner
Copy & Paste: The Most Common Excel Action
Copy and paste is probably one of the most common actions you take in Excel. It’s also one of the most common tasks we automate when writing macros.
There are a few different ways to accomplish this task, and the macro recorder doesn’t always give you the most efficient VBA code.
In the following three videos I explain:
- The most efficient method for a simple copy and paste in VBA.
- The easiest way to paste values.
- How to use the PasteSpecial method for other paste types.
You can download the file I use in these videos below. The code is also available at the bottom of the page.
Video #1: The Simple Copy Paste Method
You can watch the playlist that includes all 3 videos at the top of this page.
Video #2: An Easy Way to Paste Values
Video #3: The PasteSpecial Method Explained
VBA Code for the Copy & Paste Methods
Download the workbook that contains the code.
Paste Data Below the Last Used Row
One of the most common questions I get about copying and pasting with VBA is, how do I paste to the bottom of a range that is constantly changing? I first want to find the last row of data, then copy & paste below it.
To answer this question, I created a free training video on how to paste data below the last used row in a sheet with VBA. Can I send you the video? Please click the image below to get the video.
Free Training on Macros & VBA
The 3 videos above are from my VBA Pro Course. If you want to learn more about macros and VBA then checkout my free 3-part video training series.
I will also send you info on the VBA Pro Course, that will take you from beginner to expert. Click the link below to get instant access.
Please leave a comment below with any questions. Thanks!
You may also like
Go To Source Cell of XLOOKUP Formula
3 Ways to Fill Down Blank Cells in Excel
VBA Macro to Create Power Query Connections for All Excel Tables
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
Hi,
I have two workbooks opened (both can be different names) and I want to copy a Range from Workbook1 SheetX (unkown/active) to Workbook2 (where I know the Sheet name)
Do you have any suggestions on how to do this?
Thanks,
Erika
you need to take work under one variable than activate the workbook and select particular sheet. You have to write code to copy paste in active workbook if you have any specific sheet than write sheet name.
Hi, I have two workbooks opened (both can be different names) and I want to copy a Range from Workbook1 SheetX (unkown/active) to Workbook2 (where I know the Sheet name)
I even want the command to open the worksheet from a directory too. Do you have any suggestions on how to do this? Hope to hear from you. Many thanks.
Windows(“incident.xlsx”).Activate
Range(“A:U”).Select ‘this trows the error: application-defined or object-deferred error
I need your support, I’m creating a workbook for daily report, and I’m stuck in the summary worksheet, in the VBA I don’t know how to link the information of the new report to be copied in the summary.
Sheets(“REPORTE DIARIO”).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Shapes.Range(Array(“Button 1”)).Delete
ActiveSheet.Name = Range(“P7”).Value
Application.DisplayAlerts = False
Sheets(“REPORTE DIARIO”).Delete
Application.DisplayAlerts = True
OK I’m having a little trouble making a macro for a project at work. What I need help with: i have one work book and user form on sheet one to fill in information and this user form i have a button that should copy range of cells on sheet2 (S6:S9) to the (L6:L9)(all of this is on sheet2 and the samr thing should happen sheet3 to sheet9. If i could get some help i would be grateful
I have a crunched data in one workbook and wanted paste it in other by using macro. Also the number of line items changes every day (increase or decrease), So help me to copy and pasting the data in main template without number mismatch.
Hello, nice writeup. One quick question- Instead of copying an orderly range of cells from one column, how may we copy multiple single cells & paste them into an orderly horizontal range?
For example copying B2, F9, and Q11 yet pasting them into A1:C1
Thank you!
I am trying to write VBA for the below.
If the value in column AA is =”-” and the value in column AC is $0.00 then copy the value in column X to Column AA. My data set is ever changing so I can not set specific ranges and it may be the case where I don’t have data the satisfies the above criteria.
I have a workbook with a few tabs in it. I want to copy a cell with a formula in it from one worksheet and paste it into a cell in a different worksheet. When I use “PasteSpecial Paste:=xlPasteFormulas” it turns the formula cell references into REF# errors. For example:
It’s pasting the formula into the destination worksheet, but it must think the cell reference is still related to the source worksheet…or something like that. Do you know what could be causing this? Thank you!
Can you tell me how to Cells from Sheets present in 4 or more Workbooks to one Final Workbook?
It would be really helpful.
i have 300+ excel workbooks.
my requirement is copy specific data from all workbooks to another new workbook though VBA, can this possible,
Yes it is possible
Sub Copy()
Dim Sheet1 as worksheet
Dim sheet2 as worksheet
Dim book1 as workbook
Dim book2 as workbook
Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy
Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats
will this work to copy a formula and use it on next line?
With a macro I am copying the last row of a table and pasting below as PasteFormulas, but when I do this, it consider the row as a ” total row” of the table. I do it in two different tables and in one it paste it okay but in the other one it considers it as a “Total Row” of the table.
Can you help me?
Dim lastRow1 As Long, erow1 As Long
lastRow1 = Worksheets(“WO_SendM”).Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow1
If Worksheets(“WO_SendM”).Cells(i, 9).Value = Me.Label164.Caption Then
Worksheets(“WO_SendM”).Cells(i, 4).Copy
Worksheets(“WO_SendM”).Cells(i, 5).Copy
erow1 = Worksheets(“WO_Ledger”).Cells(Rows.Count, 18).End(xlUp).Row
Worksheets(“WO_SendM”).Paste Destination:=Worksheets(“WO_Ledger”).Cells(erow1 + 1, 18)
End If
Next i
Is it possible to get two cell value 4 and 5 in another cell?
I wanted to maintain a formatting of one of my records. In which I want to copy formats of certain cells to no. of times the user enters this will reduce the time of formatting ( which is a lot for me). Can you please make a video on that plz?
Hi jon,
Thanks for your this tutorial, but i need a urgent help if possible answer to me.
copy and paste only filter cells from one excel to other excel using VBA
excel-vba
I’m stuck with a problem when I try to copy filtered cells from one excel file and paste it onto another file. I am using a macro call to get data where I try to fix this given code. It is not working when I try to copy visible cells which i filtered.
Private Sub CommandButton1_Click()
Dim lrCD As Long
Dim fNameAndPath As Variant
Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet
fNameAndPath = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xlsx), *.xlsx”, Title:=”Seleziona il file da aprire”)
‘Sets the variables:
Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open(fNameAndPath) ‘Modify to match
‘Copies each sheet of the SourceWB to the end of original wb:
For Each WS In SourceWB.Worksheets
WS.Copy after:=WB.Sheets(WB.Sheets.Count)
Next WS
SourceWB.Close savechanges:=False
Set WS = Nothing
Set SourceWB = Nothing
Set ASheet = Nothing
Set WB = Nothing
Application.EnableEvents = True
lastrow = Worksheets(4).Cells(Rows.Count, 1).SpecialCells(xlCellTypeVisible).End(xlUp).Row
For i = 3 To lastrow
Worksheets(4).Cells(i, 16).SpecialCells(xlCellTypeVisible).Copy
erow = Worksheets(“CFF”).Cells(Rows.Count, 1).SpecialCells(xlCellTypeVisible).End(xlUp).Row
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 2)
Worksheets(4).Cells(i, 16).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 3)
Worksheets(4).Cells(i, 15).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 4)
Worksheets(4).Cells(i, 12).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 5)
Worksheets(4).Cells(i, 13).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 6)
Worksheets(4).Cells(i, 18).SpecialCells(xlCellTypeVisible).Copy
Worksheets(4).PasteSpecial xlPasteValues = Worksheets(“CFF”).Cells(erow + 1, 1)
Next i
Sheets(4).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
End Sub
Thanks in advance
Hey, when you copy a range, there is some copy buffer somewhere in excel, I assume as a manipulatable object in VBA. Is there a way to access this and what’s it called? I want to see if it’s a collection or array, or something that can be split into that- in other words, something that can be iterated over. I don’t want to just paste in one drop. I want to choose which cells I paste based on other criteria.
I have list of unique names of players in column A in sheet 1. And in sheet 2 I have data of cricket score of different matches for that particular player with same unique names. And in sheet 3 I have data of same players for football matches. Now I want to first find the name which is in the sheet 1 from sheet 2 and cut that data from sheet 2 and paste in same row of that particulate player. And find and cut the data from sheet 3 and paste it in sheet 1 on same row in next available cell. Do this task one by one for all players till cell is empty. In some cases I have multiple rows in sheet 2 and sheet 3 for the same name. In that case add a new row under that name and cut paste all the data. And every time I will update the sheet 2 and sheet 3 with the new data with new names which I will add in in sheet 1 too it will update all the data in sheet 1 by clicking a single button.
I have tried lots of thin but still it is not working as I needed. Anybody can help me for this task…..
To Edit Or Modify A Macro In Excel -Step By Step
In the constant quest to improve their work, users can get to wonder if possible edit a macro in Excel .
And yes, this is toralmente viable and is not as complicated as you might think. While it is true that the greater knowledge and management tool you have, the better.
Now, for those wishing to learn how to edit a macro in the spreadsheet, we bring you a step by step fairly straightforward guide.
The macro
Before moving on to edit a macro in Excel, first go over the method for creating this element through the Visual Basic , which is the way in which he also edited.
Now, to begin, you will need to click the “ Developer ” tab located at the top of the screen, including the main panel.
Then, in the section called “ Code ” located on the left side, a button called “ Visual Basic “, the latter must be chosen.
Consequently, the window is open corresponding to the editor, where, if necessary, is selected in the “ Insert ” element “ module ” section.
Then we proceed to paste or type in the window for the module code, the code of the macro you want to use. And the macro to run correctly, you have to press, keeping the window module, the “ F5 ” button.
Finally, in the window of the Visual Basic, you opt for the top tab “ File “, then press on “ Close and Return to Microsoft Excel “.
Edit a macro in Excel
After you have created and applied the macro, if you need or want to change, it will be very easy to do it from the Visual Basic Editor.
To achieve this, simply go to the tab aimed at the “ Developer ” this is at the top in the ribbon.
Within this, there will be a section called “ Code “, which will be selected by clicking the “ Macros ” option to get a pop-up window.
You may also be interested in:
How To Solve The Error Code Netflix Ui-800-3 In Smart Tv?
In this way, this last element, a “Macro Name “, where you must choose the macro you want to edit is positioned.
Then, on the right side panel, be given in the form “ Edit “, so that opens the “ Visual Basic Editor ” & nbsp.; so the user can edit a macro in Excel from that tool without any problem by the program.
A recurring problem: Scheduler tab
There is a situation that happens when many users have started using the spreadsheet recently or only simple functions.
And, to access the tab “ Developer ” and all related to the macro, usually necessary commands is activated it.
This is pretty quick and easy, and after doing so, and it will feature the top menu of the main options of Excel.
So to have this tab, you will need to open in the upper belt, the tab “ File ” and updated to the corresponding window.
Within this, on the left side panel, the last option called “ Options “, thus opening a window to part with general options.
Of all those submitted, will be chosen by the edge of the side panel intended to “ Customize the Ribbon “, which will show two lists.
In this way, you can proceed to locate and activate the “ Developer “, and to save the changes, record the “ OK ” button is pressed.
Finally, returning to the home screen Excel spreadsheet or white, now appear in the upper belt we have included the tab.
And within this, all settings and functions related to the macro, divided into groups, where you can create and edit a macro in Excel.
Macros are a great tool that lets you save Excel patterns already established a task and thus be able to use later. Some features that can make macros are filter fields, crear graphics or copy data from one sheet to another. These are just some of the many features you can.
