Loop through Worksheets

geoffcodd

New Member
Joined
Jul 26, 2004
Messages
14
Hi there,

I have the following worksheet
Journal_Template1.xls
ABCDEFGHIJK
1Field01Field02Field03Field04Field05Field06Field07Field08Field09Field10Field11
2ProfitcenterG/Laccountno.ValueTextRegionProductGroup4Segment2ChannelBilled/UnbilledQuantity
3P//-2584.78NRRDeferralReleaseRevenueZZ9113ZZ1
4P//-2086.1NRRDeferralReleaseRevenueZZ9199ZZ1
5P145000004600000035042982.01NRRDeferralReleaseRevenue
6P//2766.98NRRDeferralReleaseRevenueZZ9113ZZ1
7P//2295.38NRRDeferralReleaseRevenueZZ9199ZZ1
8P1450000046000000-37950681.26NRRDeferralNewDeferral
9
10
11Header
12Comp:2352-72993663.27B/Saccount-1
13FinYear:200572993663.27G&Laccount
14FinPeriod:10
15DocTypeG1
16DocCurrency:USD
2352


What I need to do is that if the value in Cell A1 = Field01 then I want to delete the Row 1. I need this to loop through all Sheets in the workbook and do the same. One last thing is that I need this to happen everytime the workbook is opened.

Thanks in advance for any help.

Thanks
Geoff
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Geoff

When you say:

What I need to do is that if the value in Cell A1 = Field01 then I want to delete the Row 1

Do you mean: when the value of A1 is a string "Field01"? In which case, placing the following in the Workbook module should suffice:

Code:
Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Range("A1").Value = "Field0" Then ws.Range("A1").EntireRow.Delete

Next ws

End Sub

Best regards

Richard
 
Upvote 0

geoffcodd

New Member
Joined
Jul 26, 2004
Messages
14
Thanks for that works great, I am trying to expand on the code but with no luck, here is the code I am using

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
    
    Columns("A:K").Select
    Selection.Columns.AutoFit
    
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("A1").Select

Next ws

End Sub

Any ideas where I'm going wrong. Thanks Geoff
 
Upvote 0

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Geoff

I think this will cause you problems because even though you are in the For...Next loop, when you specify Columns("A:K") for example, you have not specified this should be selected on a particular worksheet, hence it defaults to the active worksheet (every time). So to fix:

Code:
Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

If ws.Range("A1").Value = "Field0" 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
    
        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

Hope this works for you!

Best regards

Richard
 
Upvote 0

geoffcodd

New Member
Joined
Jul 26, 2004
Messages
14
Thanks Parsnip, that worked great.

I have another problem now, I just can't get my head around this, I'm more use towrithing 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 area I am having problem 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.

Thanks for your assistance
Geoff
 
Upvote 0

geoffcodd

New Member
Joined
Jul 26, 2004
Messages
14
Hi all,

I hate to be a pain, but can any of you expets out there help me as I am stuck to a realy tight deadline with this.

Thanks for your time
Geoff
 
Upvote 0

Forum statistics

Threads
1,195,671
Messages
6,011,077
Members
441,581
Latest member
rp4717

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top