Cells with Resize - Copy and Paste Values only

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
Hi
Iam using the below code ( I found on the internet) and does work great but some of the fields are formulas and I only want to paste the values. I have tried copy.value but get an object required error

Thank you



Dim bottomB As Integer
bottomA = Range("B" & Rows.Count).End(xlUp).Row
Dim d As Range
For Each d In Range("b2:b" & bottomA)
If d = Sheets("F-MAIL04").Range("e1") Then

Cells(d.Row, "E").Resize(, 3).Copy Sheets("F-MAIL04").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

End If
Next d
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
Use
Code:
Cells(d.Row, "E").Resize(, 3).Copy 
Sheets("F-MAIL04").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
Try this:
Code:
Sub Copy_Me()
'Modified  5/20/2019  8:37:08 AM  EDT
Dim bottomB As Integer
bottomA = Range("B" & Rows.Count).End(xlUp).Row
Dim d As Range
For Each d In Range("b2:b" & bottomA)
If d = Sheets("F-MAIL04").Range("e1") Then
Cells(d.Row, "E").Resize(, 3).Copy
Sheets("F-MAIL04").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlValues

End If
Next d
End Sub
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
Thank you both, I have used both codes and they work except it is only coping 2 rows of data it should be 3 rows. I seem to be stopping before it pastes the last row.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
OK I have been changing the data and something is the code is. causing it to only copy 2 rows. It does not matter how many rows I have only 2 will copy. My VBA is not great so any ideas would be appreciated

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
Do all rows on the activesheet Col E have data?
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
Would you please explain what your ultimate Goal is.
 

Liz_I3

Well-known Member
Joined
Dec 30, 2002
Messages
603
I have 2 tabs on my worksheet Data and F-MAIL04 The Data tab is where users will key in data. I have a button on F-MAIL04 that should copy all the data from the data tab where the date in Column B equals the date on F-MAIL04 cell E1. But not all rows are copied. Column C on Data is copied to the column A on F-MAIL04 Column D is not copied, E,F & G are copied to Columns B,C & D on F-MAIL04. My original code was working except that Column E on Data is a formula and I need to copy only the values.

This is the code to copy only Column C over and it is work fine
Dim bottomA As Integer
bottomA = Range("A" & Rows.Count).End(xlUp).Row
Dim c As Range
For Each c In Range("b2:b" & bottomA)
If c = Sheets("F-MAIL04").Range("e1") Then
Cells(c.Row, "C").Resize(, 1).Copy Sheets("F-MAIL04").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

End If
Next c

The Code to copy E,F and G will only copy the first 2 rows of data that match the date on F-MAIL04 Cell E1 even if there are more to be copied

Dim bottomB As Integer
bottomB = Range("B" & Rows.Count).End(xlUp).Row
Dim d As Range
For Each d In Range("b2:b" & bottomB)
If d = Sheets("F-MAIL04").Range("e1") Then

Cells(d.Row, "E").Resize(, 3).Copy
Sheets("F-MAIL04").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlValues

End If
Next d

Thanks Again for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,756
Office Version
365
Platform
Windows
How about
Code:
Sub Liz_I3()
   Dim bottomA As Long
   Dim c As Range
   With Sheets("Data")
      bottomA = .Range("A" & Rows.Count).End(xlUp).Row
      For Each c In .Range("b2:b" & bottomA)
         If c = Sheets("F-MAIL04").Range("e1") Then
            .Cells(c.Row, "C").Copy Sheets("F-MAIL04").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Cells(c.Row, "E").Resize(, 3).Copy
            Sheets("F-MAIL04").Cells(Rows.Count, "A").End(xlUp).Offset(, 1).PasteSpecial xlValues
         End If
      Next c
   End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,724
Messages
5,446,147
Members
405,384
Latest member
geowbadyt

This Week's Hot Topics

Top