Transfer data from sheet1 to sheet2

- - Latest reply: ac3mark
Posts
12780
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 8, 2019
- Jul 26, 2019 at 06:46 PM
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
See more 

2 replies

Posts
12780
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 8, 2019
1223
0
Thank you
Try this:


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


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!



Respond to ac3mark
0
Thank you
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
ac3mark
Posts
12780
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 8, 2019
1223 -
On what line?
it's not clear which line just give me this error
ac3mark
Posts
12780
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 8, 2019
1223 -
If you press, DEBUG, it will highlight the line it is stopped on.....and the error says what is wrong.......





So do you have blank items items in the Cells of the range that is highlighted? If not, it will throw an error!

If you are definingg the range with hard code, then you do not need to find the last row, as you are already defining it!


This will still throw 1004 error, but it is what you were attempting (I think!).

Sub save()
ThisWorkbook.Worksheets("sheet2").Activate
Range("a75:a88").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub


You are defining a sheet, but not doing anything with it! You never reference SS again! So I am confused as to why it is even there.....
abdelfatah_0230
Posts
1
Registration date
Thursday July 18, 2019
Status
Member
Last seen
July 26, 2019
-
i know how i can find the error in line but the window what shows is not like your picture thats why it doesn't allow me where the exact the errors and still continue the error and i change the code to help me where is the error and correct me because i'm newer in vba
ac3mark
Posts
12780
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
August 8, 2019
1223 -
OK. Are there values in the cells that are highlighted? I can duplicate the error when there are no duplicates within the range that happens to be selected. It is just the return of NO DUPLICATES. Do this:

1. clear the sheet that you are copying to.
2. Make the sheet that you want to transfer have focus
3. Press RUN.
4. post the feedback (as I only get this error, after I have ALREADY REMOVED the DUPLICATES)
Respond to alhagag