VBA案列1

1. Set workbook and worksheet in variable
   在工作表和工作簿中运用变量

Sub CopyData_withVariable()

Dim wb_Summary As Workbook, wb_Detail As Workbook, sht_Summary As Worksheet, sht_Detail As Worksheet

Set wb_Summary = Workbooks("AutoSummary")
Set wb_Detail = Workbooks("LossSummary")
Set sht_Summary = wb_Summary.Sheets("Auto")
Set sht_Detail = wb_Detail.Sheets("Auto")

sht_Summary.Range("B2") = Application.WorksheetFunction.Sum(sht_Detail.Range("D:E"))

sht_Summary.Range("B3") = Application.WorksheetFunction.Sum(sht_Detail.Range("F:G"))

wb_Summary.Close savechanges:=True

wb_Detail.Close savechanges:=True

'用变量方便后期修改代码

2. 筛选和排序

筛选

Sub filter()

ActiveSheet.Range("$A$1:$H$6").AutoFilter Field:=5, Criteria1:="<4000"

‘Criteria 1

End Sub

多条件排序

Sub sort()

With Range("$A$1:$H$6")

'执行顺序从下往上，先执行最下面的条件
’升序Ascending  降序Decending

.sort key1:="Policy #", order1:=xlAscending, Header:=xlYes
.sort key1:="DOL", order1:=xlDescending, Header:=xlYes

End With

End Sub

3. (1)批量创建工作表

Sub creatsheets()

Dim int_Row As Integer, sht_Name As Worksheet, int_1Row As Integer


Set sht_Name = ThisWorkbook.Sheets("Name")

'找到最后一行
int_1Row = sht_Name.Range("A" & sht_Name.Rows.Count).End(xlUp).Row
'遍历每一行的名字并新建sheet
For int_Row = 2 To int_1Row

'在当前sheet表后新建一个sheet表
Worksheets.Add after:=Worksheets(Worksheets.Count)

'给新建的sheet表命名
Worksheets(Worksheets.Count).Name = sht_Name.Range("A" & int_Row).Value

Next

End Sub

(2) 遍历工作表，给每一个工作表的A1赋值相应sheet表的名字

Sub ForEachWorksheet()
 
 Dim sht As Worksheet
 
  For Each sht In ThisWorkbook.Worksheets
  
    If sht.Name <> "Name" Then
  
    sht.Range("A1") = sht.Name
  
    End If
 
  Next

End Sub

加粗样式（3）隐藏和取消隐藏工作表

Sub ForEachWorksheet()
 
 Dim sht As Worksheet
 
 For Each sht In ThisWorkbook.Worksheets
  
  If sht.Name <> "Name" Then
  
  '隐藏
  'sht.Visible = xlSheetHidden
  
  '取消隐藏
  'sht.Visible = xlSheetVisible
  
  '超级隐藏，不能通过EXCEL表页面取消隐藏
  sht.Visible = xlSheetVeryHidden
    
  End If
 
 Next

End Sub