help adjusting code delete empty rows based on condition from sheet1 to sheet2

[maghari]

hello
i would some help about adjusting code when transfer data of invoice from sheet1 to sheet2 the sheet1 contains some empty rows when i transfer i don't want transfer the empty rows to sheet2 only filling the data in sheet1 begins from range a3:e31 the empty rows should delete from a19:e30 if the cells a19: a30= empty i make attention the range from a19:e30 don't delete in sheet1 because i need them i don't want in sheet2 when i transfer and here the notes i would a31: e31 connect with the last filling row value for instance the last filling row value in range a22:e22 =200
then the a31:e31=200 i know it this complicated qustion i hope this help to solve my problem and this is my code :



Sub TransferData()
Dim x As Integer

Dim Target As Range
Dim LastRow As Long
Dim R As Long
For x = 30 To 19 Step -1
If sheet2.Range("A" & x).Value = vbNullString Then
sheet2.Range("A" & x).EntireRow.Delete
Next x
LastRow = sheet1.Cells(.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, 5)).Copy _
Destination:=Target.Offset(R - 2) With Target.Offset(R - 2, 4)
If .HasFormula Then .Value =sheet1 .Cells(R, 5).Value
End With
Next R
On Error Resume Next
Target.Offset(R - 3, 5).Value = WorksheetFunction.Sum(sheet2.Range("E3:E35"))End Sub

[SamT]

Same code reformatted

Sub TransferData()
Dim x As Integer
Dim Target As Range
Dim LastRow As Long
Dim R As Long

For x = 30 To 19 Step -1
If sheet2.Range("A" & x).Value = vbNullString Then _
sheet2.Range("A" & x).EntireRow.Delete
Next x

LastRow = sheet1.Cells(.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, 5)).Copy _
Destination:=Target.Offset(R - 2)

With Target.Offset(R - 2, 4)
If .HasFormula Then .Value =sheet1 .Cells(R, 5).Value
End With
Next R

On Error Resume Next
Target.Offset(R - 3, 5).Value = WorksheetFunction.Sum(sheet2.Range("E3:E35"))

End Sub

[SamT]

Option Explicit

Sub TransferData()
Dim TargetRow As Long
Dim LastRow As Long
Dim Rw As Long
Dim rSource As Range

LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
TargetRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row

For Rw = 2 To LastRow
Set rSource = Sheet1.Range(Sheet1.Cells(Rw, 1), Sheet1.Cells(Rw, 5))
If rSource.Cells(1) <> "" Then
rSource.Copy
Sheet2.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteValues
TargetRow = TargetRow + 1
End If
Next Rw
End Sub

[maghari]

Same code reformatted

Sub TransferData()
Dim x As Integer
Dim Target As Range
Dim LastRow As Long
Dim R As Long

For x = 30 To 19 Step -1
If sheet2.Range("A" & x).Value = vbNullString Then _
sheet2.Range("A" & x).EntireRow.Delete
Next x

LastRow = sheet1.Cells(.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, 5)).Copy _
Destination:=Target.Offset(R - 2)

With Target.Offset(R - 2, 4)
If .HasFormula Then .Value =sheet1 .Cells(R, 5).Value
End With
Next R

On Error Resume Next
Target.Offset(R - 3, 5).Value = WorksheetFunction.Sum(sheet2.Range("E3:E35"))

End Sub

thanks for your replying it' long time to reply someone my post published 3 days ago i expect for you or anyone to help my problem

[maghari]

Option Explicit

Sub TransferData()
Dim TargetRow As Long
Dim LastRow As Long
Dim Rw As Long
Dim rSource As Range

LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
TargetRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row

For Rw = 2 To LastRow
Set rSource = Sheet1.Range(Sheet1.Cells(Rw, 1), Sheet1.Cells(Rw, 5))
If rSource.Cells(1) <> "" Then
rSource.Copy
Sheet2.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteValues
TargetRow = TargetRow + 1
End If
Next Rw
End Sub



thanks your code really work but not as i want i would transfer data the same formating table
a i attache my file to understand more


https://ufile.io/39xbgskh

[maghari]

i hope this file help anyone to understand it

[SamT]

Option Explicit

Sub TransferData()
Dim TargetRow As Long
Dim LastRow As Long
Dim Rw As Long
Dim rSource As Range

LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
TargetRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row

For Rw = 2 To LastRow
Set rSource = Sheet1.Range(Sheet1.Cells(Rw, 1), Sheet1.Cells(Rw, 5))
If rSource.Cells(1) <> "" Then
rSource.Copy
Sheet2.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteValues
Sheet2.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteFormats
TargetRow = TargetRow + 1
End If
Next Rw
End Sub

[maghari]

Option Explicit

Sub TransferData()
Dim TargetRow As Long
Dim LastRow As Long
Dim Rw As Long
Dim rSource As Range

LastRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
TargetRow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row

For Rw = 2 To LastRow
Set rSource = Sheet1.Range(Sheet1.Cells(Rw, 1), Sheet1.Cells(Rw, 5))
If rSource.Cells(1) <> "" Then
rSource.Copy
Sheet2.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteValues
Sheet2.Cells(TargetRow, "A").PasteSpecial Paste:=xlPasteFormats
TargetRow = TargetRow + 1
End If
Next Rw
End Sub



it does work code but it still simple problems some data of cells it doesn't show in sheet2

date and order no and value i no know why? you can check my file and tell me what happen

[maghari]

i'm really sorry sam your code is perfect the problem is in my attached file i tested another workbook is succeeding

[SamT]

:thumb