Loop through worksheets (1 Viewer)

geoffcodd

Registered User.
Local time
Today, 15:44
Joined
Aug 25, 2002
Messages
87
Dear all,

I have the following code, I just can't get my head around this, I'm more use too writing code in Access which i so much easier than excel

Code:
Private Sub Workbook_Open() 

Dim ws As Worksheet 

For Each ws In ThisWorkbook.Worksheets 

If ws.Range("A1").Value = "Field01" Then ws.Range("A1").EntireRow.Delete 
    
    With ws 
        .Columns("A:K").AutoFit 
    
        With .Columns("D:D") 

            .HorizontalAlignment = xlGeneral 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
            .MergeCells = False 
        End With 
    
        ws.Range("A1").Select 
        ActiveCell.FormulaR1C1 = "1" 
        Selection.Copy 
        ws.Range("D1").Select 
        ws.Range(Selection, Selection.End(xlDown)).Select 
        Range("D1").Select 
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ 
        SkipBlanks:=False, Transpose:=False 
        ws.Application.CutCopyMode = False 
        
        With Selection 
            .HorizontalAlignment = xlRight 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
            .MergeCells = False 
        End With 
    
        Selection.NumberFormat = "0.00" 
        ws.Range("A1").Select 
        Selection.ClearContents 

        With .Rows("1:1") 

            .HorizontalAlignment = xlLeft 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
            .MergeCells = False 
        End With 

    End With 

Next ws 

End Sub

The bit I am having problems with is

Code:
ws.Range("A1").Select 
        ActiveCell.FormulaR1C1 = "1" 
        Selection.Copy 
        ws.Range("D1").Select 
        ws.Range(Selection, Selection.End(xlDown)).Select 
        Range("D1").Select 
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ 
        SkipBlanks:=False, Transpose:=False 
        ws.Application.CutCopyMode = False 
        
        With Selection 
            .HorizontalAlignment = xlRight 
            .VerticalAlignment = xlBottom 
            .WrapText = False 
            .Orientation = 0 
            .AddIndent = False 
            .IndentLevel = 0 
            .ShrinkToFit = False 
            .ReadingOrder = xlContext 
            .MergeCells = False 
        End With 
    
        Selection.NumberFormat = "0.00" 
        ws.Range("A1").Select 
        Selection.ClearContents

I'm just not sure how to incorporate it into my code, so that it makes the changes to each worksheet as it lops through.

Thanks is advance for your assistance
Geoff
 

HaHoBe

Locomotive Breath
Local time
Today, 16:44
Joined
Mar 1, 2002
Messages
233
Hi, Geoff,

the code looped without problems when I used it on an workbook in Excel97. Some parts could be shortened depending on the contents of your workbook - this seems to be copied from the macro recorder (and some of those items aren´t needed).

Ciao,
Holger
 

Users who are viewing this thread

Top Bottom