+ Reply to Thread
Results 1 to 5 of 5

Macro with muliple tabs - works on some ss's - not on others.

Hybrid View

  1. #1
    Registered User
    Join Date
    10-19-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Macro with muliple tabs - works on some ss's - not on others.

    I have a macro that I plan to use each month on a P & L spreadsheet. The P & L is in the same format each month and has multiple tabs (we own retail stores - so one tab per store) When I run the macro on older reports (Sept 2012 , Oct 2012, Jan 2013) it works on one of them, but not the others. The format below only works on the first tab on a couple of the P & L's - on the other P & L it works fine on all tabs. I am baffled and to why it isn't working on all tabs on all P & L's. Any suggestions? Do I need to run the commands for each tab separately?

    Sheets(Array("@DDM-T132", "002004", "002008", "002012", "002038", "002054", "002073", _
            "002076", "002094", "002261")).Select
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftMargin = Application.InchesToPoints(0.05)
            .RightMargin = Application.InchesToPoints(0.05)
            .TopMargin = Application.InchesToPoints(0.75)
            .Zoom = 85
            .ScaleWithDocHeaderFooter = True
            End With
        Application.PrintCommunication = True
        ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro with muliple tabs - works on some ss's - not on others.

    With ActiveSheet.PageSetup
    only looks on the active sheet, you need to loop through the sheets

  3. #3
    Registered User
    Join Date
    10-19-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Macro with muliple tabs - works on some ss's - not on others.

    Can you offer a suggestion as to how I need to modify?
    Can I just remove that line "with activesheet.pagesetup" ?
    I am new to macros and am trying to learn.
    Thanks!!!

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Macro with muliple tabs - works on some ss's - not on others.

    Untested

    Sub test()
    
    Dim j&, ws As Worksheets
    j = (Array("@DDM-T132", "002004", "002008", "002012", "002038", "002054", "002073", _
            "002076", "002094", "002261"))
            
    For Each ws In ThisWorkbook.Worksheets(j)
        Application.PrintCommunication = False
        With ws.PageSetup
            .LeftMargin = Application.InchesToPoints(0.05)
            .RightMargin = Application.InchesToPoints(0.05)
            .TopMargin = Application.InchesToPoints(0.75)
            .Zoom = 85
            .ScaleWithDocHeaderFooter = True
            End With
        Application.PrintCommunication = True
        ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
     Next ws
    End Sub

  5. #5
    Registered User
    Join Date
    10-19-2012
    Location
    Virginia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Macro with muliple tabs - works on some ss's - not on others.

    Thank you AB33!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1