Copy a sheet from today into a sheet for tomorrow

[singlgl1]

I'd love to have this automated with some code if possible and appreciate any help with it:

I have a file structure set up as follows:
Folders for each month:
H:\Morning Reports 2011\Jul
Within each folder, daily files named
7-1-11.xls, and so on for the entire month
Within each file, there is a sheet called “Production” and “Tommorow’s”.
At the end of each day I save the current file,copy the “tomorrows” sheet of the current day, open the file for the following day, Paste the clipboard contents( previously copied) into the “Production” sheet and save,then close. I then close the file from the previous day.
I need to answer No to all update prompts, and yes to any save prompts.
Thanks again for sharing your expertise..

 

[singlgl1]

Here is my current code:

Sub Carryover()
'
' Carryover Macro
'

'
Sheets("TOMORROW'S").Select
Cells.Select
Selection.Copy
Cells.Select
ChDir ActiveWorkbook.Path
a = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Workbooks.Open Filename:=a
Sheets("Production").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


I’m getting this error:
Compile Error with Sheets ("TOMORROW'S").Select highlighted.I'm having problems determining what the fix is. Any ideas?

 

[John Davis]

Here is my current code:

Sub Carryover()
'
' Carryover Macro
'

'
Sheets("TOMORROW'S").Select
Cells.Select
Selection.Copy
Cells.Select
ChDir ActiveWorkbook.Path
a = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Workbooks.Open Filename:=a
Sheets("Production").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


I’m getting this error:
Compile Error with Sheets ("TOMORROW'S").Select highlighted.I'm having problems determining what the fix is. Any ideas?

Maybe Change Sheets("TOMORROW'S") to Sheets("TOMORROWS"). Just a guess.

 

[singlgl1]

Thanks for the reply John;
Changed the actual sheet names to "Tomorrow" and also changed in the code to "Tomorrow".Still no luck.

 

[John Davis]

Thanks for the reply John;
Changed the actual sheet names to "Tomorrow" and also changed in the code to "Tomorrow".Still no luck.

I was just taking a guess. The apostrophe is used to make comments. Are you still getting a compile error on that line?

 

[singlgl1]

Yes, I am. Thanks