Мы будем изменять наш макрос, зайдите в пункт меню "макросы", выберите наш и скажите "изменить":

Sub Test()
	Dim book As String
	Dim sheet As String
	Dim addr As String
	addr = "C"
	book = Application.ActiveWorkbook.Name
	sheet = Application.ActiveSheet.Name

	Workbooks(book).Activate
	Worksheets(sheet).Activate

	Range("A1") = book
	Range("B1") = sheet

	Dim xList As Integer
	xList = Application.Sheets.Count
	For x = 1 To xList
		Dim s As String
		s = addr + LTrim(Str(x))
		Range(s) = x
	Next x
End Sub

Программирование на VBA можно рассматривать, как управление обьектами приложения. Вот именно обьектами и управляет наше приложение. В нашем случае, если упростить иерхическую архитектуру, то это выглядит так.

Application
	Workbook	
	........
		Worksheets
		......
			Cell
			.....

То есть главный обьект - приложение. В приложении могут быть несколько книг ( Workbook ), внутри которых находятся листы ( Worksheets ) и листы разбиты на ячейки ( Cell ). При работе активными могут быть только одна книга и один лист. Вот я своим макросом и пытаюсь это выяснить. А заодно сколько листов в текущей книге.

DIM - обьявляет переменную с типом string. Используя обьект Application, мы получаем имена текущих книг и листа. С помощью Range("...") можно выделить ячейку и поместить значения в неё или считать. Вообщем обьекты имеют огромное количество свойств. Задача программиста на VBA знать эти свойства и методы. Ну я думаю мысль этого шага понятна :-))