Format tab for printing

[ATLGator]

I have a macro that copies a tab with formulas and pastes values/formats/column widths to replicate it in another workbook without formulas. However, I need to get the new tab into a print-ready format but the coding I have right now is not doing the trick.

There are only two columns that need to be printed and currently there is a page break after the first column, however, I need it to be after the second. The original tab is already formatted this way, but the print format is not being carried over in the "paste special > formats" process. Also, after checking the results of the code I am using, it seems that nothing changes. What am I doing wrong here?

Thanks!

[Leith Ross]

Hello ATLGator,

You will get an answer sooner if you post a copy of the workbook. A lot of time can be wasted going back and forth trying to guess what you have done and how you did it.

[ATLGator]

Here is a sample file. If you step into the Macro, it stops when you hit the page formatting.

[Leith Ross]

Hello ATLGator,

I refined your macro. Try this version and let me know the results on your nd.

Please Login or Register  to view this content.

[ATLGator]

Leith, the results of your code copied the entire tab into a new workbook, formulas and all. However, I am trying to just copy the selection and paste the values, widths, and formats instead of the formulas. Also, I don't want to have the macro button show up in the new workbook. Is there a way to just fix the page break by moving the break to after column B instead of after column A?

Thanks

[Leith Ross]

Hello ATLGator,

I am puzzled by that problem. Everything I tried failed to correct it or created another problem. It could be on my end because my computer has been a little unstable today.

The page break views are not the same on my computer once the sheet was copied using the original code. The only way I could get the same result was to copy the entire sheet. I can add additional code to clean up the sheet - formulas, remove the button, etc. It isn't clear to me why the page break appears in column "A", even after setting the margins the same as the original worksheet.

Attempting to move the page break via code on my computer resulted in an error. Not sure if you are experiencing that problem also. Let me know what problems you're having then I will know how much of what I'm experiencing is due to my computer.

[ATLGator]

When I first put the code together, I wasn't getting an error - just nothing was changing. Then, I closed the workbook and re-opened it, and now I am getting an error: "Run-time error '1004': Application-defined or object-defined error"

[ATLGator]

I just tried ActiveSheet.PageSetup.PrintArea = "$A$1:$B$18" instead of ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=2 but that didn't work either

[Leith Ross]

Hello ATLGator,

I will add the additional code to macro to clean the sheet. It doesn't look like there is any other choice.

[Leith Ross]

Hello ATLGator,

Here is the macro with the extra code. It looks good on my end but let me know how it works for you.

Please Login or Register  to view this content.

[ATLGator]

Leith, I appreciate the help but it doesn't really accomplish my goal. I am tying to get the new workbook to:
- have only one tab, named based on what is put in by the user in the original workbook
- name the file based on what is put in by the user in the original workbook
- have the new workbook be a visual replica of the original, except values instead of formulas and no button

If you look at the original code and take out the three lines starting at ActiveWindow.View = xlPageBreakPreview, then you will see what I am trying to do. The only step left after running that code is getting the result formatted to print.

Hope that helps a bit.

[Leith Ross]

Hello ATLGator,

This macro reproduces the original faithfully without the formulas or button and is renamed according to cell value in "B4".

Please Login or Register  to view this content.

[ATLGator]

Thank you for the new code, but I really need to stick with what I started with and figure out why it's not working. This code will be built upon later. For example, I would also like to add a page footer, and eventually I will have to create a loop.

Here are the two lines I'm currently using:

Please Login or Register  to view this content.

Both are giving me an error. The RightFooter error is "Run-time error '1004': RightFooter method of PageSetup class failed". The PrintArea error is "Run-time error '438': Object doesn't support this property or method". I am not sure how to fix these but everything online that I have found so far looks the same as what I have.

[Leith Ross]

Hello ATLGator,

When you assign a value to variable or object, VBA expects to see an equal sign.

Please Login or Register  to view this content.

If you have the VBA Help files installed, you should start referencing them. If not then you should download them. It will save you a lot time in the long run.

[ATLGator]

Where do I get these files?

[Leith Ross]

Hello ATLGator,

If you have the Office 2007 CD, you can load the VBA Help files from there. If Office was pre-installed on your machine then you will need to contact the installer or your IT department if applicable.

Currently, only the VBA help files for Office 2003 are available for download. There is the Excel 2007 object model reference but it deals only with VBA object, properties and methods pertaining to Excel and not VBA in general. You may find it useful at some point.

Excel 2007 Object Model Reference

[ATLGator]

The equal sign fixed the RightFooter, but not the PrintArea . . . I don't understand why this one is giving me so much trouble.

Are the help files found by hitting F1 in VBE, or are they something different? If so, I have tried the example given (Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$C$5") and even though there are no errors, the print area does not change.