Excel VBA macro to drag off the page break preview right side

[Jyotirmaya]

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
End Sub

I have recorded a macro to drag off the direction of page break to right, I have 30+ sheets and I want that the code to run over all sheets except sheet name "Count" & "Raw Data". What should be the code ??

 

[Domenic]

The following macro will loop through each worksheet in the active workbook, and for each worksheet which is not named "Count" or "Raw Data", it removes the first vertical page break. Note that it simply uses the Delete method instead.

Sub DeleteFirstVerticlePageBreakInWorksheets()

    Dim currentWorksheet As Worksheet
    
    On Error Resume Next
    For Each currentWorksheet In ActiveWorkbook.Worksheets
        If currentWorksheet.Name <> "Count" And currentWorksheet.Name <> "Raw Data" Then
            currentWorksheet.VPageBreaks(1).Delete
        End If
    Next currentWorksheet
    On Error GoTo 0
    
End Sub

Hope this helps!

 

[Jyotirmaya]

I tried the code

Sub DeleteFirstVerticlePageBreakInWorksheets()

    Dim currentWorksheet As Worksheet
    
    On Error Resume Next
    For Each currentWorksheet In ActiveWorkbook.Worksheets
        If currentWorksheet.Name <> "COUNT" And currentWorksheet.Name <> "RAW DATA" Then
            currentWorksheet.VPageBreaks(1).Delete
        End If
    Next currentWorksheet
    On Error GoTo 0
    
End Sub

But it didnt delete the page break not dragged towards right.

 

[Domenic]

Oh I see, try the following instead...

Sub DeleteFirstVerticlePageBreakInWorksheets()

    Dim currentWorksheet As Worksheet
    
    For Each currentWorksheet In ActiveWorkbook.Worksheets
        If currentWorksheet.Name <> "COUNT" And currentWorksheet.Name <> "RAW DATA" Then
            With currentWorksheet
                If .VPageBreaks.Count > 0 Then
                    .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
                End If
            End With
        End If
    Next currentWorksheet
    
End Sub

Does this help?