Option Explicit
Sub looPaevik()
  Dim ainenr As Integer
  Dim raamat As Workbook
  Dim leht As Worksheet
  Dim algleht As Worksheet
  Set algleht = Workbooks("paevikuhaldus.xls"). _
                 Worksheets("algandmed")
  Set raamat = Workbooks.Add
  ainenr = 1
  Do While Len(algleht.Cells(ainenr, 3)) > 0
    Set leht = raamat.Worksheets.Add
    leht.Name = algleht.Cells(ainenr, 3)
    ainenr = ainenr + 1
  Loop
  raamat.SaveAs "c:\temp\paevik.xls"
End Sub

Sub looNimedegaPaevik()
  Dim ainenr As Integer
  Dim nimenr As Integer
  Dim raamat As Workbook
  Dim leht As Worksheet
  Dim algleht As Worksheet
  Set algleht = Workbooks("paevikuhaldus.xls"). _
                 Worksheets("algandmed")
  Set raamat = Workbooks.Add
  ainenr = 1
  Do While Len(algleht.Cells(ainenr, 3)) > 0
    Set leht = raamat.Worksheets.Add
    leht.Name = algleht.Cells(ainenr, 3)
    nimenr = 1
    Do While Len(algleht.Cells(nimenr, 1)) > 0
       leht.Cells(nimenr, 1) = algleht.Cells(nimenr, 1)
       nimenr = nimenr + 1
    Loop
    ainenr = ainenr + 1
  Loop
  Application.DisplayAlerts = False
  raamat.SaveAs "c:\temp\paevik.xls", CreateBackup:=True
  raamat.Close
End Sub

Sub jukuHinded()
  Dim paevikuraamat As Workbook
  Dim kokkuvotteraamat As Workbook
  Dim algleht As Worksheet
  Dim jukuleht As Worksheet
  Dim ainenr As Integer
  Dim nimenr As Integer
  Dim ainenimi As String
  Set algleht = Workbooks("paevikuhaldus.xls").Worksheets("algandmed")
  Set paevikuraamat = Workbooks("paevik.xls")
  Set kokkuvotteraamat = Workbooks.Add
  Set jukuleht = kokkuvotteraamat.Sheets.Add
  jukuleht.Name = "Juku"
  nimenr = 3
  ainenr = 1
  Do While Len(algleht.Cells(ainenr, 3)) > 0
     ainenimi = algleht.Cells(ainenr, 3)
     jukuleht.Cells(ainenr, 1) = ainenimi
     jukuleht.Cells(ainenr, 2) = _
         paevikuraamat.Sheets(ainenimi).Cells(nimenr, 2)
     ainenr = ainenr + 1
  Loop
End Sub