|
以下是把工作簿内各工作表数据合并到第一个工作表的VBA代码,不足之处是要首先手动新建一个工作表并把它放在工作表中的第一个位置,我想请论坛中 的朋友帮忙修改一下代码,让该代码运行时先自动创建一工作表,然后开始汇总数据到这工作表中。
Sub 把工作簿内所有工作表内容合并到第一个工作表中()
Dim i, n, St As Worksheet
For i = 1 To Sheets.Count
If i = 1 Then
Set St = Sheets(i)
If St.UsedRange.Cells.Count = 1 And St.Cells(1, 1) = "" Then
n = 1 '下一次添加内容的行
Else
n = St.UsedRange.Rows.Count + 1
End If
Else
Sheets(i).UsedRange.Copy St.Cells(n, 1)
n = St.UsedRange.Rows.Count + 1
End If
Next i
End Sub
- Sub Greenhand()
- Dim sht As Worksheet, flag As Boolean, i%, myrow%, myrow1%
- flag = False
- For i = 1 To Sheets.Count
- If Sheets(i).Name = "汇总表" Then flag = True
- Next
- If flag = False Then
- Set sht = Worksheets.Add
- sht.Name = "汇总表"
- Sheets("汇总表").Move before:=Sheets(1)
- End If
- For i = 1 To Sheets.Count
- If Sheets(i).Name <> "汇总表" Then
- myrow = Sheets("汇总表").UsedRange.Row
- myrow1 = Sheets("汇总表").UsedRange.Rows.Count
- If myrow1 = 1 Then
- Sheets(i).UsedRange.Copy
- Sheets("汇总表").Cells(myrow, 1).End(3).Select
- Selection.PasteSpecial (xlPasteValues)
- Else
- Sheets(i).UsedRange.Copy
- Sheets("汇总表").Cells(myrow + myrow1 - 1, 1).Offset(1, 0).Select
- Selection.PasteSpecial (xlPasteValues)
- End If
- End If
- Next i
- End Sub
复制代码
|
|