[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3633
  • Last Modified:

looping through work sheets using vba

Hi,
I've a macro which formats an excel spreadsheet but when I try and use this macro to loop thoruh all the worksheets in a workbook it only loops through the same worksheet

Sub Csformatting_6()
   
   'added as part of the loop

       Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
     
   
        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
   
    Call Discretionary_b
    Call Discretionary_c
    Call Discretionary_d
    Call Discretionary_e
    Call Discretionary_f

    Call zadvisory_a
   
    Call zadvisory_b
    Call zadvisory_c
    Call zadvisory_d
    Call zadvisory_e
    Call zadvisory_f
   
       
          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
       
       
         Range("A4").Select
         ActiveCell.Value = "Discretionary Accounts"
         
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
        .Font.Size = 12
        .Font.ColorIndex = 1
    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 = 14
Columns("B:B").ColumnWidth = 36

    Columns("D:D").ColumnWidth = 7.57
    Columns("E:E").ColumnWidth = 9.43
     Columns("F:F").ColumnWidth = 7.29
    Columns("H:H").ColumnWidth = 8.86
    Columns("I:I").ColumnWidth = 10.29
    Columns("M:M").ColumnWidth = 8.57
    Columns("L:L").ColumnWidth = 7.14
    Columns("N:N").ColumnWidth = 7.86
    Columns("O:O").ColumnWidth = 6.14
   
    Columns("P:P").ColumnWidth = 8.14
    Columns("Q:Q").ColumnWidth = 8.43

   
       If Range("A4").Value = "Discretionary Accounts" Then
             
        Rows("4:4").Select
    Selection.Delete Shift:=xlUp
             
        Rows("4:4").Select
    Selection.Delete Shift:=xlUp
             
              End If

   'added as part of the loop
 
    Next wks

   
End Sub
0
LINNANDA
Asked:
LINNANDA
1 Solution
 
Jorge PaulinoIT Pro/DeveloperCommented:
Hi LINNANDA,

You macro is working adn you only want to do the same in all worksheets ? You have to select the worksheet after the for cycle.

Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Select

' You format code

Next
0
 
dentabCommented:
^ agreed
0
 
J1H1W1Commented:
If you don't want to make the sheet active and visible, use a
  With wks
    .Cells.Select
    ...
  End With
Next wks
Contruct.
You just prepend a period to objects on the worksheet.
0
 
dentabCommented:
You dont even need to select the cells
You can set the value of each cell specifically.
0
 
LINNANDAAuthor Commented:
that worked fine.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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