Excel VBA循环实现透视表多列纵向合并复制到指定工作表

原代码问题点 #

• 变量类型错误：存储行号的ss定义为String类型，实际应为Long，否则容易触发类型不匹配报错
• 列引用硬编码为B列，没有跟随循环变量动态切换目标列
• Sheet2写入位置没有做行偏移，每次循环都会覆盖同一区域，无法实现纵向拼接
• 写入列不符合需求：原代码分散写入C/H/M列，没有按要求写入指定单列、第三列

动态引用实现方法 #

循环变量i从2遍历到透视表总列数NC，刚好对应透视表第2列到最后一列（即原代码写死的B列及后续所有列）：

• 硬编码的"B1"（当前处理列的表头）替换为ws1.Cells(1, i)，通过列参数i动态取当前列的表头值
• 硬编码的"B2:B" & ss-1（当前处理列的数值区域）替换为ws1.Range(ws1.Cells(2, i), ws1.Cells(ss - 1, i))，动态定位当前列的有效数据范围
• 新增outRow变量记录Sheet2下一个写入起始行，每次写完一段数据就累加对应行数，实现纵向追加不覆盖

修正后完整代码 #

Sub SelCopCol()
    Dim ss As Long
    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long
    Dim NC As Long
    Dim dataRowCount As Long
    Dim outRow As Long
      
    Set wb = ThisWorkbook
    ' 注意：如果sheet1_Pivot、sheet2是工作表显示名称而非CodeName，需要加双引号，例如 Sheets("数据透视表")
    Set ws1 = wb.Worksheets(sheet1_Pivot)
    Set ws2 = wb.Worksheets(sheet2)
        
    ' 取透视表边界
    ss = ws1.Range("A:A").Find("Grand Total", ws1.Cells(1, 1)).Row ' 总计所在行号
    NC = ws1.Cells(1, Columns.Count).End(xlToLeft).Column ' 透视表总列数
    dataRowCount = ss - 2 ' 单列有效数据行数（第2行到总计行上一行）
    outRow = 2 ' Sheet2初始写入行，第1行可自行设置表头
    
    ws2.Cells.Clear ' 清空Sheet2原有内容，不需要可删除
    Application.ScreenUpdating = False
    
    For i = 2 To NC
        ' 透视表首列（A列）数据重复写入Sheet2指定单列，此处用A列存储，改列号即可换存储位置
        ws2.Range(ws2.Cells(outRow, 1), ws2.Cells(outRow + dataRowCount - 1, 1)) = _
            ws1.Range(ws1.Cells(2, 1), ws1.Cells(ss - 1, 1)).Value
        
        ' 当前列表头+数值写入Sheet2第三列（C列）
        ws2.Cells(outRow, 3) = ws1.Cells(1, i).Value ' 写表头
        ws2.Range(ws2.Cells(outRow + 1, 3), ws2.Cells(outRow + dataRowCount - 1, 3)) = _
            ws1.Range(ws1.Cells(2, i), ws1.Cells(ss - 1, i)).Value ' 写数值
        
        outRow = outRow + dataRowCount ' 偏移写入行到下一段空白区
    Next i
    
    Application.ScreenUpdating = True
End Sub

调整说明 #

• 要修改Sheet2存储列，直接改Cells(行号, 列号)里的列号参数即可，比如要把首列拼接结果存在B列，就把首列写入段的列号1改成2
• 如果你的透视表总计是中文“总计”，把Find方法里的"Grand Total"改成"总计"，否则会报空对象错误
• 如果不需要每次运行清空Sheet2旧数据，删掉ws2.Cells.Clear这行即可

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