VBA Learning notes Worksheet

Source: Internet
Author: User


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.