transfer data from sheet1 to sheet2 Closed

Question

Hello,   i have   data  in sheet1 from  range a1: f91   when i fill data   and transfer to sheet2  it also transfer the empty rows  here i wanna delete the rows   what contains  cells  from a75: a88  just in  specific  one condition cells a =" "   then delete the rows  and without effect the total value from a89:a91 

the used my code is: 
Sub TransferData()
    ' 20 Jun 2019

    Dim Target As Range
    Dim LastRow As Long
    Dim R As Long

    LastRow = sheet1.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
    Set Target = sheet2.Cells(sheet2.Rows.Count, "A").End(xlUp)
    For R = 2 To LastRow
        sheet1.Range(sheet1.Cells(R, 1), sheet1.Cells(R, 6)).Copy _
                Destination:=Target.Offset(R - 1)
        With Target.Offset(R - 1, 4)
            If .HasFormula Then .Value = sheet1.Cells(R, 5).Value
        End With
    Next R

    Target.Offset(LastRow, 5).Value = sheet2.Range("E91").Value
End Sub

ac3mark · Answer

Try this:

ThisWorkbook.Worksheets("Sheet2").ActivateLastRow = Sheet2.Cells(Sheet1.Rows.Count, "A").End(xlUp).RowRange("A2", "A" & LastRow).SelectSelection.SpecialCells(xlCellTypeBlanks).SelectSelection.Delete Shift:=xlUp 

Place that just before the End Sub.

As for not moving the end cells, you may need to record that value into a variable, then place that variable back down into the cell you wish to have it, after all of the deletes.  As a deleted cell needs to go away, or you will just be deleting the contents, and therefor not accomplishing anything!

alhagag · Answer

i do this code  and give me the error   run-time  1004   application- defined  or object  defined error
Sub save()
Dim ss As Range
Set ss = Worksheets("sheet2").Range("a75:a88")
ThisWorkbook.Worksheets("sheet2").Activate
LastRow = sheet2.Cells(sheet1.Rows.Count, "A").End(xlUp).Row
Range("A2", "A" & LastRow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

Transfer data from sheet1 to sheet2

2 responses

Similar discussions

Subscribe To Our Newsletter!