Our company database generates a CSV list of a given client's expenses and payments, and I am trying to create a Macro that will use the CSV to create invoices for our clients.
The CSV is formatted so that the list of payments appears a few rows below the list of expenses, both of which may vary in length. The columns in each section also represent different things (e.g. column B starts off showing the client surname in the expense section, and payment date down in the payment section). Because of this, the macro must not hard-code the start and end positions of each range when operating on them (which it does when I simply record it).
One portion of code is designed to sort the payments by date:
Dim PaymentsTable As Range
Set PaymentTable = ActiveSheet.Cells(LastRow, 1).CurrentRegion
Dim PaymentDate As Range
Set PaymentDate = PaymentTable.Columns(2)
PaymentDate.Interior.ColorIndex = 3
PaymentTable.Sort Key1:=Range("PaymentDate"), Order1:=xlAscending, Header:=xlYes
The program can correctly identify column 2, as texted in the penultimate line (which colours it red). However, the final line always generates the following error:
"Method 'Range' of object '_Global' failed."
I can remove this error by giving an absolute cell location in Key1 (e.g. "A1").
Why am I getting this error? How can I alter my code to remove it? Any help would be greatly appreciated!