I am trying to achieve selecting a region on each sheet and have tried many different methods but it only seems to apply this to the sheet that is in view on the workbook. Here is what I am using

Sub format7()
For Each sht In ActiveWorkbook.Worksheets
Range("B10").CurrentRegion.Select
Next sht
End Sub

If I can get this to work I can then apply the format.

Can you explain what you are trying to do please

Hi,

I am exporting data into Excel from Access queires (via VBA) each query goes onto an individual sheet, each sheet will have different amount of rows and I am trying to select each range on each sheet and apply a format.

Everything I have tried will do it to the sheet that is in view but not the other sheets. Sometimes there will be around 3 sheets and sometimes around 10, each sheet is named Peer Review with a number.

I can apply headers and footers, rename the sheets, apply page layout and print headings, but not the format?

I want to apply a Double line border around the current region and a single line within the current region.

My ideal is to do this from access, but as mentioned it doesn't even like using current region, so I am looking to see if I can do this from the workbook.

I have attached a copy of the workbook if this will help. In this workbook there are 4 sheets.

I hope this gives enough information.

Should be easy enough. instead of "currentregion" I would probably use "usedrange" but you may have a reason for current region. Try this:



Sub format7()
For Each sht In ActiveWorkbook.Worksheets

with Range("B10").CurrentRegion

With .Borders(xlBottom)

.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic

End With

With .Borders(xlLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End With


Next sht
End Sub

Thank you for trying but this didn't work, but it only applied it to the sheet that was open.

I don't know why the formatting is such a struggle for each sheet. If I added content or formula through For Each it works fine.

If there is any other method I am willing to review it.

Can I use the sheet name like peer review and then apply something to the number of the sheet something like

Ah the mistake was I was being stupid and din't qualify the range. I've had a look at your worksheet and using "usedrange" instead of "currentregion" is more appropriate. this will work.


Sub format7()
For Each sht In ActiveWorkbook.Worksheets

With sht.UsedRange

With .Borders(xlBottom)

.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic

End With

With .Borders(xlLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End With


Next sht
End Sub

Thank you yes it does work, but the format is applying around the first set of headings as well, I wanted them to be separate.

Rows 2 to 4 is a separate table

Row 6 would be the start point.

I am very grateful though as this has improved what I am trying to achieve.

[code]
Sub format7()
For Each sht In ActiveWorkbook.Worksheets

With sht.UsedRange

With .Borders(xlBottom)

.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic

End With

With .Borders(xlLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End With

With sht.Rows("1:5")

.Borders(xlLeft).LineStyle = xlNone
.Borders(xlTop).LineStyle = xlNone
.Borders(xlBottom).LineStyle = xlNone
.Borders(xlRight).LineStyle = xlNone

End With

sht.Range(sht.Range("A5"), sht.Range("A5").End(xlToRight)).Borders(xlTop).LineStyle = xlDouble

Next sht
End Sub

Thank you this works in the way I will want it to.

I see what you have done and can now apply this to the workbook.

Again thank you

I hope you have a lovely weekend.