老师,我可不可以请假您一个excel vb的问题。百度了好久都找不到解决办法。
这个宏的功能是从一个工作簿提取其中三个工作表,这个功能已经实现了,但是不能通用在其他的工作簿上。当我在其他工作簿运行时,他的作用域还是原先的工作簿。
现在我想要解决的问题就是如何把这个宏运用到同一模板的所有工作簿上
以下是代码:Sub 复制z()Dim s As String
s = ThisWorkbook.Name
Application.ScreenUpdating = FalseDim gzb As WorkbookSet gzb = Workbooks.AddActiveSheet.Name = "清单"
Sheet1.Cells.Copy [a1]Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "箱单"
Sheet101.Cells.Copy [a1]Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "发票"
Sheet9.Cells.Copy [a1]gzb.SaveAs ThisWorkbook.Path & "\Tracking#" & s & ".xls", FileFormat:=xlExcel8Set gzb = NothingActiveWorkbook.CloseApplication.ScreenUpdating = True
End Sub
这个宏的功能是从一个工作簿提取其中三个工作表,这个功能已经实现了,但是不能通用在其他的工作簿上。当我在其他工作簿运行时,他的作用域还是原先的工作簿。
现在我想要解决的问题就是如何把这个宏运用到同一模板的所有工作簿上
以下是代码:Sub 复制z()Dim s As String
s = ThisWorkbook.Name
Application.ScreenUpdating = FalseDim gzb As WorkbookSet gzb = Workbooks.AddActiveSheet.Name = "清单"
Sheet1.Cells.Copy [a1]Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "箱单"
Sheet101.Cells.Copy [a1]Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "发票"
Sheet9.Cells.Copy [a1]gzb.SaveAs ThisWorkbook.Path & "\Tracking#" & s & ".xls", FileFormat:=xlExcel8Set gzb = NothingActiveWorkbook.CloseApplication.ScreenUpdating = True
End Sub
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
Dim gzb As Workbook
Set gzb = Workbooks.Add
Dim objSheet As Worksheet
Set objSheet = gzb.ActiveSheet
objSheet.Name = "清单"
wb.Worksheets("Sheet1").Cells.Copy objSheet.[a1]
Set objSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
objSheet.Name = "箱单"
wb.Worksheets("Sheet101").Cells.Copy objSheet.[a1]
Set objSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
objSheet.Name = "发票"
wb.Worksheets("Sheet9").Cells.Copy objSheet.[a1]
gzb.SaveAs wb.Path & "\Tracking#" & wb.Name & ".xls", FileFormat:=xlExcel8
gzb.Close
Set gzb = Nothing
Application.ScreenUpdating = True
End Sub下载地址:
链接:https://pan.baidu.com/s/1206M0jP-5jmqmsqklDaOhA
提取码:xpjr 运行示例: