Office中国论坛/Access中国论坛

标题: 请求简化代码，历遍各个工作表 [打印本页]

---

作者: ni57    时间: 2009-7-11 10:50
标题: 请求简化代码，历遍各个工作表
一共有两个工作簿“引用表.xls”和“源表.xls”。在“引用表.xls”中创建宏Macro2()，实现执行宏Macro2()，自动打开同一文件夹下的“源表.xls”，并且把“源表.xls”中三张工作表sheet1、sheet2、sheet3等表格里面的数据自动更改，如把“张萍”改为“张平”，然后自动保存更改后的“源表.xls”。谢谢
Sub Macro2()
    Workbooks.Open ThisWorkbook.Path & "\源表.xls"
ActiveWorkbook.Sheets("Sheet1").Select
    Cells.Replace "张萍", "张平"
    Cells.Replace "李 四", "李四"   
    Cells.Replace " 赵五 ", "赵五"
ActiveWorkbook.Sheets("Sheet2").Select
    Cells.Replace "张萍", "张平"
    Cells.Replace "李 四", "李四"   
    Cells.Replace " 赵五 ", "赵五"
ActiveWorkbook.Sheets("Sheet3").Select
    Cells.Replace "张萍", "张平"
    Cells.Replace "李 四", "李四"   
    Cells.Replace " 赵五 ", "赵五"
ActiveWorkbook.Sheets("Sheet4").Select
    Cells.Replace "张萍", "张平"
    Cells.Replace "李 四", "李四"   
    Cells.Replace " 赵五 ", "赵五"
ActiveWorkbook.Sheets("Sheet5").Select
    Cells.Replace "张萍", "张平"
    Cells.Replace "李 四", "李四"   
    Cells.Replace " 赵五 ", "赵五"   
    Range("A1").Select
    ActiveWorkbook.Save
End Sub

如果还有Sheet6、7、8等等，那这段代码岂不是要一匹布那么长？请求高手出招，解决疑难……

---

作者: ni57    时间: 2009-7-11 12:15
Sub Macro2()
dim sh as worksheet
    Workbooks.Open ThisWorkbook.Path & "\源表.xls"
for each sh in worksheets
with sh
    .Cells.Replace "张萍", "张平"
    .Cells.Replace "李 四", "李四"   
    .Cells.Replace " 赵五 ", "赵五"
    .Range("A1").Select
end with
next
    ActiveWorkbook.Save
End Sub

---

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)

Powered by Discuz! X3.3