Copying rows from one sheet to another excluding "empty" rows

hi KJBox

it works but it doesn't carry across the amounts for unit price - it makes them all 1

what should i change so it carries the unit price across please?

many thanks

@ hjulian72

You gave earlier the indication to skip Column D ... which KjBox did respect in his code ...

If you know how to handle Arrays ... you can separate out this column and modify as follows :

           ' Deal with First 2 Columns
               For ii = 1 To 2
                   y(ii, iii) = x(i, j)
               Next ii
           ' Deal with Third Column
               If ii = 3 Then y(ii, iii) = x(i, ii + 1)
           ' Deal with the Last three Columns 5 to 7
               For ii = 5 To UBound(x, 2)
                   y(ii - 1, iii) = x(i, ii)
               Next ii

Hope this will help

thanks carim

i dont im afraid - im brand new to VBA

do i post this code within the other code?

many thanks

Quote from royUK

I would suggest automating AutoFilter

Code

Option Explicit

Sub CopyFiltered()
Dim lRw As Long
    With Sheet4
        If Not .AutoFilterMode Then .Range("A1").AutoFilter
        .Range("A1").AutoFilter Field:=4, Criteria1:=">0"
        With Sheet5
        lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        End With
        .AutoFilter.Range.Resize(, 2).Copy Sheet5.Cells(lRw, 1)
        .Range("A3").AutoFilter
    End With

End Sub

Display More

hi Roy

I'm sorry I don't understand. Do I add this to the other code or use in place of the other code?

many thanks

Julian

Quote from hjulian72

thanks carim

i dont im afraid - im brand new to VBA

do i post this code within the other code?

many thanks

Display More

Hello Julian,

KjBox' s macro is attached to your test file ...

His Copy macro is using Arrays for maximum efficiency ...

By the way ... standard macros have to be stored in a standard module ... NOT in the worksheet's module ....

Hope this will help

Once you have tested the macro ... feel free to share your comments

Quote

it works but it doesn't carry across the amounts for unit price - it makes them all 1

I am totally confused, your example shows exactly what my macro will produce. You ask that column D values should not be included in the result, column D is Unit Price, now you are saying that all Unit Prices in the result are 1 (in fact Amount is 1) and you now want Unit Price included in the result.

Why don't you attach a file where you manually create the Xero Export sheet so we know exactly what the required result should be.

thanks so much for your help - its worked

however, there is no amount column so I'm having to add back in an amount column (xero needs an amount and a unit price)

ive added a separate macro (in a new module) on the export sheet which adds a column with the heading "amount" but I can't work out how to add the number 1 to the column below

this is what ive added:

Sub UpdateColumns()
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("C1").EntireColumn.Insert
Range("C1").Value = "amount"
 Range("C2").Copy
Range("C2:C" & LastRow).PasteSpecial (xlPasteAll)
End Sub

is there a way i can add this to the original macro so it takes the populated lines to the export sheet and automatically adds the amount column containing the number 1?

many thanks

I assume you mean Carim's modification of my code works for you.

Now you are saying you need the Amount column included in the result data? Is that correct?

hi kjbox

your code has been briliiant thank-you and its helped my understand this a bit more

ive always needed it as the accounts import requires 1 x XXXX for each line

i couldnt find a way of copying the unpopulated lines across but keeping the amount column - so ive added a separate macro on the exported data page to add that column but i cant work out how to add "1" to each cell in that column

Try the attached

Fantastic thanks so much!

You're welcome.