Loop through Worksheets

[geoffcodd]

Hi there,

I have the following worksheet

Journal_Template1.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Field01	Field02	Field03	Field04	Field05	Field06	Field07	Field08	Field09	Field10	Field11
2		Profitcenter	G/Laccountno.	Value	Text	Region	ProductGroup4	Segment2	Channel	Billed/Unbilled	Quantity
3	P	/	/	-2584.78	NRRDeferralReleaseRevenue	ZZ	91	13	ZZ	1
4	P	/	/	-2086.1	NRRDeferralReleaseRevenue	ZZ	91	99	ZZ	1
5	P	14500000	46000000	35042982.01	NRRDeferralReleaseRevenue
6	P	/	/	2766.98	NRRDeferralReleaseRevenue	ZZ	91	13	ZZ	1
7	P	/	/	2295.38	NRRDeferralReleaseRevenue	ZZ	91	99	ZZ	1
8	P	14500000	46000000	-37950681.26	NRRDeferralNewDeferral
9
10
11		Header
12		Comp:	2352	-72993663.27	B/Saccount	-1
13		FinYear:	2005	72993663.27	G&Laccount
14		FinPeriod:	10
15		DocType	G1
16		DocCurrency:	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

 

[Richard Schollar]

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:

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

 

[geoffcodd]

Thanks for that works great, I am trying to expand on the code but with no luck, here is the code I am using

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

 

[Richard Schollar]

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:

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

 

[geoffcodd]

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

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,

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

 

[geoffcodd]

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