EXCEL VBA案例实操——批量复制数据到目标工作簿

工具:MS EXCEL 2007,宏

目的:把“源”中的数据依次复制到各个分表中。

目标工作簿
数据源

第一步:录制宏。

把“源”表中的数据手工复制到分表“1店成本”和”3店成本”中一月份的数据中。

录制宏

第二步:查看并分析代码。

代码界面

可以看出,复制动作的代码是下面这段(称为主体代码):

 Sheets("源").Select
    Range("C3:C4").Select
    Selection.Copy
    Sheets("1店成本").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

下一段代码更改的只是两个函数Range(单元格的范围),Sheets(目标工作簿)。

第三步:复制主体代码。

只需要更改第二行Range(“C3:C4”).Select和第五行Sheets(“1店成本”)的参数,例如改成C9:C10和C12:C13;Sheets(“4店成本”)和Sheets(“5店成本”)。假如后面还有更多的店别,就复制更多次。

但是这样做有个很明显的弊端:假如店别特别多的话,要复制修改很多次代码,一不小心就会出错(例如我想解决这个问题的原因是需要复制90多家店)。

既然每段代码中只改变的是Range和Sheets的参数,那我们可以使用循环格式和变量来替代——让一段代码在特定范围内循环执行。

第四步:增加循环结构。

Dim定义一个变量i,是循环的标的,for i =1 to 5意思是i 将从1到5依次运行,共5次。工作簿的命名是“店号”+“店成本”,所以可以把i变量加入Sheets的参数里。这样每次循环的目标工作簿就依次是”1店成本”, ”2店成本”, ”3店成本”……”5店成本”。

Dim i As Integer
For i = 1 To 5
    Sheets("源").Select
    Range("C3:C4").Select
    	Selection.Copy
    Sheets(i & "店成本").Select
    	Range("C5").Select
    		Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
                SkipBlanks _:=False, Transpose:=False       
Next i
End Sub

第五步:定义Range参数的变量。

为变量c赋初始值0,在循环开始的时候c加3,第一次变成3,第二次循环变成6,以此类推。因为range函数选取的是范围,所以用两个Cell函数来确定,即Cc单元格到C(c+1)单元格。

Dim i As Integer
Dim c As Integer
c = 0
For i = 1 To 5
	C=C+3
    Sheets("源").Select
    Range(Cells(c, "C"), Cells(c + 1, "C")).Select
    	Selection.Copy
    Sheets(i & "店成本").Select
    	Range("C5").Select
    		Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
                SkipBlanks _:=False, Transpose:=False       
Next i
End Sub

第六步:运行

其实第二步这个代码基本已经完成了,但是如果进入运行就会发现问题。

错误警告

跳转到表格界面发现光标定位在3号店的地方,为什么到这里复制出错呢?原来我们的工作簿从1号店到5号店缺少了2号店。但其实代码是没问题的,VBA中可以用On Error Resume Next语句来跳过错误,直接进行下一步。

但是这样做的结果是,虽然跳过了不存在的2号店,但是变量c是每一轮都增加的,导致3号店及以后表格的数据源都下移了。

这是很常见的问题,有时候店号并不是连续的,也可能并不是依次上升的。所以最好是能够用定位的方法,根据店号判断数据源从哪里取。

第七步:改进。

引入一个num变量,并且在每次循环开始后,先遍历A列,找到A列中等于i的那个单元格,并将这个单元格的行数赋值给变量c。所以在选择数据源的时候,只需要用店别所在行数加上1就行了。

Dim i As Integer
Dim c As Integer
Dim num As Range
For i = 1 To 5
On Error Resume Next
Sheets("源").Select
    	For Each num In Range("A1:A20")
            If num.Value = i Then
                c = num.Row
            End If
        Next
    Sheets("源").Select
    Range(Cells(c + 1, "C"), Cells(c + 2, "C")).Select
        Selection.Copy
    Sheets(i & "店成本").Select
        Range("C5").Select
        	Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            	:=False, Transpose:=False
Next i
End Sub

如果有几十家几百家店的话,只需要把i的值改大即可。当然也可以把第16行的目标单元格改成用inputbox输入变量的形式,这样每个月使用的时候就更方便。

其实本文中涉及的语句都比较简单,但更想传递的是一种方法。作为普通的EXCEL使用者而非专家,遇到重复性工作想用宏代替却无从下手时,不妨使用“录制宏”的方法让系统把基础语句写出来,然后再进行逐句分析及优化。本文也正是使用“倒叙”的方法叙述了这一过程。