none
Excel VBA insert copied records to last not empty rows RRS feed

  • Question

  • Hi,

    I have a requirements to insert a copied rows to an existing worksheet. Finding the last not empty rows then then then copied the records. may I ask on how to do this in vba macro. thanks.

    Below is sample data:

    Source Data Worksheet
    
    column--- AB------- AC
    
    row----category-----Model----SKU
    ---------------------------------
    
    1		      		------ppppppp			
    2                     		------ppppppp
    3                     		------ppppppp
    4------Apple--------xx------xxxxxxx
    5------Orange-------cc------ccccccc
    6------Orange-------yy------yyyyyyy
    7------Manggo-------yy------yyyyyyy
    8------Manggo-------yy------yyyyyyy
    9------Manggo-------yy------yyyyyyy
    10-----Mangoo-------yy------yyyyyyy
    
     
    
     
    
    target worksheet
     
    row----category-----Model
    ------------------------------
    
    1-------Apple--------aa
    2-------Apple--------aa
    3-------Apple--------aa
    4-------Apple--------aa
    5-------Orange-------cc
    6-------Orange-------cc
    7-------Orange-------cc
    8-------Manggo-------yy
    9-------Manggo-------yy
    10------Mangoo-------yy
    11------Mangoo-------yy
    12------Mangoo-------yy  ---last not empty records, insert the 7 records after this part.
    -----------------------------------------------------
    Total|Total|     
    -----------------------------------------------------
            Set wb = ThisWorkbook
            Set ws = wb.Sheets("TempTable")
            Set ws1 = wb.Sheets("Received")
    
    
            With ws
                lrow = ws.Cells(.Rows.count, "AB").End(xlUp).Row '.Range("AB" & Rows.count).End(xlUp).Row  
                .Range("AB2:AB" & lrow).Copy
                DestRow = ws1.Range("A" & Rows.count).End(xlUp).Offset(0, 1).Row 'not empty row of targetworksheet
    
              
    
            End With





    • Edited by Lenoj Tuesday, September 16, 2014 6:36 AM
    Tuesday, September 16, 2014 6:34 AM

All replies

  • Hello Lenoj,

    Try like this:

    Sub Main()
    
        Dim ws As Worksheet
        Set ws = Sheets("TempTable")
        
        Dim ws1 As Worksheet
        Set ws1 = Sheets("Received")
        
        Dim lastRow As Long
        lastRow = ws.Range("AB" & Rows.Count).End(xlUp).Row
        
        Dim destRow As Long
        destRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
        
        ws.Range("AB2:AB" & lastRow).Copy Destination:=ws1.Range("A" & destRow)
        
    End Sub


    Tuesday, September 16, 2014 7:14 AM
  • Thanks for the reply.

    What if the source data is like this. there is a blank cells in row 7. how can i copy the rest rows with data.

    ALso, if i will copy this records those empty cells will be also copied? I wanted to copy only those records that category is not empty.

    Source Data Worksheet
    
    column--- AB------- AC
    
    row----category-----Model----SKU
    ---------------------------------
    
    1		      		------ppppppp			
    2                     		------ppppppp
    3                     		------ppppppp
    4------Apple--------xx------xxxxxxx
    5------Orange-------cc------ccccccc
    6------Orange-------yy------yyyyyyy
    7                               ------yyyyyyy
    8------Manggo-------yy------yyyyyyy
    9------Manggo-------yy------yyyyyyy
    10-----Mangoo-------yy------yyyyyyy
    11-----Mangoo-------yy------yyyyyyy


    • Edited by Lenoj Tuesday, September 16, 2014 8:13 AM
    Tuesday, September 16, 2014 8:10 AM
  • Here is my vba code. the issue here is when copying a filtered data there's a
    rows that were hide in between of row 8 and 10. this copied only the from row 5
    to 8 only but the rest from 10 to 14 was not copied.

    My sample data filtered data
    row-category

    ---------------------------
    5---Apple
    6---Apple
    7---Motorola
    8---Motorola
    10--Motorola
    11--Apple
    12--Apple
    13--Apple
    14--Apple

    With ws
             Set rng = ws.Range("A1:AD" & ws.Cells(Rows.count, 1).End(xlUp).Row)
             rng.AutoFilter Field:=28, Criteria1:="<>"
             lrow = .Range("AB2:AB300").Cells.SpecialCells(xlCellTypeConstants).count
             lnrow = .Range("AB" & Rows.count).End(xlUp).Row
             
             With ws1
                destrow = .Range("A" & Rows.count).End(xlUp).Offset(-1, 0).Row
                m = destrow
                For i = m To m + lrow
                    .Cells(i, 1).EntireRow.Insert
                Next
             End With
             .Range("AB2:AD" & lrow).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Cores Received").Range("A" & destrow)
        End With

    Wednesday, September 17, 2014 7:48 AM
  • Hi,

    >>I wanted to copy only those records that category is not empty.<<

    To copy all the non-empty data, you just need to add a judgment before copying. I edit the Michal Krzych's code as followed.

    Sub Main()
    
        Dim ws As Worksheet
        Set ws = Sheets("TempTable")
        
        Dim ws1 As Worksheet
        Set ws1 = Sheets("Received")
        
        Dim lastRow As Long
        lastRow = ws.Range("AB" & Rows.Count).End(xlUp).Row
        
        Dim destRow As Long
        
        Dim cel As Range
        For i = 2 To lastRow
            Set cel = ws.Range("AB" & i)
            If Trim(cel.Value) <> "" Then
            destRow = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
            ws.Range("AB" & i & ":AC" & i).Copy Destination:=ws1.Range("A" & destRow)
            End If
        Next
    End Sub

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 17, 2014 8:04 AM
    Moderator
  • Hi,

    >>the issue here is when copying a filtered data there's a rows that were hide in between of row 8 and 10. this copied only the from row 5 to 8 only but the rest from 10 to 14 was not copied. <<

    I suggest you use the code below

    lastRow = ws.Range("AB" & Rows.Count).End(xlUp).Row 
    .Range("AB2:AD" & lastRow).Copy Destination:=ws1.Range("A" & destRow) 

    to replace the line

    .Range("AB2:AD" & lrow).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Cores Received").Range("A" & destrow)

    .Then it will copy all the visible data after autofiltering.

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 17, 2014 9:03 AM
    Moderator
  • hi luna,

    I tried the codes but it was copied to the last not empty rows.   the issue here is when copying a filtered data there's a rows that were hide in
    between of row 8 and 10. this copied only the from row 5 to 8 only but the rest
    from 10 to 15 was not copied.

    below is the sample data.

    Source Data Worksheet column--- AB------- AC row----category-----Model

    -----------------------------------------------

    1 -----ppppppp 2 ------ppppppp 3 ------ppppppp 4------Apple--------xx------xxxxxxx 5------Orange-------cc------ccccccc 6------Orange-------yy------yyyyyyy 7 ------yyyyyyy 8------Manggo-------yy------yyyyyyy 9------Manggo-------yy------yyyyyyy 10-----Mangoo-------yy------yyyyyyy arget worksheet row----category-----Model----SKU-------Category--------day1----day2----day3----total ------------------------------------------------------------------------------------

    1-------Apple--------aa 2-------Apple--------aa 3-------Apple--------aa 4-------Apple--------aa 5-------Orange-------cc 6-------Orange-------cc 7-------Orange-------cc 8-------Manggo-------yy 9-------Manggo-------yy 10------Mangoo-------yy 11------Mangoo-------yy 12------Mangoo-------yy ---last not empty records, insert the 6 records after this part. ----------------------------------------------------- Total|Total| -----------------------------------------------------


    Wednesday, September 17, 2014 9:25 AM
  • Actually, it's really hard for us to understand. Why you need to insert and then copy, I suggest you provide a screenshot of the result which you want to achieve instead of the just the target data above.

     
    Wednesday, September 17, 2014 9:35 AM
  • >>the issue here is when copying a filtered data there's a rows that were hide in
    between of row 8 and 10. this copied only the from row 5 to 8 only but the rest
    from 10 to 15 was not copied.

    I think @Luna's second reply has answered this issue, using lrow in your code is incorrect.

     

    Wednesday, September 17, 2014 9:44 AM
  • Herewith attached sample screen of destination/target worksheet. the data from sourcedata worksheet will be added or inserted dynamically at the rows 31 where that arrow pointed. hope this is clear now. thank you.

    Source Data Worksheet
    
    column--- AB------- AC
    row----category-----Model-----------------------------------------------1		       -----ppppppp			
    2                      ------ppppppp
    3                     ------ppppppp
    4------Apple--------xx------xxxxxxx
    5------Orange-------cc------ccccccc
    6------Orange-------yy------yyyyyyy
    7                       ------yyyyyyy
    8------Manggo-------yy------yyyyyyy
    9------Manggo-------yy------yyyyyyy
    10-----Mangoo-------yy------yyyyyyy
    

    Thursday, September 18, 2014 12:26 AM
  • Solved.
    Saturday, September 20, 2014 2:28 AM
  • Hi,

    Glad to know that you have solved the issue, would you mind sharing your sample code in the forum so that other communities which have the same requirement will learn from you?

    Thanks.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 22, 2014 5:38 AM
    Moderator