How to create multiple worksheets in a loop

[astharaj]

Hi,

I want to create multiple worksheet in a query loop. I also need to add data in those sheets depending on the query values.

For example-
1) I have a template with one sheet and a format like Name and Address.
2) I run a query in access (from where we are going to call the excell application and populate the report) to find all the names and address from a table in the database - suppose we get 3 rows.
3) Then I will need to create 3 sheets in the report with one Name and Address in each (first name n first sheet; second in second etc etc.)

Is it possible?? How do i do that. Till now I am able to create a sheet and copy the format in it

Set objSht = objWkb.Worksheets("Sheet1")
objSht.Activate

Set objShtP = objWkb.Worksheets.Add(After:=ActiveSheet)
ActiveSheet.NAME = "Test"

objSht.Range("A1:M30").Copy
objShtP.Cells(1, 1).PasteSpecial

But I don't know if I will need seperate object (objShtP) for all additional sheets in order put the data in it - and if yes, how do I do that.

Thanks in advance,
Astha