Compromise with a combination of object variables and With...End With:

 
     Sub NewWorkbook()
         Dim myWorkbook As Workbook, myWorksheet As Worksheet

         Set myWorkbook = Workbooks.add
         With myWorkbook
             Set myWorksheet = .Worksheets.add(After:=.Sheets(.Sheets.Count))
             With myWorksheet
               .name = "January"
               .range("A1").value = "Sales Data"
             End With
             .SaveAs FileName:="YourExcel2007File.xlsx"
         End With
     End Sub

Related examples in the same category

1.	The Object Data Type Set
2.	Object Data Types
3.	Object Variables
4.	reference can be assigned to an object variable so that you can easily refer to the new object in later code
5.	The difference between object variables and regular variables
6.	VBA has the ability to interact with Access to help create and edit objects
7.	Object variable
8.	With...End With
9.	VBA knows that anything starting with a period is a property or a method of the object following the With
10.	eliminate the object variable when using With statement
11.	Identifying the type of a selected object: uses the TypeName function, which returns the type of the selected object (as a string):

Compromise with a combination of object variables and With...End With: : Object Variable Set « Data Type « VBA / Excel / Access / Word

Related examples in the same category