Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Paste Values Into Next Blank Row

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Paste Values Into Next Blank Row

    Hi,

    I have a workbook with multiple sheets where I'm trying to copy rows where the value in column C > 0. I then want to paste these rows into a another sheet, called BoM. I'm ignoring the BoM sheet, along with a sheet called summary, and another sheet called Packet Storage.

    I'm got my code partially working, but I'm having trouble getting the rows from each subsequent sheet to begin pasting on the next available row. I can loop through each sheet, but then the next sheet just starts pasting at the top again overwriting the last sheet's data. I'm also getting the row headers from each sheet pasted into the destination sheet, which would be ok the first time, but I don't want that from each sheet thereafter.

    I'm sure there's a bit of ugliness in my code here (I'm a networking guy), so be nice. Thanks for your help on this in advance.

    Here's my code:

    Sub CopyRow()

    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "Packet Storage" Then
    ws.Activate
    Dim LastRow As Long
    Dim x As Long
    Dim rng As Range
    x = 1

    For Each rng In ActiveSheet.Range("C:C")
    If rng > 0 Then
    rng.EntireRow.Copy
    With Sheets("BoM").Cells(x, 1)
    .PasteSpecial xlValues
    .PasteSpecial xlFormats
    End With
    x = x + 1
    End If
    Next rng
    End If
    Next


    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    End Sub

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,670
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Hello davisshannon,

    Try this amended version of your macro...

    Code:
    Sub CopyRow()
    
    
        Dim LastRow As Long
        Dim r       As Long
        Dim rng     As Range
        Dim x       As Long
    
    
            x = 1
            Application.ScreenUpdating = False
            
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "Packet Storage" Then
                    ' // Data starts in row 2.
                    Set rng = ws.Range("A2")
    
    
                    ' // Expand the range width to the last header in row 1.
                    Set rng = rng.Resize(1, ws.Cells(1, Columns.Count).End(xlToLeft))
    
    
                    ' // Find the last row in column "C" with data.
                    LastRow = ws.Cells(Rows.Count, "C").Row
    
    
                    ' // Test if the worksheet has data.
                    If LastRow >= rng.Row Then
    
    
                        ' // Expand the range to the ladt row with data.
                        Set rng = rng.Resize(RowSize:=LastRow - rng.Row + 1)
                        For r = 1 To rng.Rows.Count
                            If rng.Cells(r, "C") > 0 Then
                                rng.Rows(r).Copy Worksheets("BoM").Cells(x, 1)
                                x = x + 1
                            End If
                        Next r
                    End If
                End If
            Next ws
    
    
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
    
    
    End Sub
    Sincerely,
    Leith Ross

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

    Default Re: Paste Values Into Next Blank Row

    Try this:
    Code:
    Sub CopyRow()
    'Modified 6/20/18 8:25 PM EDT
    Application.ScreenUpdating = False
    Dim i As Long
    Dim rng As Range
    Dim LastRow As Long
    Dim Lastrowa As Long
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "BoM" And Sheets(i).Name <> "Summary" And Sheets(i).Name <> "Packet Storage" Then
            LastRow = Sheets(i).Cells(Rows.Count, "C").End(xlUp).Row
            For Each rng In Sheets(i).Range("C2:C" & LastRow)
                If rng > 0 Then
                    Lastrowa = Sheets("BoM").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    rng.EntireRow.Copy
                    
                    With Sheets("BoM").Cells(Lastrowa, 1)
                        .PasteSpecial xlValues
                        .PasteSpecial xlFormats
                    End With
                End If
            Next rng
        End If
    Sheets(i).Rows(1).Copy Sheets("BoM").Rows(1)
    Next
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    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
    New Member
    Join Date
    Jun 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Thanks. I'm getting a 1004 error "Method resize of object range failed" for the following line:

    Set rng = rng.Resize(1, ws.Cells(1, Columns.Count).End(xlToLeft))

    Quote Originally Posted by Leith Ross View Post
    Hello davisshannon,

    Try this amended version of your macro...

    Code:
    Sub CopyRow()
    
    
        Dim LastRow As Long
        Dim r       As Long
        Dim rng     As Range
        Dim x       As Long
    
    
            x = 1
            Application.ScreenUpdating = False
            
            For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> "BoM" And ws.Name <> "Summary" And ws.Name <> "Packet Storage" Then
                    ' // Data starts in row 2.
                    Set rng = ws.Range("A2")
    
    
                    ' // Expand the range width to the last header in row 1.
                    Set rng = rng.Resize(1, ws.Cells(1, Columns.Count).End(xlToLeft))
    
    
                    ' // Find the last row in column "C" with data.
                    LastRow = ws.Cells(Rows.Count, "C").Row
    
    
                    ' // Test if the worksheet has data.
                    If LastRow >= rng.Row Then
    
    
                        ' // Expand the range to the ladt row with data.
                        Set rng = rng.Resize(RowSize:=LastRow - rng.Row + 1)
                        For r = 1 To rng.Rows.Count
                            If rng.Cells(r, "C") > 0 Then
                                rng.Rows(r).Copy Worksheets("BoM").Cells(x, 1)
                                x = x + 1
                            End If
                        Next r
                    End If
                End If
            Next ws
    
    
            Application.CutCopyMode = False
            Application.ScreenUpdating = True
    
    
    End Sub

  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,574
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Did you try my code in post 3 ??
    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"

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Yeah, sorry, I did. It is actually only grabbing an image from one of the sheets not in column C, and then also grabbing a couple of items from the sheet that it should be ignoring, Packet Storage. It doesn't appear to be cycling through column C at all.

    Quote Originally Posted by My Aswer Is This View Post
    Did you try my code in post 3 ??

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

    Default Re: Paste Values Into Next Blank Row

    Well I test all my script.

    The script copies all rows if column C in that row if not empty.

    Or what do you mean when you say Range is >0

    Does this mean if Range is empty or the values like 1 is greater then zero

    And my script does not look at sheet named Packet Storage
    You may want to check your sheet names.
    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
    New Member
    Join Date
    Jun 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Thanks. Yeah, I'm trying to go through column C in a sheets except those 3 that I called out (Packet Storage shouldn't be evaluated). If the value is greater than 0 I then want to copy those rows into the sheet called BoM.

    Quote Originally Posted by My Aswer Is This View Post
    Well I test all my script.

    The script copies all rows if column C in that row if not empty.

    Or what do you mean when you say Range is >0

    Does this mean if Range is empty or the values like 1 is greater then zero

    And my script does not look at sheet named Packet Storage
    You may want to check your sheet names.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,574
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Try this:
    Code:
    Sub CopyRow()
    'Modified 6/20/18 11:33 PM EDT
    Application.ScreenUpdating = False
    Dim i As Long
    Dim rng As Range
    Dim LastRow As Long
    Dim Lastrowa As Long
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "BoM" And Sheets(i).Name <> "Summary" And Sheets(i).Name <> "Packet Storage" Then
            LastRow = Sheets(i).Cells(Rows.Count, "C").End(xlUp).Row
            For Each rng In Sheets(i).Range("C2:C" & LastRow)
                If rng.Value > 0 Then
                    Lastrowa = Sheets("BoM").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    rng.EntireRow.Copy
                    
                    With Sheets("BoM").Cells(Lastrowa, 1)
                        .PasteSpecial xlValues
                        .PasteSpecial xlFormats
                    End With
                End If
            Next rng
        End If
    Sheets(i).Rows(1).Copy Sheets("BoM").Rows(1)
    Next
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    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"

  10. #10
    New Member
    Join Date
    Jun 2018
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste Values Into Next Blank Row

    Nope, identical results to last time. It's grabbing stuff from row A in Packet Capture, and then grabbing an image from column B in another sheet.

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
  •