|Did you set this up as a Workbook_BeforePrint macro or as a macro attached to a command button?|
If you left it as a BeforePrint event macro, you should not get a "Variable not defined" error with Cancel since it is not a variable. It is a Boolean operator that tells Excel to cancel the Print operation since the code itself already took care of the printing.
A BeforePrint event macro is a macro that runs before Excel prints the sheet. If there is code within the macro to do the printing, then you need to Cancel the Excel printing operation or Excel will print whatever it thinks it's supposed to print as soon as the macro has executed all of it's instructions.
If you turned this into a "regular" macro attached to a Command button, then delete the Cancel = True line because it it not needed and will cause problems as you have found. You should also delete the Application.DisableEvents = True and Application.DisableEvents = False lines because they are not needed in a "regular" macro either.
As far as the "Method "Range' of object'_Worksheet failed" error on the Header line, I am assuming that you didn't follow this part of the set-up instructions:
Your Header and Footer must be named ranges and placed in a different sheet, i.e. not in Sheet 1.
Let me explain why this is necessary:
When you print a "long" worksheet in the normal fashion, you can tell Excel to repeat a specified number of rows at the top of each sheet. This works because Excel knows where it is going to insert a page break and knows to insert the specified rows at the top of every page.
However, in the case of this macro, we are not using that feature because we (actually, the macro) are telling Excel not only to create separate sheets but also to print some rows at the top and bottom of each sheet. In other words, the code not printing one long sheet that Excel is splitting into pages, it is creating a series of individual single page sheets and printing each one, one at a time.
Therefore, you need to turn off the "rows to repeat" feature and create your own "Header" and "Footer" as named ranges for the code to copy to each sheet as it creates it.
I think part of the confusion is the fact that we are using "Header" and "Footer" as the name of the ranges. Since these terms have an actual meaning when dealing with documents, perhaps it would be better if we use myHeader and myFooter so we know that they are not really anything built into Excel, but simply rows of text that you created and that the macro is copying to each sheet before it prints it.
Let's say your data is on Sheet 1.
On Sheet 2, enter your "header" text in some rows and name the range "myHeader".
Also on Sheet 2, enter your "footer" text in some rows and name the range "myFooter".
In the macro, change:
Since the code is creating a new sheet for each 15 rows, and printing each sheet out as an individual sheet, the code needs to copy the myHeader and myFooter rows to each new sheet, then copy 15 rows of data, then print out each sheet individually.
This is very different than one long sheet being split into pages by Excel as Excel is printing it. That is a very important concept to grasp to help you understand what the code is doing.
You will notice that I added quite a few detailed comments in the code. Read them carefully to help you understand what the code is doing. Use F8 to single step through the code and watch as it adds the new sheets and copies the myHeader, myFooter and data rows.