VBAsheet创建以及高级筛选.md 7.1 KB
Sub 批量新建并命名工作表() 'bysdong720170707按序号命名工作表
Application.ScreenUpdating = False '开启屏幕刷新对话框
Application.DisplayAlerts = False '关闭屏幕警告对话框
Dim arr
'注意下面参数
mxr = [A1048576].End(xlUp).Row
'注意下面参数
arr = Range("a2:d" & mxr)
For i = 1 To UBound(arr, 1)
Sheets.Add before:=Sheets(i)
Sheets(i).Name = arr(i, 1)
Next i
Sheets("总页").Activate
Application.DisplayAlerts = True '开启屏幕警告对话框
Application.ScreenUpdating = True '开启屏幕刷新对话框
End Sub
Sub 制作sheet()
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "预警首报"
Sheets("预警首报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "首报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第一次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "预警二报"
Sheets("预警二报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "二报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第二次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "预警三报"
Sheets("预警三报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "三报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第三次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "预热首报"
Sheets("预热首报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "首报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第一次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "预热二报"
Sheets("预热二报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "二报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第二次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "预热三报"
Sheets("预热三报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "三报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第三次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()+1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "处罚首报"
Sheets("处罚首报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "首报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第一次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "处罚二报"
Sheets("处罚二报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "二报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第二次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "处罚三报"
Sheets("处罚三报").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "三报"
Range("B1").Select
ActiveCell.FormulaR1C1 = "第三次报价时间"
Range("C1").Select
ActiveCell.FormulaR1C1 = "实际推电销类型"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=TODAY()-1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "="
Range("C2").Select
ActiveCell.FormulaR1C1 = "="
Range("C3").Select Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预警首报!A1:C2"), CopyToRange:=Range("预警首报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预警二报!A1:C2"), CopyToRange:=Range("预警二报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预警三报!A1:C2"), CopyToRange:=Range("预警三报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预热首报!A1:C2"), CopyToRange:=Range("预热首报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预热二报!A1:C2"), CopyToRange:=Range("预热二报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预热三报!A1:C2"), CopyToRange:=Range("预热三报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("预热首报!A1:C2"), CopyToRange:=Range("预警二报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("处罚首报!A1:C2"), CopyToRange:=Range("处罚首报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("处罚二报!A1:C2"), CopyToRange:=Range("处罚二报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False
Sheets(1).Cells.AdvancedFilter Action:=xlFilterCopy _
, CriteriaRange:=Range("处罚三报!A1:C2"), CopyToRange:=Range("处罚三报!A4"), Unique:=True
ActiveWindow.SmallScroll Down:=-6 End Sub