Moving Script to ThisWorkbook (1 Viewer)

sando

Registered User.
Local time
Today, 21:41
Joined
Jul 7, 2004
Messages
25
Hi

I have VBA script that operates as it should, but I want to move it from the sheet to "ThisWorkbook" so when it opens it executes
When I do that, I get RunTime 9 Error

Any help is appreciated

The script:

Code:
Private Sub Workbook_Open()

Dim main As Workbook
Set main = ThisWorkbook
  

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
    
        ChDir Application.ActiveWorkbook.Path
      
Workbooks.Open Filename:="serverfile1.xlsx"


  With Sheets("IDM").Range("A1:MN" & Sheets("IDM").Range("A" & Rows.Count).End(xlUp).Row)

     '' Filters for team projects''
      .AutoFilter field:=8, Criteria1:=Array("PROJECT TEAM"), Operator:=xlFilterValues
        
       .Columns("B:C").EntireColumn.Hidden = True
       .Columns("K:P").EntireColumn.Hidden = True
       .Columns("AA:AF").EntireColumn.Hidden = True
       .Columns("AM:AV").EntireColumn.Hidden = True
       .Columns("AY:BN").EntireColumn.Hidden = True
       .Columns("BQ:BR").EntireColumn.Hidden = True
       .Columns("BU").EntireColumn.Hidden = True
       .Columns("CC:DB").EntireColumn.Hidden = True
       .Columns("DO:DT").EntireColumn.Hidden = True
       .Columns("HG:HT").EntireColumn.Hidden = True
       .Columns("HV:JT").EntireColumn.Hidden = True
       .Columns("JX:MO").EntireColumn.Hidden = True


.SpecialCells(xlCellTypeVisible).Copy


    
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = True
    
  
    ActiveWorkbook.SaveAs Filename:= _
        "C:\ND_TX", FileFormat:=xlCSV

Windows("serverfile1.xlsx").Close
Windows("ND_TX.CSV").Close
    
End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub
 

June7

AWF VIP
Local time
Today, 02:41
Joined
Mar 9, 2014
Messages
5,425
I tried code in ThisWorkbook module. I don't get error but also don't get the csv file. The procedure doesn't run.

If you want to provide your file for analysis, follow instructions at bottom of my post.
 

Users who are viewing this thread

Top Bottom