VBA Learning notes Worksheet

The Sub traverses all objects under Sheets () for every SHS in Sheets    1    1) = SHS. Namenextend SubSub Traversal of the worksheets under the Can object () for each SHS in worksheets    1      2) = SHS. Namenextend Sub

The difference between the two pieces of code is that one is all objects under sheets, and one is all the sheet objects under Worksheets

To determine whether a worksheet exists or not:

The Sub sheet exists or not () Dim sn$for each sht in Sheets    sn = sht.  Name    If sn = "My Worksheet" then        MsgBox "exists"        Exit Sub    End ifnext    MsgBox "No" End SubSub Worksheet exists or not 1 () Dim sn$for i = 1 to sheets.count        a = Sheets (i). Name    If Sheets (i). Name = "My Worksheet" then        MsgBox "exists"        Exit sub    End ifnext     MsgBox "No" End Sub

Ways to add Worksheets:

' Sheets.add method
' Expression. Add (before, after, Count, Type)
' One of the XlSheetType constants:
' Xlworksheet Worksheets
' Xlchart chart
' Xlexcel4macrosheet macro Table
' Xlexcel4intlmacrosheet dialog box
' Default value is Xlworksheet.

Sub New Sheets () ' Sheets.add ' sheets.add Sheets ("abc") ' added in front of the ABC sheet ' Sheets.add, Sheets ("abc") ' added after the ABC sheet  'sheets.add after:=sheets ("abc") ' Ibid. '  ' Sheets.add count:=2 ' new two worksheets ' Sheets.add, 2 ' ibid . Sheets.add,,, Xlchart ' new chart end Sub

You can create a new worksheet in the above format, noting that the new sheet location is different

' If you want to bulk create a new worksheet, you can loop the results to create a sub New 1 to December worksheet () Dim j%for j = 1 Step-1 ' because the default added sheet is before the current worksheet    Sheets.Add.Name = j & "Month "NextEnd Sub ' Delete sheet sub Delete sheet () on Error Resume nextapplication.displayalerts = False ' Remove warning when removing dim i%for i = 1 to    Heets (I & "month"). Deletenextapplication.displayalerts = True ' restore warning prompt after delete ' End Sub

　　

' Expression. Move (before, after) ' expression. Copy (before, after) Sub move () sheet1.move, Sheet3end SubSub Copy () sheet1.copy Sheets (Sheets.count) End SubSub Instance () Dim i%, sth as worksheetfor i = 1 to    Set sth = sheets.add          sth. Move after:=sheets (sheets.count)          sth. Name = i & "month" NextEnd Sub

Here's the point: Split workbooks

Splits and stores all worksheets under the current workbook into different workbooks, with the names of the previous worksheet names

Focus on using Copy's cross-workbook features, using the SaveAs method of the workbook

Give the Code

Sub Split Workbook () Dim WB as Workbook, KK = 0For each sh in ThisWorkbook.SheetsApplication.DisplayAlerts = False    Set wb = Work Books. Add    k = k + 1    thisworkbook.sheets (k). Copy WB. Sheets (1)    Path = thisworkbook.path & "\" & WB. Sheets (1). Name & ". xlsx"    WB. SaveAs Path    WB. Closenextapplication.displayalerts = TrueEnd Sub

　　

Ps:workbooks (1). Sheets (k). Copy Workbooks (2). Sheets (1) Copy the worksheet under the current workbook to the front of the new workbook under Sheets1 (that is, copy across workbooks)

Ps:saveas back with the full path

VBA Learning notes Worksheet