Complex Copy/Print Macro

[CJ]

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

 

[Ron de Bruin]

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 ?

 

[CJ]

Hi,

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

 

[Ron de Bruin]

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

 

[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]

There is a typo in the code i see now

If Trim(datatext) = "" Then Exit Sub

Must be

If Trim(DateText) = "" Then Exit Sub

 

[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!

 

[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

 

[Ron de Bruin]

Note you can not use a / in a file name

Format your date with a -