VBA Code to run in hidden (personal) workbook

[Tosborn]

Hey y'all,

I have some VBA code that works all good but I am trying to get it running in a hidden (personal) workbook instead of in the active workbook. I know what I’m meant to be doing to get it to do this but just have trouble executing.

My understanding of what I’m meant to be doing is that I’m meant to be first activating the workbook for the file that I need to run the code for. I can get loops to run on a small bit of code but when I try to do the same on this larger bit of code it just runs on the first sheet but doesn’t loop to the rest.

A bit of background as to why I want to do this.

A report runs and creates a file. I then need to add a sheet to this file and copy paste in VBA code to the new file and then run it. This process is fine for me but I need to pass it down to the assistant accountant to do and she is not really confident with VBA code. So my plan is to set her up with a hidden (personal) excel file with the VBA already in it.

The code below basically formats a file with a lot of sheets that contain the same P&L report for different retail stores, after looping through all the sheets it saves selected sheets to pdf and then emails.

I hope this explanation is clear enough

Here’s my code:

Sub PDFandEmail()

'To set page breaks on all worksheets, ignoring erros

Application.ScreenUpdating = False


Dim ws As Worksheet
Worksheets(6).Activate
    For Each ws In ThisWorkbook.Worksheets
    On Error Resume Next

        Dim a As Integer
        a = ActiveSheet.Index + 1
        If a > Sheets.Count Then a = 7
        Sheets(a).Activate
 
 
 
'get rid of DIV error

Dim r As Range
For Each r In ActiveSheet.Range("B9:Y80")
    With r
        If .Text = "#DIV/0!" Then
            .Clear
           ' .NumberFormat = "@"
            .Value = 0
        End If
    End With
Next

        ActiveWindow.View = xlPageBreakPreview
        ActiveSheet.ResetAllPageBreaks
        ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
       

'to set page print margins

                With ActiveSheet.PageSetup
            
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 1
                .Orientation = xlLandscape
                .LeftMargin = Application.InchesToPoints(0.393700787401575)
                .RightMargin = Application.InchesToPoints(0.393700787401575)
                .TopMargin = Application.InchesToPoints(0.196850393700787)
                                
                
                End With

'Column width, row hight, text size


    Range("A1:Y80").Font.Size = 35
    Rows("1:80").RowHeight = 45

    Columns("A:A").ColumnWidth = 157
    Columns("B:Y").ColumnWidth = 57
    Columns("M:M").ColumnWidth = 1.71

        Columns("M:M").Select
         Range("M3").Activate
            With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
            End With

'fix borders
            
    Range("e7").Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With

    
    'hide financial budgets
    
    Range("D:D").EntireColumn.Hidden = True
    Range("p:p").EntireColumn.Hidden = True
    Range("f:f").EntireColumn.Hidden = True
    Range("g:g").EntireColumn.Hidden = True
    Range("r:r").EntireColumn.Hidden = True
    Range("s:s").EntireColumn.Hidden = True
    
    Rows("81:91").EntireRow.Hidden = True
    Rows("62").EntireRow.Hidden = True
    

            'to fix heading

            Range("A1").UnMerge
            Range("A1").Clear
            Range("H1").FormulaR1C1 = "STRANDBAGS GROUP PTY LIMITED"
            Range("H1:U1").Merge
            Range("H1:U1").HorizontalAlignment = xlCenter
            Range("H2:U2").Merge
            Range("H2:U2").HorizontalAlignment = xlCenter
            Range("K3").Select
            
'To put brackets around numbers
            
            
    Range( _
        "B9:H16,J9:J16,N9:T16,V9:V16,B21:H21,J21,N21:T21,V21,B19:E20,J19,J20,N19,O19,N20,O20,T19,T20,V19,V20,B26:H35,B37:H91" _
        ).Select
    Range("B37").Activate
        Range( _
        "B9:H16,J9:J16,N9:T16,V9:V16,B21:H21,J21,N21:T21,V21,B19:E20,J19,J20,N19,O19,N20,O20,T19,T20,V19,V20,B26:H35,B37:H91,J26:J91" _
        ).Select
    Range("J26").Activate
    ActiveWindow.SmallScroll Down:=-27
    Range( _
        "B9:H16,J9:J16,N9:T16,V9:V16,B21:H21,J21,N21:T21,V21,B19:E20,J19,J20,N19,O19,N20,O20,T19,T20,V19,V20,B26:H35,B37:H91,J26:J91,N26:T91" _
        ).Select
    Range("N26").Activate
        Range( _
        "B9:H16,J9:J16,N9:T16,V9:V16,B21:H21,J21,N21:T21,V21,B19:E20,J19,J20,N19,O19,N20,O20,T19,T20,V19,V20,B26:H35,B37:H91,J26:J91,N26:T91,V26" _
        ).Select
    Range("V26").Activate
    
    Range( _
        "B9:H16,J9:J16,N9:T16,V9:V16,B21:H21,J21,N21:T21,V21,B19:E20,J19,J20,N19,O19,N20,O20,T19,T20,V19,V20,B26:H35,B37:H91,J26:J91,N26:T91,V26,V26:V91" _
        ).Select
        Selection.NumberFormat = "#,##0;(#,##0)"
    
Range("A7").Select
ActiveWindow.Zoom = 25
    
'ActiveWindow.View = xlPageLayoutView

        
    
Next ws

'Go to the last sheet and set variables

Sheets("List").Select

    Dim sheetArray() As String
    Dim rcell As Range
    Dim i As Integer
    Dim wksAllSheets As Variant
    Dim wksSheet1 As Worksheet
    Dim strFilename As String, strFilepath As String
    Dim r3 As Range
    Dim x As Integer
    Dim z As Integer ' Number of Stores in Row
    Dim y As Variant
    Dim c As Variant
    Dim wks As Worksheet
    Dim lastCell As Long
    Dim oApp As Object
    Dim oMail As Object
    
   
        i = 0
        x = 5
            

'Select sheets for creation of PDFs
             
    For Each rcell In Range("e5:q5")
    
    
    
    z = Cells(3, x).Value
    
        If rcell.Value <> "" Then
        
             For Each c In Range(Cells(5, x), Cells(z, x)).Cells
                    ReDim Preserve sheetArray(0 To i)
                    sheetArray(i) = c.Value
                    i = i + 1
             Next c
             
            strFilepath = "G:\Finance\SunV5.3.1\Store P&L's\RMemails\"
            strFilename = rcell
       
            Sheets(sheetArray).Select

            'Export as pdf

            With ActiveSheet
            .ExportAsFixedFormat _
              Type:=xlTypePDF, _
              Filename:=strFilepath & strFilename, _
              Quality:=xlQualityStandard, _
              IncludeDocProperties:=True, _
              IgnorePrintAreas:=True, _
              OpenAfterPublish:=False
              
              End With
            
            'clear integers
            
            x = x + 1
            i = 0
            Set c = Nothing
            
                'Email P&L to RM
            
                        Set oApp = CreateObject("Outlook.Application")
                        Set oMail = oApp.CreateItem(0) 'olMailItem = 0
                            
                            With oMail
                            
                                'User input To property
                                .To = rcell
                                'User input CC property
                                .CC = "Tim osborn"
                                .Subject = "Region Store P&Ls"
                                'Hard code Body property
                                .Body = "Please find your region's store P&Ls for the prior period attached. Kind regards, Timothy Osborn."
                                'Set attachment
                                .Attachments.Add strFilepath & strFilename & ".pdf"
                                '.Send
                                'Display it
                                .Display
                                End With
                 
              End If
        
        Sheets("List").Select
                
                
    Next rcell
    
Application.ScreenUpdating = True

MsgBox "PDF Printing & Email Creation Complete"

End Sub

Any assistance is greatly appreciated. It won’t be the first time Mr Excel msg board saved my life J

Thanks,
Tim

 

[Joe4]

So, when you use logic like this to loop through worksheets:

For Each ws In ThisWorkbook.Worksheets

This iterates through the worksheet objects, but does not select or activate them. The issue then is if you use range references that do not include a worksheet portion, it will default to whatever sheet is currently the active sheet (and it will never move off of that sheet).

So within this loop, you need to do one of two things in order to get the code to apply to all the worksheets:

1. Select/activate the worksheet before doing any work on it, i.e.

For Each ws In ThisWorkbook.Worksheets
    ws.Activate

- or -

2. Include the "ws" worksheet reference in your range references, i.e.

For Each ws In ThisWorkbook.Worksheets
    ws.Range(...)

Note this line in your code:

a = ActiveSheet.Index + 1

The way you have your code written, the value of "ActiveSheet.Index" will never change since the ActiveSheet is not changing in your code.

 

[RoryA]

Note that ThisWorkbook is a reference to the workbook containing the code. It sounds like you want to use ActiveWorkbook instead.

 

[Tosborn]

Fantastic, thanks Rory.

All I did was change

For Each ws In ThisWorkbook.Worksheets

to 

For Each ws In ActiveWorkbook.Worksheets

And it works a treat.

Tim