Getting Power Query data to an Excel worksheet

I'm having a bit of an issue with an Excel power query and VBA.

In VBA:
I create a new sheet in my workbook and name it.

I get my data from a CSV file with the information I need into a power query in my workbook. I can see the query in the workbook and view the data, everything is there. It is also a connection only query. I don't need to worry about refreshing the data because each week the CSV file has a different name. Refreshing is irrelevant.

Here is my issue. I want to use VBA code to take all the data in the query, all columns and all rows, and import it into the newly created sheet with the same formatting as if I was doing this all manually via the menu commands. Headers, Rows, shading, etc. Menu command: Data | From CSV | Choose CSV file | Load. Doing it manually creates a sheet with the name of the CSV file with, in my case, green shadings.

Once I have it in a sheet, then the rest of my script will do some data manipulation, I.e., converting customer ID# to customer name, delete a few columns, and create and format a pivot table and pivot chart.

Everything is working fine except putting the data from the query into a sheet.

Any help would be appreciated.

Thank you
Michael.