Loop through sheet

[joanna_gr]

I need a macro to loop through all sheets of a workbook and perform some formating to cells like border and color format, delete some cells and clear the contents of some other. Any idea please how I can do this? :dunno

[Ken Puls]

Hi there,

Have you searched our KB yet? There's some really good stuff in there. :yes
Looping through all sheets in a range
Remove all cell formatting - Retaining fonts

I'm also going to move this thread to the Excel section for you. ;)

[joanna_gr]

ok. I'm sure i'm doing something wrong but i don't know what. :banghead: I tried the following but it worked in only one sheet it did looping but the only change happened is that i saw the sheet name on cell A1. No format... It happens all day today. I'm getting crazy. I can't make any code to work. :rofl: Please help me..


Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'** Perform code here **
Range("C8:H22").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ws.Range("A1") = ws.Name
Next ws
End Sub

[Ken Puls]

Hi there,
a. I've edited your post above to use them. ;)

Try this out:

Sub LoopThroughSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
'** Perform code here **
With ws.Range("C8:H22")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
ws.Range("A1") = ws.Name
Next ws
End Sub

I changed it to refer to the ws in your loop, and also added a with structure to make it a little clearer (and a bit faster)

HTH,

[Zack Barresse]

Remember Joanna, use this line ...

ws.Range("A1") = ws.Name
.. ONLY if you want to have A1 in each sheet equal the sheet name. That line of code was only in the KBase entry as an example (E.g.).

Fyi: "I changed it to refer to the ws in your loop.." What Ken is talking about, is that if you don't have "ws." prior to your "Range(..", then you will always be looking at the Range of the current Active sheet - which is not necessarily that Range of the Worksheet you are looping through.

[Ken Puls]

Thanks Zack! :)

[joanna_gr]

aha, so that was the big secret... Thanks for helping me. I was about to destroy my pc... lol. And yes I realized that the line : ws.Range("A1") = ws.Name is as an example. In fact I don't really need this in my project. Thank you thank you thank you! :clap: i'm so happy now! :rotlaugh:

firefytr > Thanks for the tip. As I am a very beginner in VBA it's very useful to me to understand how it works and not only watch the result of each code. :)

[Ken Puls]

Hi Joanna,

My apologies for not explaining myself. I was in a bit of a hurry, but usually try to be a bit more descriptive than that. Rest assured that if you're ever in need of further explanation, that you just need to ask, and if the author doesn't respond (which is very unlikely) someone else definately will. We all remember when we started! :yes

Also, did you know that you can mark your own threads solved at VBAX? I'm pretty sure that you've got everything worked out here, but I'll leave it for you to do in case not.

Cheers!

[joanna_gr]

Well, thanks for posting back. I'm happy to know that I can rely on someone for help. I never imagined that VBA is a "magic tool"! Till know i've managed to reduce my work from 2 days in 2 hours. :rotlaugh: To be honest I didn't know that I can mark my posts as solved. I thought that this was up to the administrator to do so. Ehmm, how can I do this??? :dunno


Never mind. I did it!

[Ken Puls]

I thought that this was up to the administrator to do so.

We figured that it's better to put the tools in the hands of the asker, as truly, the issue isn't solved till YOU say so! :yes


I did it!
:thumb And by the way, if no one has said it yet... or what the heck, even if they have... Welcome to VBAX!

[Zack Barresse]

Glad to be of some help to you Joanna! :yes

And yes, welcome!! Ask anytime (no question too small!). :)