excel vba error the sort reference is not valid

December 6, 2023

I am running my macro:

Worksheets("LP").Range("D:D").Copy
Worksheets("LP").Range("C:C").PasteSpecial Paste:=xlPasteValues

Worksheets("LP").Range("C1:C31").Sort Key1:=Range("C1:C31"), Order1:=xlAscending, Header:=xlNo

…
Sorting is giving me an error saying that the reference is not valid. I named the proper worksheet instead of active one to avoid errors and it is a sheet without headers as also indicated. I do not see were the mistake is, if somebody could help, this are just few lines of the start of a larger macro so I need them to continue.

I checked the worksheets to see if the error was on the data, but everything is fine and the same within the code. Unless something has changed since last year in the nomenclature of the functions, I do not see the error. I wrote the code not recorded, then there is no issue with changing ranges inside the data.

>Solution :

Try:

with Worksheets("LP")
  .Range("D:D").Copy
  .Range("C:C").PasteSpecial Paste:=xlPasteValues
  .Range("C1:C31").Sort Key1:=.Range("C1:C31"), _
            Order1:=xlAscending, Header:=xlNo
end with