Hi All,

I have developed a monthly timesheet workbook with a worksheet for each
week. All workers will use this and as each week is completed, i want them to
run a macro to copy the CURRENT WORKSHEET to a supervisor's workbook. The
super's book will then have that week from all workers in one location so he
can just open his book and see all the worker's weeks.

In their worksheets, the worker's names are in cell B1 so it would be really
nice if the copies of the worker's worksheets were renamed to the name in
cell B1.

Thanks in advance for any help or suggestions.

Sammy

I wouldn't have the workers opening a supervisors workbook. the better way
of doing this is to have a macro in each workers worksheet that saves the
files in a directory. then have the supervisor run a macro to gether or the
time sheets in his workbook.

You can set up weekly folders in adavance and then have the workers save the
file into the correct weekly directory based on the date.

"sammy" wrote:

Hi All,

I have developed a monthly timesheet workbook with a worksheet for each
week. All workers will use this and as each week is completed, i want them to
run a macro to copy the CURRENT WORKSHEET to a supervisor's workbook. The
super's book will then have that week from all workers in one location so he
can just open his book and see all the worker's weeks.

In their worksheets, the worker's names are in cell B1 so it would be really
nice if the copies of the worker's worksheets were renamed to the name in
cell B1.

Thanks in advance for any help or suggestions.

Sammy

Hi Joel,

Thanks for the very quick response. And i like the idea of just copying the
worksheets to a directory but how can i copy only one worksheet from a book
with several sheets?

Sammy

"Joel" wrote:

I wouldn't have the workers opening a supervisors workbook. the better way
of doing this is to have a macro in each workers worksheet that saves the
files in a directory. then have the supervisor run a macro to gether or the
time sheets in his workbook.

You can set up weekly folders in adavance and then have the workers save the
file into the correct weekly directory based on the date.

The little tricks that most people don't know. if yo do a copy without using
before or after a new workbook is created.

ActiveSheet.Copy
Set newbk = ActiveWorkbook 'this is the copied sheet with one worksheet
newbk.SaveAs Filename:="c:\temp\book1.xls"



"sammy" wrote:

Hi Joel,

Thanks for the very quick response. And i like the idea of just copying the
worksheets to a directory but how can i copy only one worksheet from a book
with several sheets?

Sammy

"Joel" wrote:

I wouldn't have the workers opening a supervisors workbook. the better way
of doing this is to have a macro in each workers worksheet that saves the
files in a directory. then have the supervisor run a macro to gether or the
time sheets in his workbook.

You can set up weekly folders in adavance and then have the workers save the
file into the correct weekly directory based on the date.

Okay, now i'm learning something. But after more consideration, i think i
want all the sheets from all workers from one week to be copied into the same
book. Is it possible to check if a target book exists and if not create it?
This way the super has a week to rename this repository then the first worker
attempting to copy the next week's sheet will actually create a new
repository. Of course, if the repository book does exist, the worker's sheet
will just be copied into it.

"Joel" wrote:

The little tricks that most people don't know. if yo do a copy without using
before or after a new workbook is created.

ActiveSheet.Copy
Set newbk = ActiveWorkbook 'this is the copied sheet with one worksheet
newbk.SaveAs Filename:="c:\temp\book1.xls"

You can use the DIR() function.

Folder = "C:\"
BookName = "book1.xls"
Fname = dir(Folder & BookName)
if FName = "" then
set newbk = workbooks.add
else
set newbk = workbooks.open(Folder & BookName)
end if


My prefernce to this problem is to have a seperate workbook for each
employee for 1 calander year. Each week will be a different worksheet which
will be added each week. This way when a supervisor need to review an
employees times sheets in is all in one workbook. The supervisor workbook
will have a macro to open and copy all the employees workbooks and copy the
latest worksheet to his book for the summary.

The problem is a workbook can only be opened for writing by one person. if
two people try to write at one time one of the employees will get a READ ONLY
copy of the workbook and won't be able to save the information. I also like
to set up a different directory for each department when you have more than
one supervisor.
"sammy" wrote:

Okay, now i'm learning something. But after more consideration, i think i
want all the sheets from all workers from one week to be copied into the same
book. Is it possible to check if a target book exists and if not create it?
This way the super has a week to rename this repository then the first worker
attempting to copy the next week's sheet will actually create a new
repository. Of course, if the repository book does exist, the worker's sheet
will just be copied into it.

"Joel" wrote:

The little tricks that most people don't know. if yo do a copy without using
before or after a new workbook is created.

ActiveSheet.Copy
Set newbk = ActiveWorkbook 'this is the copied sheet with one worksheet
newbk.SaveAs Filename:="c:\temp\book1.xls"

Hi Joel,

I really appreciate your help with this. I tried to implement your code,
both old and new, but it doesn't work. I'm sure when i added the snippets
together it is causing it to fail. This is what i have. Will you please help
me some more?

Sub EndWeek()

ActiveSheet.Copy

Folder = "C:\junk\"
BookName = "book1.xls"
FName = Dir(Folder & BookName)

If FName = "" Then
Set newbk = Workbooks.Add
Else
Set newbk = Workbooks.Open(Folder & BookName)
End If

Set newbk = ActiveWorkbook 'this is the copied sheet with one worksheet
newbk.SaveAs Filename:=FName
newbk.Close

End Sub

"Joel" wrote:


You can use the DIR() function.

Folder = "C:\"
BookName = "book1.xls"
Fname = dir(Folder & BookName)
if FName = "" then
set newbk = workbooks.add
else
set newbk = workbooks.open(Folder & BookName)
end if


My prefernce to this problem is to have a seperate workbook for each
employee for 1 calander year. Each week will be a different worksheet which
will be added each week. This way when a supervisor need to review an
employees times sheets in is all in one workbook. The supervisor workbook
will have a macro to open and copy all the employees workbooks and copy the
latest worksheet to his book for the summary.

The problem is a workbook can only be opened for writing by one person. if
two people try to write at one time one of the employees will get a READ ONLY
copy of the workbook and won't be able to save the information. I also like
to set up a different directory for each department when you have more than
one supervisor.

The code worked if the file existed and didn't work when the file wasn't there.

Just change 1 statement

newbk.SaveAs Filename:=BookName

The old code had FName instead of BookName.

"sammy" wrote:

Hi Joel,

I really appreciate your help with this. I tried to implement your code,
both old and new, but it doesn't work. I'm sure when i added the snippets
together it is causing it to fail. This is what i have. Will you please help
me some more?

Sub EndWeek()

ActiveSheet.Copy

Folder = "C:\junk\"
BookName = "book1.xls"
FName = Dir(Folder & BookName)

If FName = "" Then
Set newbk = Workbooks.Add
Else
Set newbk = Workbooks.Open(Folder & BookName)
End If

Set newbk = ActiveWorkbook 'this is the copied sheet with one worksheet
newbk.SaveAs Filename:=FName
newbk.Close

End Sub

"Joel" wrote:


You can use the DIR() function.

Folder = "C:\"
BookName = "book1.xls"
Fname = dir(Folder & BookName)
if FName = "" then
set newbk = workbooks.add
else
set newbk = workbooks.open(Folder & BookName)
end if


My prefernce to this problem is to have a seperate workbook for each
employee for 1 calander year. Each week will be a different worksheet which
will be added each week. This way when a supervisor need to review an
employees times sheets in is all in one workbook. The supervisor workbook
will have a macro to open and copy all the employees workbooks and copy the
latest worksheet to his book for the summary.

The problem is a workbook can only be opened for writing by one person. if
two people try to write at one time one of the employees will get a READ ONLY
copy of the workbook and won't be able to save the information. I also like
to set up a different directory for each department when you have more than
one supervisor.

Thanks Joel, that gets it done.

"Joel" wrote:

The code worked if the file existed and didn't work when the file wasn't there.

Just change 1 statement

newbk.SaveAs Filename:=BookName

The old code had FName instead of BookName.

"sammy" wrote:

Hi Joel,

I really appreciate your help with this. I tried to implement your code,
both old and new, but it doesn't work. I'm sure when i added the snippets
together it is causing it to fail. This is what i have. Will you please help
me some more?

Sub EndWeek()

ActiveSheet.Copy

Folder = "C:\junk\"
BookName = "book1.xls"
FName = Dir(Folder & BookName)

If FName = "" Then
Set newbk = Workbooks.Add
Else
Set newbk = Workbooks.Open(Folder & BookName)
End If

Set newbk = ActiveWorkbook 'this is the copied sheet with one worksheet
newbk.SaveAs Filename:=FName
newbk.Close

End Sub

"Joel" wrote:


You can use the DIR() function.

Folder = "C:\"
BookName = "book1.xls"
Fname = dir(Folder & BookName)
if FName = "" then
set newbk = workbooks.add
else
set newbk = workbooks.open(Folder & BookName)
end if


My prefernce to this problem is to have a seperate workbook for each
employee for 1 calander year. Each week will be a different worksheet which
will be added each week. This way when a supervisor need to review an
employees times sheets in is all in one workbook. The supervisor workbook
will have a macro to open and copy all the employees workbooks and copy the
latest worksheet to his book for the summary.

The problem is a workbook can only be opened for writing by one person. if
two people try to write at one time one of the employees will get a READ ONLY
copy of the workbook and won't be able to save the information. I also like
to set up a different directory for each department when you have more than
one supervisor.