Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Cells with Resize - Copy and Paste Values only
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2002
    Location
    Toronto Canada
    Posts
    582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Cells with Resize - Copy and Paste Values only

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,795
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    Use
    Code:
    Cells(d.Row, "E").Resize(, 3).Copy 
    Sheets("F-MAIL04").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,333
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    Board Regular
    Join Date
    Dec 2002
    Location
    Toronto Canada
    Posts
    582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    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.

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Location
    Toronto Canada
    Posts
    582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    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

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,795
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    Do all rows on the activesheet Col E have data?
    Last edited by Fluff; May 20th, 2019 at 09:59 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,333
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    Would you please explain what your ultimate Goal is.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Dec 2002
    Location
    Toronto Canada
    Posts
    582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    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

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,795
    Post Thanks / Like
    Mentioned
    381 Post(s)
    Tagged
    40 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    Board Regular
    Join Date
    Dec 2002
    Location
    Toronto Canada
    Posts
    582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cells with Resize - Copy and Paste Values only

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

    Liz

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •