Copying 3 columns to another worksheet

[m_in_spain]

Hi again, alas more help is needed!
I am trying to copy selected data from one sheet to another, but only if there is data in column A on the origin sheet.
If there is data in column A and B, then i copy A to C to my destination sheet.
This part, from scratching around on the internet i have managed. Next I need to do the same task over a few more sheets, but copying it to the next available row on my destination sheet.
I have managed to get this to work, but it will only copy column A, I have tried every way i can think of to extend the second (and subsequent) to cover columns A,B & C but i draw blank.
I am quite sure this is possible, and for some with be simple, unfortunately for me it is I who is simple!
Story So far:

Sub CollateUsed()
Dim sIn As Worksheet, sOut As Worksheet, rIn As Range, rOut As Range
Dim inputdata() As Variant
Dim tmpArr(1 To 3) As Variant
Dim i As Long, outcount As Long
Set sIn = Sheets("TRmech")
Set sOut = Sheets("ProjOutput")
Set rIn = sIn.Range("A5:C64")
'Set rIn = sIn.UsedRange
Set rOut = sOut.Range("A1:C1")
inputdata = rIn.Value
outcount = 0
'Reads data from inputdata Array and prints selected values from columns A, B, and C on Output sheet row by row.
For i = 1 To UBound(inputdata, 1)
    If inputdata(i, 1) <> "" Then
    If inputdata(i, 2) <> "" Then
    If inputdata(i, 1) = "" Then End
        outcount = outcount + 1
        tmpArr(1) = inputdata(i, 1)
        tmpArr(2) = inputdata(i, 2)
        tmpArr(3) = inputdata(i, 3)
        rOut.Offset(outcount - 1, 0).Value = tmpArr
        Erase tmpArr
    End If
    End If
Next i
Erase inputdata
'==================DO IT ALL AGAIN DIFFERENT INPUT SHEET
Set sIn = Sheets("TRelec")
Set sOut = Sheets("ProjOutput")
Set rIn = sIn.Range("A5:C64")
'Set rIn = sIn.UsedRange
Set rOut = sOut.Range("A" & Rows.Count).End(xlUp).Offset(1)
inputdata = rIn.Value
outcount = 0
'Reads data from inputdata Array and prints selected values from columns A, B, and C on Output sheet row by row.
For i = 1 To UBound(inputdata, 1)
    If inputdata(i, 1) <> "" Then
    'If inputdata(i, 2) <> "" Then
    If inputdata(i, 1) = "" Then End
        outcount = outcount + 1
        tmpArr(1) = inputdata(i, 1)
        'tmpArr(2) = inputdata(i, 2)
        'tmpArr(3) = inputdata(i, 3)
        rOut.Offset(outcount - 1, 0).Value = tmpArr
        Erase tmpArr
    End If
    'End If
Next i
Erase inputdata
End Sub

Any guidance greatly appreciated

 

[m_in_spain]

sorry! see.. you are helping the stupid!

=IF(fuel_type=hybrid,"Vivek Verbal Quotation 24JULU 2017","")

I have many many such formulae as this is a workbook that prepares costs and quotations depending on what is selected. In this instance the fuel_type hybrid was not selected, and therefore the cell shows as blank

 

[Fluff]

With that formula there should be no problem, as the code only looks at the value which is "" & should therefore be ignored.

 

[Trixterz]

Maybe use Trim to remove any spaces around the string that may cause a false reading...

If Trim(Ary(r, 1)) <> "" And Trim(Ary(r, 2)) <> "" Then

 

[m_in_spain]

Hi again,
Thanks, both for suggestions.
I have about 30 worksheets, about 50% have data in A5, the remaining 50% are all blank due to the formula similar to =IF(fuel_type=hybrid,"Vivek Verbal Quotation 24JULU 2017","") as noted before.
If i only add the sheets with visible data in A5 your code works like a dream, thanks.
Adding any of the sheets with invisible data causes this to fall over.

I do have a front sheet that in columnA calls for a result from specific work pages =TRmech!$A$1, and a column B with a quantity. It happens that when there is no data in other sheets cell A5, the quantity in column B of the front sheet will always be 0.
Therefore my next task is to find a way to read down column A of the front sheet, if there is a 1 in column B to somehow extract the sheet name from column A and write it to the code line Shtary = Array("TRmech", "TRelec", ........

There are known knowns; they are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unkown unknowns. it is the latter category that tend to be the difficult ones.

 

[Fluff]

What do you mean by "Adding any of the sheets with invisible data causes this to fall over."?

 

[m_in_spain]

Where i have Shtary = Array("TRmech", "TRelec" ) i have added more work sheets which worked fine...
Shtary = Array("TRmech", "TRelec", "TRrefr", "afterburn", "AirGas",) were all fine and all had visible data in cell A5, then i added the next sheet "OxyFuel" to give
Shtary = Array("TRmech", "TRelec", "TRrefr", "afterburn", "AirGas", "OxyFuel") all fine up to and including "AirGas", then as soon at it got to the "OxyFuel" it errors, this was the first sheet with no visible data in A5.
I carried on adding my sheets and can add any, so long as they have visible data in A5, any without this cause the code to run time error in code line: WsOut.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, 3).Value = Nary

 

[Fluff]

In that case just use

      If nr > 0 Then WsOut.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(nr, 3).Value = Nary

 

[m_in_spain]

Your status has been elevated from Saint to God!
Many, many thanks
It worked!

 

[Fluff]

You're welcome & thanks for the feedback.