If criteria meet copy and paste to specific rows

Question

I'm having trouble with my code: Here is the thing, if column H = "Yes" then copy cell content from row "A" to another sheet on row "A", but starting at the first row.

The problem is: I want to paste the content to the first empty row after my header which is in cell "A7" not to the corresponding row from where the criteria meet. Also if there is a way to avoid blank cells between rows after copy and paste to organise them.

Option Explicit

Private Sub CommandButton1_Click()

'submacro which copies data over columns
    Dim lastRow As Long, i As Long
    'determine last row in column H
    lastRow = Cells(Rows.Count, "H").End(xlUp).Row
    For i = 1 To lastRow


        'if Yes in H then copy from cell B in OFCE to cell B in DASHBOARD in    current row
        If Worksheets("OFCE").Cells(i, "H").Value = "Yes" Then
        'To paste from Dashboard tab to OFCE tab
            Worksheets("Dashboard").Cells(i, "A").Value = Worksheets("OFCE").Cells(i, "A").Value
        End If
    Next


End Sub

After this i'll have to transfer not only one row, but many others.

Thanks for sharing your code. can you add an image of what your sheet looks like. I'm having a hard time following your problem. — Miguel, Feb 15 '19 at 16:32
No need to loop :) Use Autofilter as shown [Here](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) — Siddharth Rout, Feb 15 '19 at 16:58

Error 1004 · Accepted Answer · 2019-02-15T20:28:31.027

0

I m not sure if i have fully understand your needs but this may help:

Option Explicit

Private Sub CommandButton1_Click()

        Dim LastRowH As Long, i As Long, LastRowA As Long

LastRowH = Worksheets("OFCE").Cells(Worksheets("OFCE").Rows.Count, "H").End(xlUp).Row

For i = 1 To LastRowH

    If Worksheets("OFCE").Cells(i, "H").Value = "Yes" Then

        LastRowH = Worksheets("Dashboard").Cells(Worksheets("Dashboard").Rows.Count, "A").End(xlUp).Row

        Worksheets("Dashboard").Cells(LastRowH + 1, 1).Value = Worksheets("OFCE").Cells(i, 1).Value

    End If

Next i

End Sub

edited Feb 15 '19 at 20:28

answered Feb 15 '19 at 18:01

Error 1004

7,877
3
23
46

Hello Error1004, this is almost there but the problem is that now i receive only one corresponding row, not all of them! Could you please help me out? – Dark Seer Feb 15 '19 at 19:50
It worked just fine!! Thank you so much, you are a genius! Now i am trying to find a way to stop copying again the same values to the following rows when i run the code again, would you know a way to do this? Thanks!! – Dark Seer Feb 16 '19 at 22:45
Glad to here that i help. If the answer is what you are looking for vote for it to help others. Could you please upload a pieces of your raw data and desirable outcome? – Error 1004 Feb 17 '19 at 12:44
i've uploaded two pictures of how it looks right now, if you could please support me again on this – Dark Seer Feb 17 '19 at 16:51

score 0 · Answer 2 · answered Feb 17 '19 at 16:50

0

The button update runs the code from you @Error1004 but now i need not only to transfer but to check and update the transfered data without repeating entries just adding the new ones that meet criteria, but it is copying all over again to the following rows as you can see in the images below from the sheet OFCE where the data comes from and the second one where the data is being copied and pasted.

first tab

second tab with repeated data

answered Feb 17 '19 at 16:50

Dark Seer

7
4

Based on which column/s you want to check if a record exists and which columns to update? – Error 1004 Feb 17 '19 at 21:01
Sorry by update i mean that it should look in the first tab and copy the value from cells we've described on the code, and paste on the second tab. This is working fine but if i run the code again i want it to check if there are new rows with corresponding values to the criteria (Column H in first tab = "Yes") and then copy and paste to the second tab not to copy everything again, as in the screenshot, do you know? – Dark Seer Feb 17 '19 at 21:41
One possible solution is to create a unique KEY which represents each row, pass it one variable, loop the KEY column in OFCE sheet to check if this key appears and update the column you want, if not just import the line as it is. – Error 1004 Feb 19 '19 at 08:55

If criteria meet copy and paste to specific rows

2 Answers2