Set up variables for each sheet and initialise them e.g.
Dim wsReportLucy As Worksheet, wsReportAmir As Worksheet
Set wbReport = excelApp.Workbooks.Add
Set wsReportLucy = wbReport.ActiveSheet
wsReportLucy.Name = "Lucy"
Set wsReportAmir = wbReport.Sheets.Add
wsReportAmir.Name = "Amir"
With wsReportLucy
.Cells(2, 2).Value = "NAME"
.Cells(2, 3).Value = "ORDER"
.Cells(2, 4).Value = "PRICE"
.Rows(4).Font.Bold = True
.Rows(4).Font.Color = vbBlue
End With
With wsReportAmir
.Cells(2, 2).Value = "NAME"
.Cells(2, 3).Value = "ORDER"
.Cells(2, 4).Value = "PRICE"
.Rows(4).Font.Bold = True
.Rows(4).Font.Color = vbBlue
End With
Have a counter of rows for each sheet e.g.
Dim intRowLucy As Integer, intRowAmir As Integer
intRowLucy = 6
intRowAmir = 6
Then just check the name as you loop through the recordset e.g.
Do While Not rsReport.EOF
If rsReport!Name = "Amir" Then
intRowAmir = WriteData(wsReportAmir, wsReportAmir, intRowAmir)
ElseIf rsReport!Name = "Lucy" Then
intRowAmir = WriteData(wsReportLucy, wsReportLucy, intRowLucy)
End If
rsReport.MoveNext
Loop
Where I have created a small function to handle actually writing the data e.g.
Private Function WriteData(rs As ADODB.Recordset, ws As Worksheet, intRow As Integer) As Integer
With ws
.Cells(intRow, 2).Value = Trim(rsReport!Name)
.Cells(intRow, 3).Value = Trim(rsReport!Order)
.Cells(intRow, 4).Value = Trim(rsReport!Price)
End With
WriteData = intRow + 1
End Function