本帖最后由 wanl01 于 2017-5-6 12:57 编辑
多sheet合并保留一个表头并增加a列,我的错在哪了 为什么原表头会错位了呢?应该在B1才对
请问应该怎么修改
Sub 合并所有工作表() Dim i As Integer, sht As Worksheet, sh AsWorksheet, bt As Integer, d As Integer On Error Resume Next Set sht = Sheets("可配") If Err <> 0 Then With Sheets.Add(before:=Sheets(1)) .Name = "可配" End With Else Sheets("可配").MoveSheets(Sheets.count) Sheets("可配").rows("2:60000").Delete End If For Each sh In Worksheets If sh.Name <> "可配" Andsh.Name <> "透视汇总" Then sh.rows("1:1").Copy Sheets("可配").[a1] sh.UsedRange.Offset(1).Copy Sheets("可配").Cells(rows.count,2).End(xlUp).Offset(1) '拷贝数据 Sheets("可配").Cells(rows.count,1).End(xlUp).Offset(1).Resize(sh.UsedRange.rows.count - 1, 1) = sh.Name End If Next sh Range("A1").Select ActiveCell.FormulaR1C1 = "仓位" ActiveWorkbook.Save End Sub
本帖最后由 france723 于 2017-5-5 19:35 编辑
Sub 合并所有工作表() Dim i As Integer, sht As Worksheet, sh AsWorksheet, bt As Integer, d As Integer On Error Resume Next Set sht = Sheets("可配") If Err <> 0 Then With Sheets.Add(before:=Sheets(1)) .Name = "可配" End With Else Sheets("可配").MoveSheets(Sheets.count) Sheets("可配").rows("2:60000").Delete End If For Each sh In Worksheets If sh.Name <> "可配" Andsh.Name <> "透视汇总" Then sh.rows("1:1").Copy Sheets("可配").[a1] Cells(1, 1).Insert Shift:=xlToRight sh.UsedRange.Offset(1).Copy Sheets("可配").Cells(rows.count,2).End(xlUp).Offset(1) '拷贝数据 Sheets("可配").Cells(rows.count,1).End(xlUp).Offset(1).Resize(sh.UsedRange.rows.count - 1, 1) = sh.Name End If Next sh Range("A1").Select ActiveCell.FormulaR1C1 = "仓位" ActiveWorkbook.Save End Sub
|