Previous Topic Next Topic

Transfer data from sheet1 to sheet2

Closed
Report
-
 Blocked Profile -
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

2 replies

Answer 1 / 2

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!



Answer 2 / 2

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
Report
Blocked Profile
On what line?
Report

it's not clear which line just give me this error
Report
Blocked Profile
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.....
Report
Posts
72
Registration date
Thursday July 18, 2019
Status
Member
Last seen
November 3, 2021
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
Report
Blocked Profile
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)
Ask a question