EXCEL-vba-操作工作簿

1，excel批量复制已打开工作簿中的内容到新工作簿

Sub 宏1()
'
' 宏1 宏
'
   '如果不需要程序的人机交互可以主动关闭更新和告警会一定程度上提高运行效率，
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim newbook As Workbook
    For i = 2 To 18
    '从“汇总”的工作簿的表1的sheet中筛选出符合sheet5 D列单元格的值（D列的值从第1行到18行逐个筛查），复制到新的工作簿中去，
    Workbooks("汇总").Sheets("表1").Range("$A$1:$L$478").AutoFilter Field:=9, Criteria1:= _
        Workbooks("汇总").Sheets(5).Range("D" & i).Value
    Set newbook = Workbooks.Add
    Workbooks("汇总").Sheets("表1").Range("A1:L478").Copy newbook.Sheets("Sheet1").Range("A1")
    newbook.SaveAs Filename:= _
        "E:\workspace\excel\测试例子\" & Workbooks("汇总").Sheets(5).Range("D" & i).Value & ".xlsx", CreateBackup:=False
    newbook.Close False
    
    Next
    '程序运行结束后记得打开屏幕的更新和告警
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True
　　

End Sub　　

2，引用一个工作簿

log0 = Dir(ThisWorkbook.Path & "\" & report_name(report_order, 3) & "0.xlsx")
Set book0 = GetObject(ThisWorkbook.Path & "\" & log0)
Set wk = Workbooks.Open(Filename:=ThisWorkbook.Path & "\1111.xlsm", Password:="weixin")
wk.Save
wk.Close
Set wk = Nothing

 

转载于:https://www.cnblogs.com/tommyjiang/p/10629770.html