3 Ways to Copy and Paste Cells with VBA Macros in Excel
139

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

3 Ways to Copy and Paste in Excel with VBA Macros

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.

VBA Copy Paste Methods.xlsm (91.7 KB)

Video #1: The Simple Copy Paste Method

Videos best viewed in full screen HD.

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.

VBA Copy Paste Methods.xlsm (91.7 KB)

'3 Methods to Copy & Paste with VBA
'Source: https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/
'Author: Jon Acampora

Sub Range_Copy_Examples()
'Use the Range.Copy method for a simple copy/paste

    'The Range.Copy Method - Copy & Paste with 1 line
    Range("A1").Copy Range("C1")
    Range("A1:A3").Copy Range("D1:D3")
    Range("A1:A3").Copy Range("D1")
    
    'Range.Copy to other worksheets
    Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")
    
    'Range.Copy to other workbooks
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _
        Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")

End Sub


Sub Paste_Values_Examples()
'Set the cells' values equal to another to paste values

    'Set a cell's value equal to another cell's value
    Range("C1").Value = Range("A1").Value
    Range("D1:D3").Value = Range("A1:A3").Value
     
    'Set values between worksheets
    Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value
     
    'Set values between workbooks
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = _
        Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value
        
End Sub


Sub PasteSpecial_Examples()
'Use the Range.PasteSpecial method for other paste types

    'Copy and PasteSpecial a Range
    Range("A1").Copy
    Range("A3").PasteSpecial Paste:=xlPasteFormats
    
    'Copy and PasteSpecial a between worksheets
    Worksheets("Sheet1").Range("A2").Copy 
    Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteFormulas
    
    'Copy and PasteSpecial between workbooks
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats
    
    'Disable marching ants around copied range
    Application.CutCopyMode = False

End Sub

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.

Paste Data Below Last Used Row VBA Free Training

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.

Free Training on Macros & VBA

Please leave a comment below with any questions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 139 comments
PL - November 21, 2016

Hi Jon

I’m trying to copy the data from sheet 2 (example:A5:AD10) to Sheet 1, and then sheet 3 (example:A5:AD6), total 5 sheets to go.
The number of row is unknown, its depends on how much the user input.
And A1:AD5 for each sheet is fixed as header, so i just want it to select from A5 to the last cell(but not empty cell) and copy to sheet 1.

I tried the code as below:

‘Copy from Sheet 2 to Sheet 1

Worksheets(“Sheet2”).Range(“A5”).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Worksheets(“Sheet1”).Selection.End(xlDown).Offset(1, 0).Range(“A1”)

On Error Resume Next
‘Copy from Sheet 3 to Sheet 1

Worksheets(“Sheet3”).Range(“A5”).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Worksheets(“Sheet1”).Selection.End(xlDown).Offset(1, 0).Range(“A1”)

On Error Resume Next

It works only if there are more than one rows, but if there are only one row, it will become duplicated rows on sheet 1 (Example:Copy Row of sheet 3 A5:AD5 to sheet 1, it will become two line in sheet 1)

How i explain well….Please help 🙁

Reply
Fahad Khalfe - November 21, 2016

I want to copy a range say A1:B1 from a total range of A1:B10 till it reaches blanks one by one and paste it horizontally in cells A21:A22 and again copy a range from A21:B30 and paste it to different locations say AA1 for A1:B1, AF1 for A2:B2 and so on

Reply
Barbara Ines - November 18, 2016

Hi Jon,
I need to copy data from one table column to another in the same table, (table is filtered) and when it paste, values don’t match. How can I fix it? I hope you can help me, thanks.
here is what im doing:

wsIPV.Range(“tIPV[ZFinal]”).Copy
wsIPV.Range(“tIPV[test]”).PasteSpecial Paste:=xlPasteValues

in the same row, after runing the macro, both columns should have the same value, but it is not.
any idea why?

ws is worksheet
tIPV is a table(ListObject)

Reply
    Jon Acampora - December 1, 2016

    Hi Barbara,
    Excel cannot perform the paste on a filtered range. This typically results in an error. My Paste Buddy Add-in has a feature called Paste Visible that solves this issue and performs the paste. I have a Developer Version of Paste Buddy available where you can access the VBA code that makes this all work. There are also other sites that have the code for this specific task of pasting to visible rows. You basically have to loop through each cell in the visible range to determine if it is visible, then perform the pastes individually. It is a labor intensive process, and this is why Excel does not allow it natively in the app. I hope that helps.

    Reply
      Barbara Ines - December 1, 2016

      Thanks Jon for your reply. I had to keep moving so what I did at the end was to remove the filter, copy and paste, and then apply the filter again.

      Reply
George Peterson - November 15, 2016

Hi Jon,

Wonderful training, information and resources…thank you very much! Similar to David Briggs’ comment on July 12, 2015, I, too, “recorded” a macro to accomplish a copy/paste task. I was really happy to discover your much simpler, shorter code for this task. I chose “Option #2” for pasting between workbooks that you teach and it works great for me…up to approximately 32,000 rows. After 32,000 rows, I get an Run-time error 6: Overflow. After much research on the Internet, it seems that this results from some parameters/restrictions that exist in Excel. Apparently, using “As Long” language in the coding might be able to help, but I’m much too inexperienced with VBA to know how to implement that language into the “Option #2” code that you shared. Essentially, I have a workbook that holds a bunch of data that changes daily (interest rate indexes, etc.). After the daily update of the data, I then copy this data (cells A1:BZ46000) to a separate workbook that I use. (The first workbook acts somewhat like what I would call a “lookup database”.)

Any thoughts you might be willing to offer on how to solve my overflow error 6 would be greatly appreciated! Thank you!

Reply
    Jon Acampora - November 21, 2016

    Hi George,
    Are you using any Integer variable types in your code?

    Reply
      George Peterson - November 23, 2016

      Thanks for your consideration of my inquiry! I thought I would share the code that I have at this point for the process that I’m trying to accomplish:

      Sub UpdateLookup()

      ‘ UpdateLookup Macro


      Worksheets(“Lookup”).Activate – THIS IS IN THE TARGET WORKBOOK
      Worksheets(“Lookup”).Range(“A1:BZ23000”).Value = Workbooks(“Master Lookup.xlsm”).Worksheets(“Lookup”).Range(“A1:BZ23000”).Value – THIS IS “COPY AND PASTE” TO TARGET WORKBOOK FROM SOURCE WORKBOOK
      Range(“B2”).Select
      Worksheets(“Index”).Activate – TAKES USER BACK TO “HOME PAGE” IN TARGET WORKBOOK
      Range(“C3”).Select
      End Sub

      I get the overflow error 6 when I change the cell references in the code to A1:BZ46000.

      Thanks again!

      -George

      Reply
Harshad N - November 10, 2016

hey i want to copy data from one workbook1 to workbook2, but query is i want to copy more data to workbook2 and i want it to paste it after that data…
eg: i want to copy 1st 10 record from workbook1-worksheet(sheet1) to workbook2-worksheet(data) and again i want to copy more records from workbook1-worksheet(sheet2) to workbook2-worksheet(data)… but paste that 10 records after the 1st records… what should be the exact code for it….?
regards harshad.

Reply
FAIZULLAH - November 8, 2016

Hello.

I want to know that if I copy and paste the data of the specified name like I want to sort all the information by name ali and paste all the data of ali to another sheet or workbook so what will be the code.

thanks
Arain

Reply
erik - November 6, 2016

hai sir, i need your help.
i have 5 sheets.
i need to copy a1:e10 then paste to a2 each sheet with one vba button.
how the vba? sorry fo my bad english

Reply
Lauren - November 4, 2016

Hi Jon,

Thanks for these easy tips, they’re helping me out a lot! One question I’m wondering if you could help me with – I would love to be able to use the below code but then paste as values instead of as formulas in the worksheet called ‘Color Forecast’. I want to be able to make the ‘Color Forecast’ tab of my worksheet a copy of the values in ‘DI Entry’ tab but without the formulas present in the DI tab…

Sub Copy_Data_to_Color_Forecast_Tab()

Worksheets(“DI Entry”).Range(“C6:EP200000”).Copy Worksheets(“Color Forecast”).Range(“A6:EN200000”)

End Sub

Please let me know if you have a tip! I am unable to view the videos for some reason, so apologies if the answer is in there.

Thank you!
-Lauren

Reply
    Jon Acampora - November 14, 2016

    Hi Lauren,

    Thanks for your question. The videos are on Youtube and might be blocked at your office.

    You can use either the PasteSpecial method or the .Values = .Values method for this. The Copy method will only do a straight copy/paste. To paste values you will need to use one of the other methods explained above. I hope that helps. 🙂

    Reply
Shibu - October 25, 2016

Please help

I have 2 cell with all my final result and date.
But the result date will change daily.
I need to copy the result in the cell to another cell with date like daily result backup.

Thanks in advance

Reply
    Jon Acampora - October 26, 2016

    Hi Shibu,

    You can use the PasteSpecial method to paste values. This will paste the date that is returned by the formula, and not change when the workbook is calculated. I hope that helps.

    Reply
Ratish - October 22, 2016

Hi Jon,

Below is my working code. But while copying and pasting its paste only formula instead of value. Could you please help me in rectifying error.

Sub Copy_Temp()

Dim xlSel As Excel.Range

Set xlSel = Excel.Application.Selection
xlSel.Copy Excel.Application.Sheets(“Template”).Range(“A1”)

End Sub

Regards
Ratish

Reply
ThomasK - October 22, 2016

A very helpful tutorial. Well structured and easy to apply. Thank you.

Reply
Zulf Ansari - October 19, 2016

Hi Jon,

I’m new to vba so please excuse me if I’m asking a stupid question.

So I have two questions and I’m hoping you can help me.

First question, I would like to copy a value from a cell let’s A2 and paste it cells K7:??.

?? means that populate this value from K7 to whenever data ends from column A in column 7.

Second question, can I execute this scripts from a batch file?

Thank you so very much Jon,

Have a blessed day.

Zulf Ansari

Reply
Moe - October 11, 2016

Hi mr jon
I have a workbook of 2 sheets
When i click the commande button to add data from sheet to another
Its always replace the old one
Please. Can i get the code that add my new data to the first erow in sheet2
Sheet 1 range(“A7:B34”)
Sheet 2 range(“B2”)
Thanks

Reply
yaash - October 9, 2016

Hi Jon,

Im trying to copy data from worsksheet 2 into worksheet 1 .

And I want macro to be able to copy all the columns in worksheet 2 with the exact number of rows as the coloumn D ( meaning i want to copy all the other columns with data to the reference to column D’s last row count)

i have something like this:
lastRow = Range(“D” & Rows.Count).End(xlUp).Row
Range(“I2”).

I do not know how to completely code that.

Help me please

Reply
    Jon Acampora - October 11, 2016

    Hi Yaash,

    Once you have the lastRow variable set, you can use it in the Range property to create a range reference.

    Range(“A1:C” & lastRow)

    If the lastRow was 100, then that would evaluate to Range(“A1:C100”)

    I hope that helps get you started.

    Reply
Peeraji Shilare - October 7, 2016

Hay,
I am Employer in health sector, i having a problem that i want to copy r range of cells in that some contents are colored with red fonts, expect red font row i want to copy them to another sheet called BRS using VBA ?

Reply
    Jon Acampora - October 11, 2016

    Hi Peeraji,

    One way to approach that is to filter the column for the font color, then copy/paste the visible cells. I don’t have an article that explains that in detail, but the macro recorder should help give you some of the code. I hope that helps get you started.

    Reply
PJ - October 7, 2016

Hi Jon,
I am working on a basic macro to copy a cell text if a criteria is met.
This is what I have, but when when I execute all my cells are replaced…
colume SS has text “PARENT TO BE IDENTIFIED”, I want the macro to replace ONLY the cells that show “PARENT TO BE IDENTIFIED” with the value of the cell from R column.

===
Sub PARENTNOTIDENTIFIED()
For Each R In Intersect(ActiveSheet.UsedRange, Range(“s:s”))
If R.Text = “PARENT TO BE IDENTIFIED” Then
Range(“R2:R10”).Copy Range(“S2:S10”)
End If
Next R

End Sub
==

hope you can help me resolve this one.
Kindest regard

Reply
    Jon Acampora - October 11, 2016

    Hi PJ,
    Try replacing Range(“R2:R10”).Copy Range(“S2:S10”) with the following.

    R.Value = R.Offset(0, -1).Value

    That will replace the value/text in R with value from 1 column to the left. The Offset property allows us to specify columns to the left/right/above/below. I hope that helps.

    Reply
RAM - September 22, 2016

THANKS TO HELP JOHN

Reply
Ajay Dwivedi - September 9, 2016

HI,

i want a file template , formula or VBA code for following

if any value change in cell A1 that value should be copy and paste in B2
if again value change of A1 the that value shoud copy in new cell without erasing previous number

Reply
    Jon Acampora - September 12, 2016

    Hi Ajay,
    You can use the worksheet change event to trigger a macro when a cell is changed in the worksheet. I hope that helps get you started. Thanks!

    Reply
Greg - August 25, 2016

Hi,

I have question,

How should I copy last row to another file but the file is same on the drive? I don’t want to open file, it should be automatically and I don’t know how should I do it?

Best,
Grzeg

Reply
    Jon Acampora - August 26, 2016

    Hi Greg,
    I don’t believe there is a way to update/modify the destination file without opening it. You can change the name of the source file with a SaveAs, open the destination file, then change the name back on the source file. You can write this into the macro, and that way you can have both files open at the same time. I hope that helps.

    Reply
Anastasia - August 15, 2016

Hi there,

Thank you so much for posting these. I have code I am using in order to pull a range of values over from several worksheets in to a master (Summary) worksheet. The code works fine but the range of cells it is pulling over have formulas in them. I don’t want the formula pulled over (as it is currently doing) I just want the cell value to be pulled over. I am unsure however, how to adjust my current code. This is what I’m working with:

Sub SummurizeSheets()
Dim ws As Worksheet

Application.ScreenUpdating = False
Sheets(“Summary”).Activate

For Each ws In Worksheets
If ws.Name “Summary” Then
ws.Range(“B1:L1”).Copy
ActiveSheet.Paste Range(“A65536”).End(xlUp).Offset(1, 0)
End If
Next ws
End Sub

Any help would be really appreciated! Thanks!

Reply
    Jon Acampora - August 20, 2016

    Hi Anastasia,

    You can use the Paste Values option for the PasteSpecial method. Checkout video #3 above for more info on how to use the PasteSpecial method.

    Reply
Suraj Bhalgotra - August 13, 2016

Hi Jon, I am working on something where I copy a row from sheet 1 and paste it in sheet 2 based on the no of people who have worked on it. I have used the above code and rows got pasted in sheet 2. I need help where the rows should be pasted based on the no. of people who have worked on it. if the no. of people who have worked cell has 3 names then the row should be pasted 3 times with each row having only one name.

Uploaded the file in Google docs for reference.
https://docs.google.com/spreadsheets/d/1XVwI1YxKxxlwFtlJ5szYSguEHZ3Y-76IpVBI0AmYJNI/edit?usp=sharing

Thanks for your help.

Reply
Eduardo França - August 11, 2016

Thanks Sir, it’s working well.

Reply
Nidhi Gupta - August 9, 2016

Hi Jon,
This is my first day with VBA macros and getting the 1004 runtime error while copying data from one workbook to another.

Sub CopyData()

‘clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

Workbooks(“L3 SR Tickets.xlsm”).Sheets(“L3 SR Tickets”).Range(Cells(2, 2), Cells(1000, 2)).Copy
Workbooks(“L3 status report 25thJuly16 to 31thJuly16.xlsm”).Sheets(“ServiceRequest”).Range(“B2”).PasteSpecial

‘clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False

End Sub

The same code runs fine if I try to copy lesser (around 500) cells at a time.
But my requirement is to copy large data.

Please help.

thanks,
Nidhi Gupta.

Reply
dan - July 27, 2016

Hi Jon,

Trying to figure out a macro that would help me at work. Here is my example: Basically I have 2 worksheets opened, I want to copy from worksheet1 cell a1,b1 to worksheet2 cell a2,a3 and basically need it to copy for the next 100 rows, so the macro would then copy worksheet1 cell a2,b2 to worksheet2 cell a4,a5 and so on.

Thanks.

Reply
    Jon Acampora - August 1, 2016

    Hi Dan,
    Great question! There are a few ways you could go about that. You can use the Transpose option of the PasteSpecial method to transpose the rows to columns. You would put this code in a loop to offset 2 rows from the last pasted row each time.

    You could also copy/paste the entire column, add an index column that contains numbers in sequential order for each paste range, then sort the index column.

    I will add this to my list of blog posts to create in the future. I hope that helps get you started. Thanks!

    Reply
Rob - July 21, 2016

Dear Jon,

Here’s my excel problem. It’s kind of a magic if I may say.

I’m working in sheet 1, I need that the contents of Sheet 1 be automatically copied to maybe Sheet 2 or any section in Sheet 1 that I may wish it be copied. The copied items should not be carrying the formula meaning it should be a plain text and values. I know the copy paste and or special, I know the equal sign (=) in a cell then go to the cell to be copied, but I won’t choose these. Please help. Thank you.

Reply
    Jon Acampora - July 31, 2016

    Hi Rob,
    Both of those methods will work. Why don’t you want to use them?

    Reply
      Rob - August 17, 2016

      Hi Jon,

      Well, I’d like the copying to be simultaneous. The copy paste and or special can be done after you have entered the data, while the one using the equal sign it can be established as a formula so you are able to copy not a text data bec. it has the formula.

      If you please, kindly take a look at this:

      I’ve researched another way of copying text data which is: In excel Sheet1, hold the ctrl key then press sheet2 or sheet3. Text entered in sheet1, ex. Rob in “A1” will automatically be copied in Sheet2 & 3 at the same col and row that is A1.

      Now what I need is, Rob can be copied to any where (row & col) I want in Sheet1, 2, & 3. Please help.

      Thank you,

      Rob

      Reply
Eero - July 7, 2016

Hi Jon i copyed your code from your and tried it.
But i get a run-time error 9 Subscript out of range.

Here is the code that i’m using

Sub Pastespecial_excample()

‘Copy and PasteSpecial between workbooks
Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy
Workbooks(“Book3.xlsx”).Worksheets(“Sheet1”).Range(“A1”).PasteSpecial Paste:=xlPasteFormats

End Sub

I hope for a quick reply and a an ansver i have a bigger program where i am trying to get data from a different book from a specific plas e.g.A3:AA150, and to paste it to a different sheet but i get the same error as this simple code that i have posted here

Reply
    Jon Acampora - July 12, 2016

    Hi Eero,

    In this case, both Book1 and Book3 will need to be open before running the macro. The files will have to be saved with these exact names. I hope that helps. Let me know if you have questions.

    Reply
H.Corona - July 6, 2016

Hello John

I am working with big set of data, so am trying to pull the data from other workbook bypassing the clipboard to save resources.

This is part of a endless loop that i have in order to keep a dashboard updated, i tried and worked, but i notice that after a few cycles the data does not update
any idea why?

there is another method that could help me get the same results?

Workbooks(“Dashboard (005).Xlsm”).Sheets(“Data Source”).Range(“A:Q”).Value = Workbooks(“New Data”.xls”).Sheets(“New Data Tab”).Range(“A:Q”).Value

Reply
    Jon Acampora - July 12, 2016

    Hi H.,
    I’m not sure what you mean after a few cycles the data does not update. You will have to have both workbooks open for this code to work.

    It’s also best to not use full column references like that (A:Q). You might want to use methods to find the last used cell in the source range first. Here is an article on how to find the last used cell on a sheet. I hope that helps. Thanks!

    Reply
Shubham Sharma - June 30, 2016

Hello Jon,

I am trying to paste the value of changing random numbers thousand times as a part of simulation. I have calculated a mean using random numbers and every time it gives a unique value. I want this unique value to be pasted thousand times.

Reply
Francois - June 28, 2016

Hi Jon

this is my first attempt to use VBA code and i have watched your videos but need more guidance.

I have a master sheet with multiple data per client in a row. i would like to create separate client works sheet copying the date from the master sheet into various columns, description, value(formula), order value9formula) and order cycle. Please assist with a suggested code or way forward.

Thanks

Francois

Reply
Dominique Tremblay - June 23, 2016

Hi Jon,

Thank you for this explanation which helped me tremendously with a past work. However, this time, I face a challenge that leaves me without a solution…yet! What I hope to achieve is my macro would find a cell (based on another cell input, that I was able to do), autofilter that column based on another cell (again, I was able to do that) and then, copy column A + my selection’s column to a new workbook. I tried with the union function without luck thus far.

Thank you in advance for your answer.

Reply
    Jon Acampora - June 30, 2016

    Hi Dominique,
    Sorry to not get back sooner. Did you find a solution yet? If not, can you post your code here? I’d be happy to take a look. Thanks!

    Reply
Daniel Gutierrez - June 20, 2016

Hello Jon,

I hope you are doing well. I really appreciate all the videos you have been doing since they are of great help! I was just wondering if you could give me some advice, too. I am just trying to copy one column from one Workbook, and paste it to a column in another Workbook. Both Workbooks are saved in the same folder in the same computer. Here is the code I have been using:

Sub ImportData()

Dim document1, document2 As String

document1 = “IMA-1.xlsx”
document2 = “IMA-2.xlsx”

‘Open first workbook
Workbooks.Open “C:\filepath\” & document1

‘Copy the column
Workbooks(document1).Worksheets(“Sheet2”).Range(“O9:O300”).Copy

‘Activate document 2
Workbooks(document2).Activate
Workbooks(document2).Worksheets(“Sheet2”).Range(“N9:N300”).PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

‘Save document1 and close
Workbooks(document1).Close SaveChanges:=False

End Sub

I am using exactly what you said we were supposed to use, but for some reason the code is not doing what it is supposed to be doing. It opens document1, but that’s it….It doesn’t do the rest of the code. It doesn’t trigger any error.

What should I do?

Thanks!

Best,

Daniel

Reply
    Jon Acampora - June 22, 2016

    Hi Daniel,
    That PasteSpecial paste type you are using is only going to paste the cell formatting. Is this what you are expecting? If not you will want to change the xlPasteFormats to one of the other paste types. Here is a list of the PasteType enumerations. I hope that helps. Thanks!

    Reply
Cayla - June 20, 2016

Thank you so much for these helpful videos!

I am trying to do multiple rounds of copy:paste – first from A1:A3 to A4:A6, then A1:A3 to B4:B6, then A1:A3 to C4:C6, etc. so that eventually, as I enter different values into A1:A3 and run the macro, I get a large table of all my different values. Any advice on how to do this?

Reply
Pierce - June 14, 2016

Hi Jon,

I keep on encountering a run-time error 9 in the last two lines of the code. Any idea why that would happen

Sub getData()
Dim currentDay As String, currentMonth As String, currentYear As String, fileDate As String, workbookOne As String, workbookTwo As String
currentDay = (Day(Date))
currentMonth = UCase((MonthName(Month(Date))))
currentYear = (Year(Date))
fileDate = “(” + currentDay + ” ” + currentMonth + ” ” + currentYear + “)”
workbookOne = “Import Non Import Summary” + fileDate
workbookTwo = “ZICO & Monster Tracking Summary” + fileDate

Workbooks(“DailyTemplate_SG_v2 1_08Jun_2016”).Worksheets(“Summary”).Range(“C21”, “I23”).Value _
= Workbooks(“workbookOne”).Worksheets(“Sheet1”).Range(“C3”, “I5”).Value

End Sub

Reply
    Jon Acampora - June 20, 2016

    Hi Pierce,
    The Workbooks references need to have the file extension in them.

    Workbooks(“DailyTemplate_SG_v2 1_08Jun_2016.xlsx”) or Workbooks(“DailyTemplate_SG_v2 1_08Jun_2016.xlsm”)

    I hope that helps.

    Reply
siva - June 1, 2016

Hi Jon

My Query

1. Have an excel sheet with data
2. Need to copy the xl data from the sheet1 of the xl book, and mail it using macros
3. Need it simple, and preferred object for mail is outlook

Thanks in advance

Siva

Reply
Mark - May 31, 2016

I have entered your Paste value code into my work book and it is working well, but I would like it to automatically run when ever the cell in questions, in this case cells AC53:AC60 changes rather than run the Macro manually. Is there any way of doing this?

Option Explicit
Sub Paste_Values_Examples()
Range(“AD52”).Value = Range(“AC52”).Value
Range(“AD53:AD60”).Value = Range(“AC53:AC60”).Value
End Sub

Reply
    Jon Acampora - June 2, 2016

    Hi Mark,

    Yes, that can be done with worksheet events. Macros can be run when certain actions are taken in the worksheet by the user. In this case we will want to use the Worksheet_Change event. I have a tutorial on this in my VBA Pro Course, and I will try to write up an article on it. Here is an article from Contextures that explains the Change Event.

    Reply
Sai Varun - May 30, 2016

Dear Jon,

Could you please help me with following macro.

I am looking for a macro which can copy data from A1 of Sheet 1 to B1 of sheet 2.then next A2 of Sheet 1 to B2 of sheet 2 and so on.

Could you please help me with this.

Appreciate your help in this regards.

Thanks
Sai

Reply
ramesh - May 16, 2016

sir
i have a issue i want to copy my b7 e20 e21 data to other workbook d:\ram\esh\2.xlxs
sheet 2
thanks in advance please reply me here or send me email i am waiting

Reply
amani mwakafwila - May 2, 2016

hi guys i need support for this line Sheet11.Cells(j, 1).PasteSpecial x1pastevalue i don’t know what is wrong

my full command is

Sub Macro1()
Dim myfile As String
Dim n As Integer

‘ file t be executed
n = 30

myfile = “D:\mm.txt”
Open myfile For Output As #1

For i = 2 To n + 1

Sheet1.Cells(2, 1).Value = Sheet10.Cells(i, 1).Value

For j = 12 To 337
Sheet1.Select
Sheet1.Cells(j, 1).Select
Selection.Copy
Sheet11.Cells(j, 1).PasteSpecial x1pastevalue

Write #1, Sheet11.Cells(j, 1).Value

Next j

Next i

Close #1

Reply
    Jon Acampora - May 9, 2016

    Hi Amani,

    The paste enumeration is XL not X1. I know it is hard to differentiate the “1” and the “l” with a lot of different fonts. Here is a list of the pastespecial method paste type enumerations on the MSDN help site. I hope that helps.

    Reply
      amani mwakafwila - May 10, 2016

      Dear Jon

      Thanks for your reply now i run it without ERRORS

      have a nice day

      Reply
        Jon Acampora - May 10, 2016

        Awesome! Glad to hear it is working now. There are a lot of little nuances like this when learning VBA, but it does get easier over time. Thanks again!

        Reply
David - April 26, 2016

Dear Jon,

thanks for the well structured video for copying and pasting in VBA.

I have a MacPro 2010 (so assume that Excel is 2010) and am having trouble executing the first easy command:

‘The Range.Copy Method – Copy & Paste with 1 line
Range(“A1”).Copy Range(“C1”)

You say that you can run the command by pressing F8. I also notice a yellow marker. I keep pushing F8 and nothing happens.

Can you help me?

Thanks

David
Berlin / Germany

Reply
    Jon Acampora - April 26, 2016

    Hi David,

    The F8 keyboard shortcut equivalent for stepping through code on the Mac is Cmd+Shift+i. I believe you have the Mac 2011 version of Excel. Try it out and let me know. Thanks!

    Reply
sam - April 20, 2016

I tried the below script but it only copied the value into sheet2 but not the color that I set on sheet1.

‘Copy and PasteSpecial a between worksheets

Worksheets(“Sheet1”).Range(“A2”).Copy
Worksheets(“Sheet2”).Range(“A2”).PasteSpecial Paste:=xlPasteFormulas

I also tried the below and the color format was copied to c1 but not the value of a1 .
Range(“A1”).Copy
Range(“c1”).PasteSpecial Paste:=xlPasteFormats

thanks for your help
sam

Reply
    Jon Acampora - April 26, 2016

    Hi Sam,
    That is correct. You are using the PasteSpecial options to paste the formulas. This only pastes the formulas or values, and does not paste formatting. PasteFormats only pastes the formats and does not paste values or formulas. If you want to paste everything then just leave the Paste argument out.

    Range(“A1”).Copy
    Range(“C1”).PasteSpecial

    That code will paste the formulas/values and the formatting. I hope that helps.

    Reply
Brad - April 19, 2016

Hi John,

Great videos, thanks for making them. I was wondering if you could give me some advice?
I’m trying to write some VBA to populate cells in the following way.
Starting at cell A1, populating through to A25 with the number 1, then from cell A26 populate the next 25 cells with 2, all the way up to 25 000. So in essence create 50 000 groups of 25 cells, with each set of cells increasing by 1, ending up with 652 000 rows.

I don’t expect a full answer, maybe just a nudge in the rite direction.

Thanks very much,
Brad

Reply
    Jon Acampora - April 26, 2016

    Hi Brad,
    Sorry to not get back to you sooner. Yes, I can help with that. We can use a For Next Loop to create the sequence of numbers. Here is the code:

    Sub Fill_Sequence()
    
    Dim lRow As Long
    
    
        For lRow = 1 To 25000
            Range("A1").Offset((lRow - 1) * 25).Resize(25, 1).Value = lRow
        Next lRow
    
    
    End Sub

    This code will loop through the numbers 1 to 25000. It uses the Offset property to offset 25 rows each time in the loop. It also uses the Resize property to resize the range to 25 rows tall. Then it sets the value of the 25 cells in that range equal to the current number of the variable lRow in the loop.

    I have a free video series on getting started with Macros & VBA that explains loops in more detail.

    I hope that helps. Let me know if you have any questions.

    Reply
Elizabeth - April 11, 2016

Jon,

In regards to my previous post, you mention in the first video that there is no need to do the selection like the record macro does, however, is this necessary if you want to cut the selected range and not just copy it?

Elizabeth

Reply
    Jon Acampora - April 11, 2016

    Hi Elizabeth,
    No, the Cut method will work similar to copy. You don’t need to select the cell or range first. Let me know if you have any other questions.

    Thanks!

    Reply
Elizabeth - April 11, 2016

Jon,
Thank you for your free and clear video tutorials. Do you have a recommendation on another video that explains how to do the copy/paste in a loop? I have to do the same sort of thing, repetitively and want to know if there is a way to loop the action instead of specifying each copy range and paste range?

Elizabeth

Reply
    Jon Acampora - April 11, 2016

    Hi Elizabeth,
    Great question. You can definitely use a loop to automate the copy/paste. The setup of the loop will depend on what you want to loop through to change the range references for the copy paste. Do you want to loop through rows, columns, or specific ranges? Let me know and I will provide more help. Thanks again!

    Reply
sathish - April 4, 2016

Hi Jon,

i have summary data in one excel file. Example “Data1” contains some variables and numbers and “Data2” contains some details.

Based on the data numbers, separate excel files needs to create with the details available in summary file using copy and paste values.

Could you help with the coding for these.

Reply
    Jon Acampora - April 8, 2016

    Hi Satish,

    I’m not sure I fully understand your question. The code sample above contains an example of how to copy and paste between workbooks. Is that what you are looking for?

    Reply
james - March 31, 2016

Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value = _
Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“NamedRange”).Value

How would you use the above with a named range. I’ve used it as per above but it only copies the first cell in the range into A1 and not the whole named range.

Reply
    Jon Acampora - April 1, 2016

    Hi James,
    For that line of code, the destination range will have to be the same size (rows and columns) as the named range. You are specifying “A1” as the destination. That means that only the value in A1 will be changed. So, A1 needs to be changed to reference a range that is the same number of rows and columns as the named range. You could use something like the following code for that.

    lRow = Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“NamedRange”).Rows.Count
    lCol = Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“NamedRange”).Columns.Count

    Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Resize(lRow, lCol).Value

    I hope that helps. Thanks!

    Reply
Brian Z - March 15, 2016

Hi Jon,

I am trying to copy and paste the same data range on multiple worksheets onto a single summary tab. Do you have any tips on how to accomplish that?

Thanks,
Brian

Reply
    Jon Acampora - March 16, 2016

    Hi Brian,

    Great question! I actually have a free video series on macros and VBA, and we cover that exact scenario in the 2nd video. Here is the link to signup, it’s free.

    https://www.excelcampus.com/free-macro-vba-training/

    Basically you will want to use a Loop to loop through each sheet, copy the data/range, then paste to the summary sheet. I cover it step-by-step in video #2 of that series and also provide an example file so you can use the code and modify it. Check it out and let me know if you have any questions. Thanks again!

    Reply
GrahamL - February 25, 2016

Hi Jon
in the Paste_Values_Examples() above, does one reference the path location within the “”
eg

Workbooks(“C:\Source\Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy _
Workbooks(“C:\Destination\Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”)

Thx
Graham

Reply
    Jon Acampora - March 1, 2016

    Hi Graham,
    Great question! The Workbooks Property represents a collection of all the open workbooks on the computer. So you only need to reference the workbook name, and not the file path. The following is the correct code.

    Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Copy

    If the workbooks are not open then you will need to add lines of code above that to first open the workbook. The following line uses the Workbooks.Open method to open the workbook.

    Workbooks.Open “C:\Source\Book1.xlsx”

    I hope that helps. Please let me know if you have any questions. Thanks!

    Reply
salim pathan - December 29, 2015

good 1

Reply
David Briggs - July 12, 2015

I never cease to be amazed at what I don’t know.
I’ve created some powerful automated spreadsheets using VBA. For example, I created some VBA code that uses the LINEST and SERIESSUM functions to build predictive gas usage models based on historic daily natural gas consumption and temperature data. Unfortunately, I initially learned VBA coding by reviewing the code created by the Macro Recorder. I’m sure you’re aware how much code is created by the recorder just to copy and paste data from one workbook to another. It’s sad to say, but I had no idea it could be as simple as what you’ve shown here. I look forward to revising my code.
Thanks!

Reply
    Jon Acampora - July 12, 2015

    Thanks David! I completely agree with learning new things. I don’t know if I’ve ever used the SERIESSUM function. There is always something new to learn with Excel and VBA and I think that is what makes it fun. Thanks again and have a great day!

    Reply
John Catsicas - July 1, 2015

Thank you Jon for the outstanding work you continuously put out – thank you Sir

Reply

Leave a Reply: