Fantastic thanks so much!
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:Code
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?
I would suggest automating AutoFilterCode
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
I'm sorry I don't understand. Do I add this to the other code or use in place of the other code?
i dont im afraid - im brand new to VBA
do i post this code within the other code?
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?
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
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
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.Code
Sub CopyData() Dim x, y(), i As Long, ii As Long, iii As Long x = Sheets("COPY FROM").Cells(1).CurrentRegion For i = 1 To UBound(x, 1) If x(i, 1) <> 0 Then iii = iii + 1: ReDim Preserve y(1 To UBound(x, 2), 1 To iii) For ii = 1 To UBound(x, 2) y(ii, iii) = x(i, ii) Next End If Next With Sheets("FINAL") .Cells.Clear .[a1].Resize(iii, UBound(y, 1)) = Application.Transpose(y) End With End Sub
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.