Macro needed for Formatting Print Area, Margins etc

[cSciFiChick]

Can someone please help me with possibly an advanced macro. I process a bunch of different reports with different sizes, sometimes single tabs, sometimes multiple tabs, different rows or columns of data and different formatting etc. But every report I send has a few formatting requirements and I would like a macro if possible to do these things in one step.
First we have to Set the Print Area to only include the area of the data. Again the size veries and I can not find a macros that does what the button under the Page layout - Page Setup - Print Area - Set Print Area. I tried to record a macro to do it but it does not seem to carry over when the size of the columns and rows change :/. If this is not possible let me know.

Second Margins need to be
TOP - .25
BOTTOM - .25
LEFT - .17
RIGHT - .22
HEADER - .3
FOOTER - .3

And last the cursor needs to be in cell A1.

I have found a few macros to do one or two of these but it would be nice to do it all in one which is beyond me to build. Also the first one with the Print area has caused problems.

If I had to run these as three seperate macros it would be fine as long as it does it for all the tabs.

 

[selkov]

In so far as setting the print area, It needs to be defined. When you use the recorder it is setting the range. You will need to find a way to get the range criteria in every instance and change the range in the macro.
For the print margins you can use:

Sub Set_Pmargin ()
For Each ws In wb.Worksheets ' add to set all worksheets in work book or rem out if just for the one sheet

With ws.PageSetup
'. remout lines not needed
.Header = 2
.Footer = 65
.LeftMargin = 25
.RightMargin = 25
.BottomMargin = 20
.TopMargin = 20
End With

Next ' add to set all worksheets in work book or rem out if just for the one sheet

 

[cSciFiChick]

Okay I have used macros but really don't want to alter it every time I am not good at writing them. So I wish I could take whatever code is in that print area option I listed above and just make a macro apply it to every tab with one click. If that is not possible then I would like the second and last things. The margins and cursor in A1.

Selkov the margins in your macro don't seem to match the ones I listed is that because in macros they don't take decimals or something. And I want it to do it for all tabs is there a way to do that?

 

[SpillerBD]

Does each worksheet have "data" that you want to print and "other data" you do not want to print?

 

[SpillerBD]

Okay I have used macros but really don't want to alter it every time I am not good at writing them. So I wish I could take whatever code is in that print area option I listed above and just make a macro apply it to every tab with one click. If that is not possible then I would like the second and last things. The margins and cursor in A1.

Selkov the margins in your macro don't seem to match the ones I listed is that because in macros they don't take decimals or something. And I want it to do it for all tabs is there a way to do that?

Different ways to indicate margins, Points, Inches and Millimeters.
This code includes a line to keep the width 1 page wide.

Sub Macro2()

Dim wb As Workbook
Dim ws As Worksheet
'
    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
    With ws.PageSetup
        .LeftMargin = Application.InchesToPoints(0.17)
        .RightMargin = Application.InchesToPoints(0.22)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .FitToPagesWide = 1 ' Sets 1 data to print 1 page wide
    End With
    Range("A1").Activate
    Next
   
    
End Sub

 

[cSciFiChick]

Does each worksheet have "data" that you want to print and "other data" you do not want to print?

I doubt any of these reports actually get printed but someone had the brillant idea that we needed to make sure they all had the print area set. So I want whatever this does 'Page layout - Page Setup - Print Area - Set Print Area.' where it draws a line around where the data is. Every report I do is different one could have five columns and 100 rows of data. The next one could have a bunch of tables with thousands of rows of data. I don't know how the Set Print Area does it but if I click in the corner to highlight the sheet then Set Print area it only selects around where there is actually data. Sometimes I can have a ton of tabs on a worksheet and having to individually Set the Print Area on each sheet can be a long process. So it was wishful thinking that it could be done in a macro. If the other Macro you wrote sets the margins on all tabs and puts the cursor in A1 that is half the battle

 

[cSciFiChick]

I appreciate the macro but I am having a problem. It looks like it does set the margins on every tab but it does not scroll the data to the top and put the cursor in A1 in every tab. Is there a way that can be built into the Marco?

 

[cSciFiChick]

I appreciate the macro but I am having a problem. It looks like it does set the margins on every tab but it does not scroll the data to the top and put the cursor in A1 in every tab. Is there a way that can be built into the Marco?

Can someone please help.