• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

adding sheet names to sheet

Hi,

I've a macro which formats a sheet

    Sub Csformatting_6()
   
       'Dim wks As Worksheet
    'For Each wks In ActiveWorkbook.Worksheets
     
   
    Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Select
   
   
      ActiveWindow.DisplayGridlines = False
   
        Cells.Select
    Selection.Sort Key1:=Range("G2"), Order1:=xlDescending, Key2:=Range("C2") _
        , Order2:=xlAscending, header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Selection.Insert Shift:=xlDown
    Range("A9").Select
   
   
        Cells.Select
   
    Range("A:Z").Activate
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
         
          For lRow = 2 To Cells.SpecialCells(xlCellTypeLastCell).row
             
                If Cells(lRow, 1).Value <> " " Then
             
                   
                    Range("K" & lRow).NumberFormat = "0%"
                    Range("L" & lRow).NumberFormat = "0%"
                    Range("O" & lRow).NumberFormat = "0%"
                    Range("P" & lRow).NumberFormat = "0%"
             
                    Range("S" & lRow).NumberFormat = "0%"
                    Range("T" & lRow).NumberFormat = "0%"
                    Range("W" & lRow).NumberFormat = "0%"
                    Range("X" & lRow).NumberFormat = "0%"
               
               
               
               
              End If
           
        Next lRow
       
       
    Columns("C:H").Select
    Range("H1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
       
    Columns("J:J").Select
       Selection.Delete Shift:=xlToLeft
       
    Columns("M:M").Select
       Selection.Delete Shift:=xlToLeft
    Columns("P:P").Select
        Selection.Delete Shift:=xlToLeft
   
   
    Columns("C:C").Select
       Selection.Delete Shift:=xlToLeft
   
    Rows("4:4").Select
    Selection.Delete Shift:=xlUp
   
    Columns("A:A").ColumnWidth = 17.14
 
   
    '
    'ActiveWindow.Zoom = 75
       
       
         
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
        .Font.Size = 12
        .Font.ColorIndex = 1
        .Font.Bold = True
    End With
       
       
       
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
       

Columns("A:A").ColumnWidth = 8.43
Columns("B:B").ColumnWidth = 22.14
Columns("C:C").ColumnWidth = 1.71

    Columns("D:D").ColumnWidth = 7.43
    Columns("E:E").ColumnWidth = 6.86
    Columns("F:F").ColumnWidth = 6.86
    Columns("G:G").ColumnWidth = 1.71
    Columns("H:H").ColumnWidth = 7.29
    Columns("I:I").ColumnWidth = 5.86
    Columns("J:J").ColumnWidth = 6.86
    Columns("K:K").ColumnWidth = 1.57
    Columns("L:L").ColumnWidth = 6.86
    Columns("M:M").ColumnWidth = 7.01
    Columns("N:N").ColumnWidth = 5.86
    Columns("O:O").ColumnWidth = 1.57
    Columns("P:P").ColumnWidth = 6.43
    Columns("Q:Q").ColumnWidth = 6.29
    Columns("R:R").ColumnWidth = 7.14
    Columns("S:S").ColumnWidth = 8.4
             
    '    Rows("4:4").Select
    'Selection.Delete Shift:=xlUp
             
    '    Rows("4:4").Select
   ' Selection.Delete Shift:=xlUp
             
    '          End If
   
        Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C6").Select
    Columns("C:C").ColumnWidth = 2.86
   
         'Range("A2").Select
         'ActiveCell.Value = getSheetName()
   
   
    'Range("A1").Value = getSheetName()
   
   
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
        .Font.Size = 14
        .Font.ColorIndex = 1
        .Font.Bold = True
    End With
   
   
        Columns("H:O").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("A2").Select
   
   
        Range("G1:G1000,K1:K1000,O1:O1000").Select
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 15
    End With
   
   
    Columns("A:A").ColumnWidth = 8.43
Columns("B:B").ColumnWidth = 22.14
Columns("C:C").ColumnWidth = 1.71

    Columns("D:D").ColumnWidth = 7.43
    Columns("E:E").ColumnWidth = 6.86
    Columns("F:F").ColumnWidth = 6.86
    Columns("G:G").ColumnWidth = 1.71
    Columns("H:H").ColumnWidth = 7.29
    Columns("I:I").ColumnWidth = 5.86
    Columns("J:J").ColumnWidth = 6.86
    Columns("K:K").ColumnWidth = 1.57
    Columns("L:L").ColumnWidth = 6.86
    Columns("M:M").ColumnWidth = 7.01
    Columns("N:N").ColumnWidth = 5.86
    Columns("O:O").ColumnWidth = 1.57
    Columns("P:P").ColumnWidth = 6.43
    Columns("Q:Q").ColumnWidth = 6.29
    Columns("R:R").ColumnWidth = 7.14
    Columns("S:S").ColumnWidth = 8.4
   
   ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
 
        .Orientation = xlLandscape
    End With
   
        Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
   
   
   
    Range("A1").Select
   
        Cells.Select
    With Selection.Font
        '.Name = "Arial"
        .ColorIndex = 1
    End With
   
    Columns("C:Q").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With

 ActiveWindow.DisplayGridlines = False
 
    Range("A1").Select

   ' Call getrow_start_end
   
   'Call Header_Open
   

   Next


End Sub

The macro loops through a number of sheets and I need to add the sheet name  of each sheet to the A1 cell of each seet within the loop

Any help would be mu h appreciated.

Regards,

0
LINNANDA
Asked:
LINNANDA
1 Solution
 
James ElliottManaging DirectorCommented:
Try adding this in the loop

wks.Range("A1") = wks.Name


Probably should go here:

 Sub Csformatting_6()
   
       'Dim wks As Worksheet
    'For Each wks In ActiveWorkbook.Worksheets
     
   
    Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Select

wks.Range("A1") = wks.Name
0
 
LINNANDAAuthor Commented:
Tnaks jell, that worked fine.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now