Private Sub CommandButton1_Click()
Dim DSht As Worksheet
Dim PB As HPageBreak
Dim StLine As Long
Dim MidLine As Long
Dim EdLine As Long
Dim rng As Range
Dim ColN As Integer
Application.ScreenUpdating = False
Sheets(1).Copy after:=Sheets(1)
ActiveWindow.View = xlPageBreakPreview
i = 1
With ActiveSheet
For Each PB In .HPageBreaks
For ColN = 0 To 19
'B列
If PB.Location.Offset(-1, ColN).MergeArea.Address = PB.Location.Offset(0, ColN).MergeArea.Address Then
Set rng = PB.Location.Offset(-1, ColN).MergeArea
'Get StartLine and EndLine
StLine = rng.Cells(1).Row
MidLine = PB.Location.Row
EdLine = rng.Cells(rng.Cells.Count).Row
'取消合并
rng.UnMerge
'写入值
.Cells(MidLine, ColN + 1) = .Cells(StLine, ColN + 1).Text
'重新合并
.Range(.Cells(StLine, ColN + 1), .Cells(MidLine - 1, ColN + 1)).Merge
.Range(.Cells(MidLine, ColN + 1), .Cells(EdLine, ColN + 1)).Merge
End If
Next ColN
i = i + 1
Next
.Range("A4").CurrentRegion.Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
End Sub