Posts by hjulian72

    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

    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").Value = "amount"
    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 would suggest automating AutoFilter

    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


    thanks for the suggestion - the first tab is the amalgamated data from other sheets.

    i want to just take the non zero rows (the ones with a unit price) into the export sheet (xero export)

    at the moment the VBA sees the account codes and the amount columns as data, and copies them over (ie copies all the rows rather than just the ones with a unit price).

    many thanks for your help :)

    hi KjBox

    many thanks for coming back so fast. Unfortunately that won't work for me in this instance. I have a table with multiple rows and columns, i want to copy across just the rows that contain certain complete columns (containing data), but i also need the account code that is in the D row. So the VBA needs to ignore the account code column for the calculation but carry the account code into the new sheet.

    the way you have suggested would mean the rows would be selected but without the account code, and that would need to be entered manually afterwards.

    is the a line i can add just ignoring a specific column?

    many thanks for your help and ideas

    Try this.

    Note that your sample FINAL sheet had a trailing space in the tab name ("FINAL " instead of "FINAL"), make sure you remove that trailing space.

    Hi there

    thanks so much for posting this. This is my first time using Visual Basic and your code works well. Except, I have one column that is account numbers and so those lines keep being copied to the new sheet. Is there a minor change I can make to deflect that column please? In my case it column D (4th column).

    Many thanks for any help you can give.