Conditionally Import cells to another workbook

[MtrxTch1760]

Hi, I'm fairly new to making macros and need some guidance in how to create a macro to import data based upon a given condition is set.

Sample Data:

Task Days Budget Invoice Engineer
C14 9 1000 500 John
C15 2 1000 500 Mike
C16 0 1000 0 Chris
C17 7 1000 500 Tom

The boss wants certain data imported into a new workbook to be sent to our accounting. However, he only wants the following data, from Tasks that have been work on for more than 0 days.

Sample after macro:

Task Budget Invoice
C14 1000 500
C15 1000 500
C17 1000 500

If this is possible any and all help would be greatly appreciated. I am using Excel 2003, and running on Windows XP.

 

[Sektor]

Sub FFFFFF()

    Dim i As Long, arr As Variant, book As Workbook, sh As Worksheet
    
    Application.ScreenUpdating = False

    ' Get data without header.
    With Range("A1")
        arr = .CurrentRegion.Offset(1, 0).Resize(.Rows.Count - 1)
    End With
    
    ' Create new workbook.
    Set book = Workbooks.Add
    Set sh = book.Worksheets(1)
    
    With sh
        For i = 1 To UBound(arr)
            If arr(i, 2) > 0 Then
                .Cells(i, "A") = arr(i, 1)
                .Cells(i, "B") = arr(i, 3)
                .Cells(i, "C") = arr(i, 4)
            Next
        Next
    End With

    Application.ScreenUpdating = True

End Sub

 

[VoG]

This should be a start: change the bits in red to suit

Sub Acopy()
Dim LR As Long, i As Long
With Workbooks("Main").Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(1).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("A1")
    For i = 2 To LR
        If .Range("B" & i).Value > 0 Then
            .Range("A" & i).Resize(, 4).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next i
End With
ThisWorkbook.Sheets("Sheet1").Columns("B").Delete
End Sub

 

[MtrxTch1760]

Thank you Vog that worked perfectly.

 

[MtrxTch1760]

I have came accross one more problem that I was hoping you would be able to help me with. I am using the code VoG provided, with the variables modified to match my sheet. The code works great, but I am now on a sheet, where the information that I pull into the workbook is referencing another sheet so I get the reference null in my cells. How do I modify the code to pull just the value displayed and not the formula and reference behind it?

 

[VoG]

Try

Sub Acopy()
Dim LR As Long, i As Long
With Workbooks("Main").Sheets("Sheet1")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(1).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("A1")
    For i = 2 To LR
        If .Range("B" & i).Value > 0 Then
            .Range("A" & i).Resize(, 4).Copy
            ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
End With
ThisWorkbook.Sheets("Sheet1").Columns("B").Delete
End Sub

 

[MtrxTch1760]

When using that code, I'm getting a Compile Error. Expected: End of Statement on the line
ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues


My apologies I made an error in the typing that, my co-worker and I missed. We are correcting it and trying it again now.

 

[MtrxTch1760]

Once again you have saved the day VoG. I owe you big.