VBA/Excel/Access/Word/Excel/Worksheet Add

Материал из VB Эксперт
Перейти к: навигация, поиск

Add a new Sheet

   <source lang="vb">

Sub AddNew_Sheet()

   Dim myWorksheet As Worksheet
   Dim myWorksheetName As String
       myWorksheetName = "MyName"
       Sheets.Add.Name = myWorksheetName
       Sheets(myWorksheetName).Move After:=Sheets(Sheets.Count)
       Sheets("Sheet1").Range("A1:A5").Copy _
           Sheets(myWorksheetName).Range("A1")

End Sub

</source>
   
  


Add a new sheet with name in A1

   <source lang="vb">

Sub AddSheetWithNameCheckIfExists()

   Dim ws As Worksheet
   Dim newSheetName As String
   newSheetName = Sheets(1).Range("A1")
       For Each ws In Worksheets
           If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
               MsgBox "Sheet already exists or name is invalid", vbInformation
               Exit Sub
           End If
       Next
       Sheets.Add Type:="Worksheet"
       With ActiveSheet
           .Move After:=Worksheets(Worksheets.Count)
           .Name = newSheetName
       End With

End Sub

</source>
   
  


Add a worksheet and append value

   <source lang="vb">

Sub NewWorkbook()

 Dim myWorkbook As Workbook, myWorksheet As Worksheet
 
 Set myWorkbook = Workbooks.Add
 Set myWorksheet = myWorkbook.Worksheets.Add(After:=myWorkbook.Sheets(myWorkbook.Sheets.Count))
 myWorksheet.Name = "January"
 myWorksheet.Range("A1").Value = "Sales Data"
 myWorkbook.SaveAs Filename:="JanSales.xls"

End Sub

</source>
   
  


Add a worksheet and save it

   <source lang="vb">

Sub NewWorkbook()

 Dim myWorksheet As Worksheet
 With Workbooks.Add
   Set myWorksheet = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
   myWorksheet.Name = "January"
   myWorksheet.Range("A1").Value = "Sales Data"
   .SaveAs Filename:="JanSales.xls"
 End With

End Sub

</source>
   
  


Append a worksheet to the end of the worksheet list

   <source lang="vb">

Sub NewWorkbook()

 With Workbooks.Add
   With .Worksheets.Add(After:=.Sheets(.Sheets.Count))
     .Name = "January"
     .Range("A1").Value = "Sales Data"
   End With
   .SaveAs Filename:="JanSales.xls"
 End With

End Sub

</source>
   
  


Create a new sheet with month name and year as its name

   <source lang="vb">

Sub Add_Sheet()

   Dim myWorksheet As Worksheet
   Dim myWorksheetName As String
   myWorksheetName = Format(Now, "mmmm_yyyy")
   For Each myWorksheet In Worksheets
       If myWorksheet.Name = myWorksheetName Then
           MsgBox "Sheet already exists...Make necessary " & _
               "corrections and try again."
           Exit Sub
       End If
   Next myWorksheet
       Sheets.Add.Name = myWorksheetName
       Sheets(myWorksheetName).Move After:=Sheets(Sheets.Count)
       Sheets("Sheet1").Range("A1:A5").Copy Sheets(myWorksheetName).Range("A1")

End Sub

</source>
   
  


Creating a new worksheet for your workbook, and then it fills in several cells in that new worksheet.

   <source lang="vb">

    Sub MyMacro()
        ActiveWorkbook.Worksheets.Add
        Range("A1").Value = "Company Report"
        Range("A2").Value = "Generated by an Excel macro"
        Range("A3").Value = "Generated for " & Application.UserName
    End Sub
</source>
   
  


Inserts new monthly sheet

   <source lang="vb">

Sub NewMonth()

  Dim myWorksheet As Worksheet
  Worksheets(1).Copy After:=Worksheets(1)
  Set myWorksheet = Worksheets(2)
  myWorksheet.Name = Format(DateSerial(2000, 8, 1), "mmm yyyy")
  On Error Resume Next
  myWorksheet.Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents

End Sub

</source>