Cells with Resize - Copy and Paste Values only

[Liz_I3]

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

 

[Fluff]

Use

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]

Try this:

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]

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]

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]

Do all rows on the activesheet Col E have data?

 

[My Aswer Is This]

Would you please explain what your ultimate Goal is.

 

[Liz_I3]

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]

How about

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

 

[Liz_I3]

Yes, that is working perfectly...Thank you so much

Liz