VBA run time error 1004

[rilezJ]

I have the following code in MS Access. And I think something is getting weird defining between Access and Excel (I am looping through a subform, using the variable as a criteria in a filter on tables, and then pasting the table into excel, where I am making charts.) So I have no problem pasting values into excel, but When I try formatting it, I run into trouble. So, this code was actually working before, and I added some variables (See the first 4) to count the rows to select as the data source for the chart. Now, I am getting a run time error 1004 when arriving at the highlighted line. Please provide some feedback. Thanks!

VBA.PNG

[millz]

What happens if you replace code starting from .SortFields.Clear with this?

Please Login or Register  to view this content.

I am assuming your table starts from Column A, hence .Columns(3) -> (C) for the sort key.

[rilezJ]

Thanks, Millz. I am now bypassing the object defined error, but my column(3) is not sorting. So the command has no syntax error but is not functioning. Any suggestions on how to tweak your code?

[millz]

What if you run this code directly from Excel? Does it sort the data in the table? Maybe changing Header parameter to xlNo?

I am purely guessing, I can't really imagine what's wrong without looking at anything. I tried the code directly in Excel, on a table and it works.

[rilezJ]

Thanks for your persistence Millz, it was a "communication" issue between access and excel. So (I feel really stupid) I just added criteria to the $ field in my query and dumped into excel that way, allowing me to delete all these steps!! What a waste of time! Thanks again.