printing range of dates

[WayneCa]

Using the description above does not help me find an answer, as it is not quite adequate to describe the issue. Allow me to elaborate.

I want to print a worksheet n times with each print containing the next date in a series of dates. For example, the first print out contains in a cell the date November 7, 2014, the next print out is November 8, 2014 and so on, for the number of copies of the sheet I specified. It is for a sign-in sheet that I want to have sequentially dated, and will be printed one month of sheets at a time.

Is there a formula or a built-in function for this, or do I have to write one?

41 views and not a single reply. I guess I need to include that I do not know how to write such a formula, and I'm not sure I can write a function for it. I understand Visual Basic syntax, but not where Excel functions are concerned.

[bmouse]

Unless you have actually created the sheets for every date in your workbook, I don't think you can print them out of "thin air" so to speak, formulas and VBA don't work that way.

[HaHoBe]

Hi, WayneCa,

from what I understand the request should be to augment the date after printing.

Code may look like this (Range for the cell with the date has to be adapted to suit):

Please Login or Register  to view this content.

Ciao,
Holger

[bmouse]

Didn't expect something like this in the Basics section

[WayneCa]

Hi, WayneCa,

from what I understand the request should be to augment the date after printing.

Code may look like this (Range for the cell with the date has to be adapted to suit):

Please Login or Register  to view this content.

Ciao,
Holger

Thanks for the function, Holger. However I am having a problem with using the function. I created the module, and then attempted to insert the function name into the cell. I find no user defined module list in the functions, and Excel is reporting a bad name error. How do I get Excel to recognize the function?

Wayne

[WayneCa]

Didn't expect something like this in the Basics section

Hi bmouse. I am new to this forum, and did not know where else to post my question. I am grateful that I got a good response instead of just being told the proper place to post it.

[HaHoBe]

Hi, WayneCa,

the code is Sub which needs to be started one way or another but defenitely not a Function which would feature that word instead of sub and should return some kind of value.

Please have a look especially at the second part of the quote:

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

HTH,
Holger

[WayneCa]

Hi, WayneCa,

the code is Sub which needs to be started one way or another but defenitely not a Function which would feature that word instead of sub and should return some kind of value.

Please have a look especially at the second part of the quote:

HTH,
Holger

Thanks, Holger. I did the first part. I did not think about sub(routine) and func(tion) not being the same. I was in too much of a hurry. Thanks for spelling it out for me. I am going to give it a try now. I do have one question (which I assume will be answered in my test print). If the cell specified has been merged into adjacent cells, so that the whole date appears instead of #####, does that necessitate changing the sub to reflect the first cell in the merge?

Thanks again,
Wayne

[WayneCa]

Well, the first trial run didn't work as expected, but it did run. I am not familiar with all of the changes to how VB works in Excel, so I am not sure exactly what is going on. When I ran the sub, it began sending print instructions, but I had not opened the print dialog to select the number of copies I wanted to print. I had to get back into the macro dialog to cancel it, and by the time it reopened, about 15 print commands had been issued. I was able to cancel most of them, and (fortunately) the ones that got through "printed" nothing, so no paper was wasted.

How do I specify which worksheet the macro is to operate on, and how many copies to act on? The workbook I am using has 11 worksheets, and the one I want to act on is the third one. Each worksheet has a unique title (not the default "sheet#", so I assume it has something to do with naming the worksheet.

OK, looking at the sub, it is pretty straight-forward. It prints the active sheet 30 times, incrementing the content of the cell by one with each print. That should work, but it did not print anything. It just caused the printer to spit out blank sheets. Then there is the question of the cell being updated. The cell that originally had the date was D11, and after merging C11-J11, selecting the cell shows it to be C11. Should I specify C11, C11-J11, or leave it as D11?

[HaHoBe]

Hi, WayneCa,

the typical situiation: you deliver a couple of information not covering everything you want to do, and we donīt know what you want to do as we are based on the inforamtion given.

I would only rely on the index of a sheet if I know that there is only one sheet in the workbook. Rather Iīd rely on the codename of the sheet which is not the name seen on the worksheet tab but in the project explorer in the VBE.

I usually test my codes with 2 or three numbers to see if itīs okay, no matter on how otīs posted here in the forum. And as I do not have a real printer installed I usually use PrintPreview in order to see the result onscreen before even daring to have paper run through any printer.

Assuming the codename for the sheet is shtPrint then the code may look like this:

Please Login or Register  to view this content.

The values for both constants may be adapted to suit as well as the range for the date, I would recommend to set them as low as possible for testing the code (say 2 for each) or use PrintPreview prior to a live test.

There is absolutely no need to merge cells, no matter what youīre argument for them should be. Use the address of tehe first cell of the merged area as the other addresses would not fill in the information.

Ciao,
Holger

[WayneCa]

OK, the answer is that I had to specify the cell C11 for the sub to work correctly. Apparently all the blank sheets being printed was due to my cancelling the prints the first time. I modified the sub to only print 2 pages, and it worked correctly once I had changed the cell from D11 to C11.

Thanks for the help, Holger. Now I have the capability to print a sheet with multiple dates.

Wayne

[WayneCa]

Hi, WayneCa,

the typical situiation: you deliver a couple of information not covering everything you want to do, and we donīt know what you want to do as we are based on the inforamtion given.

I would only rely on the index of a sheet if I know that there is only one sheet in the workbook. Rather Iīd rely on the codename of the sheet which is not the name seen on the worksheet tab but in the project explorer in the VBE.

I usually test my codes with 2 or three numbers to see if itīs okay, no matter on how otīs posted here in the forum. And as I do not have a real printer installed I usually use PrintPreview in order to see the result onscreen before even daring to have paper run through any printer.

Assuming the codename for the sheet is shtPrint then the code may look like this:

Please Login or Register  to view this content.

The values for both constants may be adapted to suit as well as the range for the date, I would recommend to set them as low as possible for testing the code (say 2 for each) or use PrintPreview prior to a live test.

There is absolutely no need to merge cells, no matter what youīre argument for them should be. Use the address of tehe first cell of the merged area as the other addresses would not fill in the information.

Ciao,
Holger

Thanks again, Holger. I am usually told I go into too much detail rather than not enough. I believe it is my lack of knowledge of the terms used in Excel programming that is the reason for the lack of understanding.

The truth is, your sub worked fine. It was me being in too much of a rush and not paying enough attention that was causing the issues. The merging of the cells is due to the fact that the width of the cell the date is specified in is not wide enough to hold the date in the form "November 1, 2014". To make it work, and center the date the way I need it, I had to merge a series of cells. The original cell was D11, and after the merge it was C11. I was not sure if leaving the cell specified in the SUB as D11 would work. It did not, and changing it to C11 corrected that issue. It was looking at that when I realized that the SUB was acting on the active worksheet, so there was no issue with that. I believe that the blank pages being printed was because I was cancelling the print action on the first run, when I had left the loop construct to iterate 30 times.

I hope this clears things up for you. The SUB works as expected, and is exactly what I was looking for. Thank you again for your help.

Wayne