How to use Current Region for all sheets

[Trevor G]

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.

 

[chergh]

Can you explain what you are trying to do please

 

[Trevor G]

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.

 

[chergh]

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

 

[Trevor G]

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

 

[chergh]

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

 

[Trevor G]

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.

 

[chergh]

[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

 

[Trevor G]

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.