How do I create labels of spreadsheets/worksheets?

  • Thread starter Thread starter Lidia
  • Start date Start date
I THINK you want to create printed AVERY type labels for each worksheet for
file folders????
If so...
1) Create a blank worksheet in the workbook that you want the labels for.
2) The macro below [MakeWkstLabels] will create a list of the worksheets in
your workbook and adjust for printing to an Avery Label 5066 or Avery label
5266...
'/============================/
Sub MakeWkstLabels()
'NOTES
'72 points = 1 inch
'Excel printing uses 71 points
'Avery Label for File Folders are
' label #s 5066 and 5266
'Avery Labels 5066 & 5266 are
' .54 inches high
'Therefore, .54/71 = row height of 38.34
'---------------
Dim wks As Worksheet

'add a blank sheet
Sheets.Add

For Each wks In Worksheets
With ActiveCell
'put worksheet name in cell
.Value = wks.Name
'adj row height for Avery Labels
.Rows("1:1").RowHeight = 38.34
'Center the worksheet name horizontally
.HorizontalAlignment = xlCenter
'Center the worksheet name vertically
.VerticalAlignment = xlCenter
'go to the next cell and repeat process
.Offset(1, 0).Activate
End With
Next wks

'fit the column width to the worksheet names
Columns("A:A").EntireColumn.AutoFit

End Sub
'/============================/

3) Put your Avery Labels in the printer
4) Print
 
Back
Top