Exporting multiple files

[JT]

I am using the following code to export data for multiple profit centers to a
file (TargetFile). This works great and it runs the query (Create Sheets)
for each profit center.

Do While Counter < LastRecord Or Counter = LastRecord

DoCmd.TransferSpreadsheet acExport, 8, "Create Sheets", TargetFile
DoCmd.GoToRecord acDataForm, "Group", acNext
vGroup = Forms![Group]![Group]
Counter = Counter + 1

Loop

The problem is it keeps exporting the data to the same same sheet in the
TargetFile, so I'm left with the data for the last profit center on a sheet
called "Create Sheets".

What I would like to do is add the data to a sheet in TargetFile and rename
it with the vGroup variable (below). I think then it will keep adding
separate sheets for each profit center.

Any suggestions or ideas on how to accomplish this would be appreciated.
Thanks for all of the help.

 

[Klatuu]

Although Access Help says you can't use a range name for an export, it does
work, at least in AC2003, I don't know about prior versions. The True means
the first row is field names, It that is not the case, change it to false.

Do While Counter <= LastRecord

DoCmd.TransferSpreadsheet acExport, 8, "Create Sheets", TargetFile, _
True, VGroup
DoCmd.GoToRecord acDataForm, "Group", acNext
vGroup = Forms![Group]![Group]
Counter = Counter + 1

Loop