Inserting subform/Table data into specific fields in Excel

[Sam Summers]

Your code shows MsgBox Error$ in the error handler, which suggests the error handler isn't being reached.
Perhaps the error is in Excel rather than Access?

Try changing that line to:

MsgBox "Error " & Err.Number & " in SaveAndNewBtn_Click procedure: " & Err.Description

What error message do you see now?

I've downloaded your file but have no idea which button to click to trigger this behaviour
I don't have time to look at your db properly now but suggest you tell us what exactly to do for the error to occur
Help us to help you

Tried that and now I get the message attached.

I'll take another look with the debugger

 

[KitaYama]

oBook
oSheet(Me.SelectedFile).Offset(0, 5).Value.CopyFromRecordset rs 'Copy recs to sheet

• rs is empty. You haven't set it yet.
• Offset should be set against a range not a sheet. oSheet.Range("A1").Offset(0,5)
• the first line (oBook). You can't use an object like this.
• As advised by @June7 Disable error handler for debugging. Only implement after code works properly.
• While testing, don't set warning to false (DoCmd.SetWarnings False)

 

[Gasman]

Walk through your code line by line. That will tell you what line is causing the error.