Trying to Hide Gridlines using DisplayGridLines instruction only worked for one worksheet from the workbook

Question

I'm a beginner with Vba.

I tryed to hide cell Gridlines on my Workbook with the last insruction of the following sub, the problema is that my workbook is composed from two worksheets, and Gridlines were only hide for one of them (the first):

Sub Valores()

Set NewBook = Workbooks.Add
szToday = Format(Date - 1, "YYYYMMDD")
    With NewBook
        .Title = "Control_Colaterales"
        .Subject = "Control Colaterales"
        .SaveAs Filename:="V:\Departamento\7920-SOLVENCIA\1. Riesgo de Mercado\2. RIESGO DE CONTRAPARTIDA\1. REPORTING DIARIO R.Contrapartida\1. Enviados\Colaterales\Informe_Control_Colaterales_" & szToday & ".xls"
    End With

ThisWorkbook.Worksheets(1).Activate
Cells.Select
Selection.Copy
NewBook.Sheets(1).Activate
ActiveSheet.Name = "CSA y REPO Retrospectivo"
ActiveSheet.Paste
ActiveSheet.Cells.Select
Selection.Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteValues
Worksheets.Add(After:=Sheets(Worksheets.Count)).Name = "CSA y REPO Actual"
ThisWorkbook.Worksheets(1).Activate
Nows = "CSA y REPO Actual"
Worksheets(Nows).Activate
ActiveSheet.Cells.Select
Selection.Copy
NewBook.Sheets(2).Activate
ActiveSheet.Paste
ActiveSheet.Cells.Select
Selection.Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteValues
NewBook.Sheets(1).Activate
ActiveSheet.Range("A1").Activate
ActiveWorkbook.Windows(1).DisplayGridlines = False

End Sub

¿Could anyone help me?

Thank you very much

score 1 · Accepted Answer · answered Nov 07 '17 at 16:07

1

It only works on the active worksheet, so change the last line to this instead:

Worksheets(1).Activate
ActiveWorkbook.Windows(1).DisplayGridlines = False
Worksheets(2).Activate
ActiveWorkbook.Windows(1).DisplayGridlines = False

answered Nov 07 '17 at 16:07

braX

11,506
5
20
33

2

A way to do it without activating worksheet - https://stackoverflow.com/questions/40368373/how-can-i-turn-off-gridlines-in-excel-using-vba-without-using-activewindow – Vityata Nov 07 '17 at 18:22

Vityata · Answer 2 · 2017-11-07T18:22:26.133

1

Try to loop like this to hide the gridlines of all workbooks:

Public Sub TestMe()

    Dim cnt As Long

    For cnt = 1 To ThisWorkbook.Worksheets.Count
        Worksheets(cnt).Activate
        Windows(1).DisplayGridlines = False
    Next cnt

End Sub

You can call the Sub at the end of your code. Edit: A way to do it without activating worksheet - How can I turn off gridlines in excel using VBA, without using ActiveWindow

edited Nov 07 '17 at 18:22

answered Nov 07 '17 at 16:08

Vityata

42,633
8
55
100

1

Thank you, it worked but I needed to understand first that this command only Works on the activesheet. Voted positive to your answer. – Mauro Nov 07 '17 at 16:42
1

@Mauro - welcome. Concerning `ActiveSheet` and `Select`, see the following - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Vityata Nov 07 '17 at 16:44

Trying to Hide Gridlines using DisplayGridLines instruction only worked for one worksheet from the workbook

2 Answers2