G
Guest
Hi ive got the following code running manually on a spreadsheet. But I need
it to run automatically only when the macro is in a particular folder. Need
any suggestions or ways to improve my code..thank you in advance!!..
Sub main()
call Hide_cols()
call Format_cols()
call Filter_sm()
call Page_setup()
end sub
Sub Hide_cols()
Dim i As Integer
For i = 26 To 1 Step -1
If (i <> 2) And (i <> 3) And (i <> 5) And (i <> 6) And (i <> 9) And (i <>
15) And (i <> 24) Then
Columns(i).Select
Selection.EntireColumn.Hidden = True
End If
Next i
End Sub
Sub Format_cols()
Cells(1, "X").Value = "SsC"
Cells(1, "O").Value = "SL"
Cells(1, "E").Value = "AWS"
Cells(1, "I").Value = "BOH"
Cells(1, "AA").Value = "Pickbin"
Cells(1, "AB").Value = "SGF"
Cells(1, "AC").Value = "Diff+/-"
Range("E1:AC1").HorizontalAlignment = xlHAlignCenter
Cells(1, "AC").Font.Bold = True
With Worksheets("Data")
.Columns("O:O").Select
Selection.NumberFormat = "00-00-00"
.Columns("B").AutoFit
.Columns("E").AutoFit
.Columns("O").AutoFit
.Columns("X").AutoFit
.Columns("I").AutoFit
.Columns("AA:AC").ColumnWidth = 9
.Columns("C").ColumnWidth = 39.3
End With
End Sub
Sub Filter_SM()
Columns("F").AutoFilter Field:=1, Criteria1:=">=2"
Columns("F").Select
Selection.EntireColumn.Hidden = True
End Sub
Sub Page_Setup()
With Worksheets("Data").PageSetup
.LeftHeader = "&""Arial,Bold""IKEA Confidential"
.CenterHeader = Format(Now(), "mmmm dd, yyyy")
.LeftFooter = "Audit Commenced By:_______________________"
.RightFooter = "Audit Verified By:_______________________"
.RightHeader = "page &p"
.CenterHorizontally = True
.Zoom = 125
.Orientation = xlLandscape
.PrintGridlines = True
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.51)
.BottomMargin = Application.InchesToPoints(0.35)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.11)
.PrintTitleRows = "$1:$1"
End With
End Sub
it to run automatically only when the macro is in a particular folder. Need
any suggestions or ways to improve my code..thank you in advance!!..
Sub main()
call Hide_cols()
call Format_cols()
call Filter_sm()
call Page_setup()
end sub
Sub Hide_cols()
Dim i As Integer
For i = 26 To 1 Step -1
If (i <> 2) And (i <> 3) And (i <> 5) And (i <> 6) And (i <> 9) And (i <>
15) And (i <> 24) Then
Columns(i).Select
Selection.EntireColumn.Hidden = True
End If
Next i
End Sub
Sub Format_cols()
Cells(1, "X").Value = "SsC"
Cells(1, "O").Value = "SL"
Cells(1, "E").Value = "AWS"
Cells(1, "I").Value = "BOH"
Cells(1, "AA").Value = "Pickbin"
Cells(1, "AB").Value = "SGF"
Cells(1, "AC").Value = "Diff+/-"
Range("E1:AC1").HorizontalAlignment = xlHAlignCenter
Cells(1, "AC").Font.Bold = True
With Worksheets("Data")
.Columns("O:O").Select
Selection.NumberFormat = "00-00-00"
.Columns("B").AutoFit
.Columns("E").AutoFit
.Columns("O").AutoFit
.Columns("X").AutoFit
.Columns("I").AutoFit
.Columns("AA:AC").ColumnWidth = 9
.Columns("C").ColumnWidth = 39.3
End With
End Sub
Sub Filter_SM()
Columns("F").AutoFilter Field:=1, Criteria1:=">=2"
Columns("F").Select
Selection.EntireColumn.Hidden = True
End Sub
Sub Page_Setup()
With Worksheets("Data").PageSetup
.LeftHeader = "&""Arial,Bold""IKEA Confidential"
.CenterHeader = Format(Now(), "mmmm dd, yyyy")
.LeftFooter = "Audit Commenced By:_______________________"
.RightFooter = "Audit Verified By:_______________________"
.RightHeader = "page &p"
.CenterHorizontally = True
.Zoom = 125
.Orientation = xlLandscape
.PrintGridlines = True
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.51)
.BottomMargin = Application.InchesToPoints(0.35)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.11)
.PrintTitleRows = "$1:$1"
End With
End Sub