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

Hi Ron

Thanks for the correction.

The macro is working the way that I would like, except for one thing. When
it runs, I am always prompted to rename the sheet manually. The new sheet is
always Master Event ((3), the date that I input is not being implemented.

Is the input box expecting an actual date format? I thought it would just
take any text

Here is my code:

Sub CopyPrint()
Dim DateText As String

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

If Trim(DateText) = "" Then Exit Sub

With Worksheets("Master Event Calc.")
.Copy After:=Sheets("Master Event Calc.")

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.PrintPreview
ActiveSheet.Visible = False
.Select
End With
End Sub


It's great except for the naming issue!

--
Thanks for the brainwaves!

CJ
I blame the parents........


"Ron de Bruin" wrote:

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

No worries Ron, I think I have found the problem.

The name of the sheet is too long. When I try to put in the long form of the
date, the macro has trouble.....an easy fix.

Thanks again for all of your assistance!

Cheers
CJ

"CJ" wrote in message
...
Hi Ron

Thanks for the correction.

The macro is working the way that I would like, except for one thing. When
it runs, I am always prompted to rename the sheet manually. The new sheet
is
always Master Event ((3), the date that I input is not being implemented.

Is the input box expecting an actual date format? I thought it would just
take any text

Here is my code:

Sub CopyPrint()
Dim DateText As String

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

If Trim(DateText) = "" Then Exit Sub

With Worksheets("Master Event Calc.")
.Copy After:=Sheets("Master Event Calc.")

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.PrintPreview
ActiveSheet.Visible = False
.Select
End With
End Sub


It's great except for the naming issue!

--
Thanks for the brainwaves!

CJ
I blame the parents........


"Ron de Bruin" wrote:

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

Note you can not use a / in a file name

Format your date with a -

--

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


"CJ" wrote in message ...
No worries Ron, I think I have found the problem.

The name of the sheet is too long. When I try to put in the long form of the
date, the macro has trouble.....an easy fix.

Thanks again for all of your assistance!

Cheers
CJ

"CJ" wrote in message
...
Hi Ron

Thanks for the correction.

The macro is working the way that I would like, except for one thing. When
it runs, I am always prompted to rename the sheet manually. The new sheet
is
always Master Event ((3), the date that I input is not being implemented.

Is the input box expecting an actual date format? I thought it would just
take any text

Here is my code:

Sub CopyPrint()
Dim DateText As String

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

If Trim(DateText) = "" Then Exit Sub

With Worksheets("Master Event Calc.")
.Copy After:=Sheets("Master Event Calc.")

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.PrintPreview
ActiveSheet.Visible = False
.Select
End With
End Sub


It's great except for the naming issue!

--
Thanks for the brainwaves!

CJ
I blame the parents........


"Ron de Bruin" wrote:

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