| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
CJ
Guest
Posts: n/a
|
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
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:4682c307$(E-Mail Removed)... > 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 > > |
|
||
|
|
||||
|
CJ
Guest
Posts: n/a
|
Hi,
We need to keep a copy of each sheet for records. "Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message news:4682c307$(E-Mail Removed)... >> 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
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:4682fb53$(E-Mail Removed)... > Hi, > > We need to keep a copy of each sheet for records. > > "Ron de Bruin" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message news:4682c307$(E-Mail Removed)... >>> 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 > > |
|
||
|
|
||||
|
CJ
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message news:4682fb53$(E-Mail Removed)... >> Hi, >> >> We need to keep a copy of each sheet for records. >> >> "Ron de Bruin" <(E-Mail Removed)> wrote in message >> news:(E-Mail Removed)... >>> 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" <(E-Mail Removed)> wrote in message >>> news:4682c307$(E-Mail Removed)... >>>> 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
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:46843d2a$(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news:(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message news:4682fb53$(E-Mail Removed)... >>> Hi, >>> >>> We need to keep a copy of each sheet for records. >>> >>> "Ron de Bruin" <(E-Mail Removed)> wrote in message >>> news:(E-Mail Removed)... >>>> 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" <(E-Mail Removed)> wrote in message >>>> news:4682c307$(E-Mail Removed)... >>>>> 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 >>> > |
|
||
|
|
||||
|
=?Utf-8?B?Q0o=?=
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:46843d2a$(E-Mail Removed)... > > 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" <(E-Mail Removed)> wrote in message > > news:(E-Mail Removed)... > >> 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" <(E-Mail Removed)> wrote in message news:4682fb53$(E-Mail Removed)... > >>> Hi, > >>> > >>> We need to keep a copy of each sheet for records. > >>> > >>> "Ron de Bruin" <(E-Mail Removed)> wrote in message > >>> news:(E-Mail Removed)... > >>>> 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" <(E-Mail Removed)> wrote in message > >>>> news:4682c307$(E-Mail Removed)... > >>>>> 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 > >>> > > > |
|
||
|
|
||||
|
CJ
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:0EB6F426-2C83-4D59-8CF4-(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message >> news:46843d2a$(E-Mail Removed)... >> > 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" <(E-Mail Removed)> wrote in message >> > news:(E-Mail Removed)... >> >> 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" <(E-Mail Removed)> wrote in message >> >> news:4682fb53$(E-Mail Removed)... >> >>> Hi, >> >>> >> >>> We need to keep a copy of each sheet for records. >> >>> >> >>> "Ron de Bruin" <(E-Mail Removed)> wrote in message >> >>> news:(E-Mail Removed)... >> >>>> Why not change the footer or header with the sheet name + date and >> >>>> 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" <(E-Mail Removed)> wrote in message >> >>>> news:4682c307$(E-Mail Removed)... >> >>>>> 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
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in message news:468a6040$(E-Mail Removed)... > 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" <(E-Mail Removed)> wrote in message > news:0EB6F426-2C83-4D59-8CF4-(E-Mail Removed)... >> 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" <(E-Mail Removed)> wrote in message >>> news:46843d2a$(E-Mail Removed)... >>> > 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" <(E-Mail Removed)> wrote in message >>> > news:(E-Mail Removed)... >>> >> 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" <(E-Mail Removed)> wrote in message >>> >> news:4682fb53$(E-Mail Removed)... >>> >>> Hi, >>> >>> >>> >>> We need to keep a copy of each sheet for records. >>> >>> >>> >>> "Ron de Bruin" <(E-Mail Removed)> wrote in message >>> >>> news:(E-Mail Removed)... >>> >>>> Why not change the footer or header with the sheet name + date and >>> >>>> 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" <(E-Mail Removed)> wrote in message >>> >>>> news:4682c307$(E-Mail Removed)... >>> >>>>> 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 >>> >>> >>> > >>> > > |
|
||
|
|
||||
|
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Complex Copy | ryguy7272 | Microsoft Excel Programming | 6 | 13th Aug 2009 09:24 PM |
| macro to copy/paste print area | SteveDB1 | Microsoft Excel Programming | 5 | 29th Aug 2008 04:26 PM |
| Converstion of Complex Word VBA macro to Excel Macro | Michael | Microsoft Excel Programming | 1 | 18th Jan 2008 12:21 AM |
| Any way to copy contents of print area with macro? | uwajes | Microsoft Excel Programming | 1 | 23rd Jun 2006 05:29 PM |
| macro to copy all print settings to all sheets | rbanks | Microsoft Excel Programming | 4 | 14th Mar 2006 05:06 PM |
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc. |
