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 IntegerFor 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 iEnd Sub

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

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

Dim i As IntegerDim c As Integerc = 0For 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 iEnd Sub

第六步：运行。

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

错误警告

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

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

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

第七步：改进。

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

Dim i As IntegerDim c As IntegerDim num As RangeFor i = 1 To 5On Error Resume NextSheets("源").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:=FalseNext iEnd Sub

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

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