3 Ways to Copy and Paste Cells with VBA Macros + Video

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!

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

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

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

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

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.

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

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

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