Consulting

Results 1 to 3 of 3

Thread: Copy to the latest blank row

  1. #1
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location

    Copy to the latest blank row

    I have this VBA code for copy my data from sheet2 to Sheet 1 , but the problem is on
    lastrowDest = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
    that mean if i do not have any data on sheet1 ,it will be add a blank row on sheet1 but if i have data on sheet1 it will not have any problem , so i need to add something to the below code that if the sheet1 is empty do not paste the data with a one blank row.

    Please modify the below code.

    Thank you very much

      Dim LastRow As Long
    
        lastrowSrc = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
         
         'Get first blank row (last row of data +1)
        lastrowDest = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
         
         'Copy row
        Sheets("Sheet2").Range("A1:E" & lastrowSrc).EntireRow.Copy Sheets("Sheet1").Range("A" & lastrowDest)

  2. #2
    Loads of various ways around this... You could do a IF statement.

    Dim LastRow As Long  
    lastrowSrc = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row 
     
     'Get first blank row (last row of data +1)
    lastrowDest = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1 
     
     'Copy row
    If Sheets("Sheet1").Range("A1").Value = "" Then
       Sheets("Sheet2").Range("A1:E" & lastrowSrc).EntireRow.Copy Sheets("Sheet1").Range("A1")
       Else
       Sheets("Sheet2").Range("A1:E" & lastrowSrc).EntireRow.Copy Sheets("Sheet1").Range("A" & lastrowDest)
    End If

  3. #3
    VBAX Mentor
    Joined
    Feb 2012
    Posts
    406
    Location
    Thank you very much

Posting Permissions

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