答:下面代码可以实现循环处理。但不一定得到正确结果,原因在你复制的时候,不知道你选择的是哪个单元格。最好能说明将每张表里的哪块区域数据复制到汇总表。
Sub Summary()
Dim Sht As Worksheet
For Each Sht In Sheets
If Sht.Name <> "汇总" Then
Sht.Activate
'-------复制粘贴过程-------
End If
Next Sht
End Sub
追问看不懂唉,这个我要从哪里改?我是每个sheet里C25到C601复制到汇总表B2,C2,D2
就是sheet001的C25:C601粘贴到B2;’sheet002的C25:C601粘贴到C2;sheet003的C25:C601粘贴到D2,以此类推
追答那就试试下面代码:
Sub Summary()
Dim Sht As Worksheet
Dim DesRng As Range
Set DesRng = Sheets("汇总").Range("B2")
For Each Sht In Sheets
If Sht.Name <> "汇总" Then
With Sht
.Range("C25:C601").Copy
DesRng.PasteSpecial xlPasteValues
End With
End If
Set DesRng = DesRng.Offset(0, 1)
Next Sht
End Sub
追问汇总是成功了,可是后面画折线图就不对了
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/cefc1e178a82b901737536f1798da9773812ef1c?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
我把数字格式设置为数值的,折线图画出来不对。
追答可能是数字以文本形式保存了,你改格式是不行的。试试下面代码
Sub Summary()
Dim Sht As Worksheet
Dim DesRng As Range
Set DesRng = Sheets("汇总").Range("B2")
For Each Sht In Sheets
If Sht.Name <> "汇总" Then
With Sht
.Range("C25:C601").Copy
DesRng.PasteSpecial xlPasteValues
Call TextToNumber(Range(DesRng, DesRng.End(xlDown)))
End With
End If
Set DesRng = DesRng.Offset(0, 1)
Next Sht
End Sub
Sub TextToNumber(Rng As Range)
On Error Resume Next
Dim temp() As Variant
Set Rng = Application.Intersect(Rng.Parent.UsedRange, Rng)
temp = Rng.Value
For i = 1 To UBound(temp, 1)
For j = 1 To UBound(temp, 2)
temp(i, j) = Val(temp(i, j))
Next j
Next i
Rng.Value = temp
On Error GoTo 0
End Sub