vba - copy data from one sheet to another due to condition

Question

I am very new to vba (and code in general) so I apologise if I haven't asked the right question or have missed a thread that covers this. I have spent a couple of weeks trying to find the answer so hopefully you may be able to help.

I am trying to copy, data from one sheet (Named Master Sheet) to another depending on a variable in column L (Variables "In Progress" or "Not Started") to an Overview / GUI sheet. My current code (below) does this for the first line of data, however I would like this to work for the whole sheet.Unfortunately it will have a changing amount of data added so the array will be expanding- unsure how much more difficult this will make it.

Thank you very much for any help you can provide, and I apologise for the marked out notes. I can add a picture too (if possible) if it would help make more sense of what I would like to do?

Sub Update_Uncompleted_Tasks()
' Update_Uncompleted_Tasks Macro
' Selects tasks from Master Sheet and copies to the Overview Sheets if    assigned as uncompleted

'DON'T USE BELOW YET (UNSURE IF IT WILL WORK)
'Maybe Vlookup?
'Dim LastRow As Long, i As Long
'LastRow = Cells(Rows.Count, "L").End(xlUp).Row
'For i = 1 To LastRow

Sheets("Master Sheet").Select
    If Range("L2") = "In Progress" Then
        Range("A2:L2").Select
        Selection.Copy
        Sheets("Overview").Select
        Application.Goto Reference:="R10000C2"
        Selection.End(xlUp).Select
        Selection.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,   SkipBlanks _
            :=False, Transpose:=False
    ElseIf Range("L2") = "Not Started" Then
        Range("A2:L2").Select
        Selection.Copy
        Sheets("Overview").Select
        Application.Goto Reference:="R10000C2"
        Selection.End(xlUp).Select
        Selection.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If

'Next i

End Sub

Can you provide snapshot of your worksheets? – Zac Oct 07 '16 at 10:17 — Zac, Oct 07 '16 at 10:17

score 0 · Answer 1 · answered Oct 07 '16 at 10:38

You can use this piece of code to get the number of last nonempty line on your sheet:

Dim LastDataLine As Long
LastDataLine = Sheets("Master Sheet").Range("A" & Rows.Count).End(xlUp).Row

And for last non-empty column (just in case):

Dim LastDataCol As Integer
LastDataCol = Sheets("Master Sheet").Cells(1, Columns.Count).End(xlToLeft).Column

If you also need help implementing a loop which goes through each line leave a comment.

score 0 · Accepted Answer · edited May 23 '17 at 12:16

First of all you should take a look at this: Avoid using select in Excel-VBA-Macros.

The following code should fulfill your needs, but it requires that your data in column L has no empty cells (until the end is reached)

Sub Update_Uncompleted_Tasks()
  Dim row as Long

  'Initial value
  row = 2

  With ThisWorkbook.Worksheets("Master Sheet")
    Do Until IsEmpty(.Range("L" & row))
      If (.Range("L" & row).Value = "In Progress") Or (.Range("L" & row).Value = "Not Started") Then
        .Range("A" & row & ":L" & row).Copy
        ThisWorkbook.Worksheets("Overview").Range("B10000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
      End If
      row = row + 1
    Loop
  End With
End Sub

The loop ends when a row is reached where the cell in column L is empty.

EDIT: You can also replace the Do Until -- Loop with a For row=2 To lastRow -- Next row. To determine the last row of your data, there are many ways, check out this link Excel-VBA find last row or just use the search function.

vba - copy data from one sheet to another due to condition

2 Answers2