Excel宏实现扁平表转换：如何保留批注、格式并拆分列？

核心修改点说明 #

1. 保留批注与单元格格式
   原宏仅复制单元格数值，替换为Copy+PasteSpecial组合，同时复制值、格式和批注。
2. 拆分动作与门号列
   针对“In Door 1”这类列标题，用Split函数拆分出动作（In/Out）和门号，新增两列分别存储。

修改后的完整宏代码 #

Sub Redesigner()
    Dim i As Long
    Dim hc As Integer, hr As Integer
    Dim ns As Worksheet
    Dim splitTitle As Variant ' 用于拆分列标题的数组
     
    hr = InputBox("请输入列标题的行数：")
    hc = InputBox("请输入左侧固定列的数量：")
     
    Application.ScreenUpdating = False
    Application.CutCopyMode = False ' 避免粘贴后保留复制状态
     
    i = 1
    Set inpdata = Selection
    Set ns = Worksheets.Add
     
    For r = (hr + 1) To inpdata.Rows.Count
        For c = (hc + 1) To inpdata.Columns.Count
            ' 复制左侧固定列：值、格式、批注
            For j = 1 To hc
                inpdata.Cells(r, j).Copy
                ns.Cells(i, j).PasteSpecial Paste:=xlPasteValuesAndNumberFormats ' 复制值和数字格式
                ns.Cells(i, j).PasteSpecial Paste:=xlPasteFormats ' 复制单元格格式（字体、对齐等）
                ns.Cells(i, j).PasteSpecial Paste:=xlPasteComments ' 复制批注
            Next j
             
            ' 拆分列标题为动作和门号
            splitTitle = Split(inpdata.Cells(hr, c).Value, " ") ' 假设标题行是hr行，比如"In Door 1"拆分为数组
            If UBound(splitTitle) >= 2 Then ' 确保拆分后有足够元素
                ns.Cells(i, j) = splitTitle(0) ' 动作：In/Out
                ns.Cells(i, j + 1) = splitTitle(2) ' 门号：1/2等
            Else
                ns.Cells(i, j) = inpdata.Cells(hr, c).Value ' 拆分失败时保留原标题
                ns.Cells(i, j + 1) = ""
            End If
             
            ' 复制数据单元格：值、格式、批注
            inpdata.Cells(r, c).Copy
            ns.Cells(i, j + 2).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            ns.Cells(i, j + 2).PasteSpecial Paste:=xlPasteFormats
            ns.Cells(i, j + 2).PasteSpecial Paste:=xlPasteComments
             
            i = i + 1
        Next c
    Next r
     
    ' 自动调整新表列宽
    ns.Columns.AutoFit
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "转换完成！"
End Sub

细节说明 #

• 输入框改为中文提示，适配国内使用习惯
• 新增Application.CutCopyMode = False清理复制状态，避免Excel界面残留复制虚线
• 拆分逻辑默认标题格式为“动作 Door 门号”，如果你的标题格式不同（比如“In-Door-1”），只需修改Split的分隔符为对应符号即可（比如Split(..., "-")）
• 自动调整列宽提升新表可读性

内容的提问来源于stack exchange，提问作者sekutor