Repeat action to all sheets in workbook

[suyah]

Hi guys,
Can someone help me how I want to make this codes more shorter? which is I can apply this code in all sheets in open workbook? For workbook that have only 29 sheets is still ok for me, but after this I will have 63 sheets every workbook.

I had found this code, but it's not work for me
I don't understand what "For I = 1 To WS_Count" means.

Dim WS_Count As Integer
Dim I As Integer        
 ' Set WS_Count equal to the number of worksheets in the active        
 ' workbook.         

WS_Count = ActiveWorkbook.Worksheets.Count         
' Begin the loop.         

For I = 1 To WS_Count            
' Insert your code here.            
' The following line shows how to reference a sheet within           
' the loop by displaying the worksheet name in a dialog box.            

MsgBox ActiveWorkbook.Worksheets(I).Name         
Next I      
End Sub

This my code I'm using now.

Sub Date_Copy_Paste()'
' Date_Copy_Paste Macro
'


'
    Sheets("Sheet1").Select
    Columns("A:A").Select
    Selection.Copy
    
    On Error Resume Next
    Sheets("0001").Select
     If Err = 0 Then
    Range("A1").Select
    ActiveSheet.Paste
    Else
    Err.Clear
    On Error GoTo 0
End If


    Sheets("0002").Select
    ActiveSheet.Paste
    Sheets("0003").Select
    ActiveSheet.Paste
    Sheets("0004").Select
    ActiveSheet.Paste
    Sheets("0005").Select
    ActiveSheet.Paste
    Sheets("0006").Select
    ActiveSheet.Paste
    Sheets("0007").Select
    ActiveSheet.Paste
    Sheets("0008").Select
    ActiveSheet.Paste
    Sheets("0009").Select
    ActiveSheet.Paste
    Sheets("0010").Select
    ActiveSheet.Paste
    Sheets("0011").Select
    ActiveSheet.Paste
    Sheets("0012").Select
    ActiveSheet.Paste
    Sheets("0013").Select
    ActiveSheet.Paste
    Sheets("0014").Select
    ActiveSheet.Paste
    Sheets("0015").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("0016").Select
    ActiveSheet.Paste
    Sheets("0017").Select
    ActiveSheet.Paste
    Sheets("0018").Select
    ActiveSheet.Paste
    Sheets("0019").Select
    ActiveSheet.Paste
    Sheets("0020").Select
    ActiveSheet.Paste
    Sheets("0021").Select
    ActiveSheet.Paste
    Sheets("0022").Select
    ActiveSheet.Paste
    Sheets("0023").Select
    ActiveSheet.Paste
    Sheets("0024").Select
    ActiveSheet.Paste
    Sheets("0025").Select
    ActiveSheet.Paste
    Sheets("0026").Select
    ActiveSheet.Paste
    Sheets("0027").Select
    ActiveSheet.Paste
    Sheets("0028").Select
    ActiveSheet.Paste
    Sheets("0029").Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
End Sub

I'm really appreciate, if someone can help me. THANK YOU

 

[suyah]

This actually the codes that I'm going repeat to all sheets in a workbook.

Sub Complete_Clean()'
' Complete_Clean Macro
'


'
    Columns("A:A").Select
    Selection.Copy
    Sheets("0001").Select
    Range("A1").Select
    ActiveSheet.Paste
    Cells.Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    ActiveSheet.Shapes.Range(Array("Button 1")).Select
    Selection.Delete
    Selection.Cut
    ActiveWorkbook.Save
End Sub

 

[wigi]

I don't understand what "For I = 1 To WS_Count" means.

This means that you are looping with a variable called I, from 1 to whatever worksheets the active workbook has.
That number of sheets is stored in the variable called WS_Count (see the definition of that variable).

 

[wigi]

Code-wise, have a look at this code and please experiment (use F8 to step through the entire code):

Sub wigi()

    Dim WS_Count As Integer
    Dim I As Integer
    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.


    WS_Count = ActiveWorkbook.Worksheets.Count
    ' Begin the loop.


    For I = 1 To WS_Count
        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.


        Call Complete_Clean(ActiveWorkbook.Worksheets(I))
        
    Next
    
End Sub


Sub Complete_Clean(ws As Worksheet)
' Complete_Clean Macro
    With ws
        .Columns("A:A").Select
        Selection.Copy
        Sheets("0001").Select
        Range("A1").Select
        ActiveSheet.Paste
        Cells.Select
        Application.CutCopyMode = False
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Range("A1").Select
        ActiveSheet.Shapes.Range(Array("Button 1")).Select
        Selection.Delete
        Selection.Cut
        ActiveWorkbook.Save
    End With
End Sub

The worksheet is passed as a variable to the other function.
With these 2 procedures, you can process the active workbook with any number of sheets in it.