VBA/Excel/Access/Word/Excel/Worksheet Add
Версия от 19:33, 26 мая 2010; (обсуждение)
Содержание
- 1 Add a new Sheet
- 2 Add a new sheet with name in A1
- 3 Add a worksheet and append value
- 4 Add a worksheet and save it
- 5 Append a worksheet to the end of the worksheet list
- 6 Create a new sheet with month name and year as its name
- 7 Creating a new worksheet for your workbook, and then it fills in several cells in that new worksheet.
- 8 Inserts new monthly sheet
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>