Hi Groupies.

I have a request from a client that is a little over my head. Please lend me
your brain cells for a while!

The request that I have received, and some explanation, is as follows:

"I want the users to be able to save a copy of each event as different
sheets in the workbook, named by the date of the event. For each event, I
need to make a copy of the master, with all the input data and a copy of the
printout sheet which contains the data from the master."

So let's say they push a button and a message box pops up and asks for
the event date. When they enter the data (example: June 27) and hit OK, a
sheet is created that is a copy of the sheet, which is currently called
Master Event Calculation, as well as a copy of the sheet which is currently
called Printout Sheet. So now we would have Event Calculation June 27 and
Printout June 27.

"If they were to click on any sheet and hit the print button, it would print
the information corresponding to that specific event date from the
corresponding printout sheet."

So, the user clicks on the sheet called Event Calculation June 27,
pushes a print button, either on the sheet or a custom toolbar, and the
Printout June 27 sheet prints.

Why not just click on the Printout June 27 sheet and print it??? The users
are only allowed to see the Event sheets, everything else in this workbook
needs to be hidden and protected.

Can somebody please help me with the code for this macro?

Thanks
CJ

Why not change the footer or header with the sheet name + date and print
Or do you have another reason that you want to make a copy of the sheets ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Hi Groupies.

I have a request from a client that is a little over my head. Please lend me
your brain cells for a while!

The request that I have received, and some explanation, is as follows:

"I want the users to be able to save a copy of each event as different
sheets in the workbook, named by the date of the event. For each event, I
need to make a copy of the master, with all the input data and a copy of the
printout sheet which contains the data from the master."

So let's say they push a button and a message box pops up and asks for
the event date. When they enter the data (example: June 27) and hit OK, a
sheet is created that is a copy of the sheet, which is currently called
Master Event Calculation, as well as a copy of the sheet which is currently
called Printout Sheet. So now we would have Event Calculation June 27 and
Printout June 27.

"If they were to click on any sheet and hit the print button, it would print
the information corresponding to that specific event date from the
corresponding printout sheet."

So, the user clicks on the sheet called Event Calculation June 27,
pushes a print button, either on the sheet or a custom toolbar, and the
Printout June 27 sheet prints.

Why not just click on the Printout June 27 sheet and print it??? The users
are only allowed to see the Event sheets, everything else in this workbook
needs to be hidden and protected.

Can somebody please help me with the code for this macro?

Thanks
CJ

Hi,

We need to keep a copy of each sheet for records.

"Ron de Bruin" wrote in message
...
Why not change the footer or header with the sheet name + date and print
Or do you have another reason that you want to make a copy of the sheets ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Hi Groupies.

I have a request from a client that is a little over my head. Please lend
me your brain cells for a while!

The request that I have received, and some explanation, is as follows:

"I want the users to be able to save a copy of each event as different
sheets in the workbook, named by the date of the event. For each event, I
need to make a copy of the master, with all the input data and a copy of
the printout sheet which contains the data from the master."

So let's say they push a button and a message box pops up and asks for
the event date. When they enter the data (example: June 27) and hit OK, a
sheet is created that is a copy of the sheet, which is currently called
Master Event Calculation, as well as a copy of the sheet which is
currently called Printout Sheet. So now we would have Event Calculation
June 27 and Printout June 27.

"If they were to click on any sheet and hit the print button, it would
print the information corresponding to that specific event date from the
corresponding printout sheet."

So, the user clicks on the sheet called Event Calculation June 27,
pushes a print button, either on the sheet or a custom toolbar, and the
Printout June 27 sheet prints.

Why not just click on the Printout June 27 sheet and print it??? The
users are only allowed to see the Event sheets, everything else in this
workbook needs to be hidden and protected.

Can somebody please help me with the code for this macro?

Thanks
CJ

Hi CJ

The basic code looks like this
Example for one sheet

copy the sheet
Name it
print it
hide it

Sub test()
Dim DateText As String

DateText = Application.InputBox("Enter date here", _
"Date", , , , , 2)

If Trim(datatext) = "" Then Exit Sub

With Worksheets("Master Event Calculation")
.Copy after:=Sheets(.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = .Name & " " & DateText
If Err.Number 0 Then
MsgBox "Change the name of : " & ActiveSheet.Name & " manually"
Err.Clear
End If
On Error GoTo 0
ActiveSheet.PrintOut
ActiveSheet.Visible = xlVeryHidden
.Select
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Hi,

We need to keep a copy of each sheet for records.

"Ron de Bruin" wrote in message
...
Why not change the footer or header with the sheet name + date and print
Or do you have another reason that you want to make a copy of the sheets ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Hi Groupies.

I have a request from a client that is a little over my head. Please lend
me your brain cells for a while!

The request that I have received, and some explanation, is as follows:

"I want the users to be able to save a copy of each event as different
sheets in the workbook, named by the date of the event. For each event, I
need to make a copy of the master, with all the input data and a copy of
the printout sheet which contains the data from the master."

So let's say they push a button and a message box pops up and asks for
the event date. When they enter the data (example: June 27) and hit OK, a
sheet is created that is a copy of the sheet, which is currently called
Master Event Calculation, as well as a copy of the sheet which is
currently called Printout Sheet. So now we would have Event Calculation
June 27 and Printout June 27.

"If they were to click on any sheet and hit the print button, it would
print the information corresponding to that specific event date from the
corresponding printout sheet."

So, the user clicks on the sheet called Event Calculation June 27,
pushes a print button, either on the sheet or a custom toolbar, and the
Printout June 27 sheet prints.

Why not just click on the Printout June 27 sheet and print it??? The
users are only allowed to see the Event sheets, everything else in this
workbook needs to be hidden and protected.

Can somebody please help me with the code for this macro?

Thanks
CJ

Awesome, thanks for the code Ron.

I will try it this evening, hopefully, and let you know how it goes.

Cheers
CJ

"Ron de Bruin" wrote in message
...
Hi CJ

The basic code looks like this
Example for one sheet

copy the sheet
Name it
print it
hide it

Sub test()
Dim DateText As String

DateText = Application.InputBox("Enter date here", _
"Date", , , , , 2)

If Trim(datatext) = "" Then Exit Sub

With Worksheets("Master Event Calculation")
.Copy after:=Sheets(.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = .Name & " " & DateText
If Err.Number 0 Then
MsgBox "Change the name of : " & ActiveSheet.Name & " manually"
Err.Clear
End If
On Error GoTo 0
ActiveSheet.PrintOut
ActiveSheet.Visible = xlVeryHidden
.Select
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Hi,

We need to keep a copy of each sheet for records.

"Ron de Bruin" wrote in message
...
Why not change the footer or header with the sheet name + date and print
Or do you have another reason that you want to make a copy of the sheets
?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message
...
Hi Groupies.

I have a request from a client that is a little over my head. Please
lend me your brain cells for a while!

The request that I have received, and some explanation, is as follows:

"I want the users to be able to save a copy of each event as different
sheets in the workbook, named by the date of the event. For each event,
I need to make a copy of the master, with all the input data and a copy
of the printout sheet which contains the data from the master."

So let's say they push a button and a message box pops up and asks
for the event date. When they enter the data (example: June 27) and hit
OK, a sheet is created that is a copy of the sheet, which is currently
called Master Event Calculation, as well as a copy of the sheet which
is currently called Printout Sheet. So now we would have Event
Calculation June 27 and Printout June 27.

"If they were to click on any sheet and hit the print button, it would
print the information corresponding to that specific event date from
the corresponding printout sheet."

So, the user clicks on the sheet called Event Calculation June 27,
pushes a print button, either on the sheet or a custom toolbar, and the
Printout June 27 sheet prints.

Why not just click on the Printout June 27 sheet and print it??? The
users are only allowed to see the Event sheets, everything else in this
workbook needs to be hidden and protected.

Can somebody please help me with the code for this macro?

Thanks
CJ

There is a typo in the code i see now

If Trim(datatext) = "" Then Exit Sub

Must be

If Trim(DateText) = "" Then Exit Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Awesome, thanks for the code Ron.

I will try it this evening, hopefully, and let you know how it goes.

Cheers
CJ

"Ron de Bruin" wrote in message
...
Hi CJ

The basic code looks like this
Example for one sheet

copy the sheet
Name it
print it
hide it

Sub test()
Dim DateText As String

DateText = Application.InputBox("Enter date here", _
"Date", , , , , 2)

If Trim(datatext) = "" Then Exit Sub

With Worksheets("Master Event Calculation")
.Copy after:=Sheets(.Parent.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = .Name & " " & DateText
If Err.Number 0 Then
MsgBox "Change the name of : " & ActiveSheet.Name & " manually"
Err.Clear
End If
On Error GoTo 0
ActiveSheet.PrintOut
ActiveSheet.Visible = xlVeryHidden
.Select
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message ...
Hi,

We need to keep a copy of each sheet for records.

"Ron de Bruin" wrote in message
...
Why not change the footer or header with the sheet name + date and print
Or do you have another reason that you want to make a copy of the sheets
?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CJ" wrote in message
...
Hi Groupies.

I have a request from a client that is a little over my head. Please
lend me your brain cells for a while!

The request that I have received, and some explanation, is as follows:

"I want the users to be able to save a copy of each event as different
sheets in the workbook, named by the date of the event. For each event,
I need to make a copy of the master, with all the input data and a copy
of the printout sheet which contains the data from the master."

So let's say they push a button and a message box pops up and asks
for the event date. When they enter the data (example: June 27) and hit
OK, a sheet is created that is a copy of the sheet, which is currently
called Master Event Calculation, as well as a copy of the sheet which
is currently called Printout Sheet. So now we would have Event
Calculation June 27 and Printout June 27.

"If they were to click on any sheet and hit the print button, it would
print the information corresponding to that specific event date from
the corresponding printout sheet."

So, the user clicks on the sheet called Event Calculation June 27,
pushes a print button, either on the sheet or a custom toolbar, and the
Printout June 27 sheet prints.

Why not just click on the Printout June 27 sheet and print it??? The
users are only allowed to see the Event sheets, everything else in this
workbook needs to be hidden and protected.

Can somebody please help me with the code for this macro?

Thanks
CJ