here ya go, create multiple sheets and name them as you go, with the range option for transferspreadsheet.
Code:
Private Sub Command94_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim gg As String
sdt = Format(start_date, "dd-mm-yy")
edt = Format(End_date, "dd-mm-yy")
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
aa = MsgBox(" Do you also require Manaul Sheets ?", vbYesNoCancel + vbDefaultButton2, "Time Sheet Additions")
If aa = vbYes Then
CC = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
bb = InputBox("Enter number of lines per sheet required", "Manual Time Sheet creation", 1)
Else
If aa = vbCancel Then Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("create excel time sheets for selected employees on main menu")
With rs
.MoveFirst
Do While Not .EOF
fn = rs.Fields("First Name")
Ln = rs.Fields("Last Name")
cd = rs.Fields("Code #")
bc = rs.Fields("barcode")
Me.barcode = bc
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "print time sheets for selected employees", gg, True, fn & " " & Ln & " " & cd
DoEvents
.MoveNext
Loop
End With
If aa = vbYes Then
DoCmd.RunSQL "DELETE * FROM manual_sheet"
t = Forms![main menu].start_date
For i = 1 To bb
Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
rstActual.AddNew
rstActual!new_date = t
rstActual.Update
rstActual.Close
t = t + 1
Next i
nRecords = DCount("*", "Employee Time Report Output with lunch for manual sheet")
If nRecords = 0 Then
MsgBox "No data for selected period, change report dates and try agian.", vbOKOnly, "Error"
Exit Sub
End If
For i = 1 To CC
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
Next i
End If
rs.Close
Set rs = Nothing
Set db = Nothing
Call format_sheets_now
t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & " And saved in the following directory" & vbCrLf & vbCrLf & " C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
End Sub '***********************************************************************************************************************************************
Private Sub Command147_Click() 'create manual time sheet
Dim db As DAO.Database
Set db = CurrentDb
Dim rstActual As DAO.Recordset
Set rstActual = db.OpenRecordset("manual_sheet")
gg = "C:\aaa\timesheets\Employee Time Report master.xls"
bb = InputBox("Enter number of sheets required", "Excel time sheet generation", 1)
aa = InputBox("Enter number of lines per sheet required", "Manual Time Sheet creation", 1)
fn = "FirstName"
Ln = "LastName"
DoCmd.RunSQL "DELETE * FROM manual_sheet"
t = Forms![main menu].start_date
For i = 1 To aa
Set rstActual = db.OpenRecordset("manual_sheet", dbOpenDynaset)
rstActual.AddNew
rstActual!new_date = t
rstActual.Update
rstActual.Close
t = t + 1
Next i
For i = 1 To bb
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Employee Time Report Output with lunch for manual sheet", gg, True, "FirstName" & i & " " & "LastName" & i
Next i
Call format_sheets_now
t = MsgBox("All requested Excel Time Sheets have been created" & vbCrLf & " And saved in the following directory" & vbCrLf & vbCrLf & " C:\aaa\timesheets", vbOKOnly, "Automated Time Sheet Generation")
End Sub