Macro Loop with Dynamic Range

Question

I'm trying to automate the following process using a Excel but I'm experiencing some difficulties as obviously I need to set up a variable within the OFFSET function:

Sheets("XXX").Visible = True
Sheets("XXX").Select
ActiveWorkbook.Names.Add Name:="XXX_aaa", RefersToR1C1:= _
    "=OFFSET('XXX'!R2C1,0,1,COUNTA('XXX'!C1),21)"
Sheets("XXX").Visible = False

Sheets("YYY").Visible = True
Sheets("YYY").Select
ActiveWorkbook.Names.Add Name:="YYY_bbb", RefersToR1C1:= _
    "=OFFSET('YYY'!R2C1,0,1,COUNTA('YYY'!C1),21)"
Sheets("YYY").Visible = False

Sheets("ZZZ").Visible = True
Sheets("ZZZ").Select
ActiveWorkbook.Names.Add Name:="ZZZ_ccc", RefersToR1C1:= _
    "=OFFSET('ZZZ'!R2C1,0,1,COUNTA('ZZZ'!C1),21)"
Sheets("ZZZ").Visible = False`

Is there an easy macro function I can use to automate this task (it has to be repeated 30 times !)

Thanks !

Repeat 30 times is that how many sheets you have? Can you give us the Sheet Index you want to repeat it for? Sheets 1:30? or 2:31 for example? You have to at least have 1 sheet always visible also — user2140261
– user2140261, Commented Jun 11, 2013 at 16:02
Hi Yes I have a Control Sheet. It has to be repeated for Sheets 2 to 31. — user2475110
– user2475110, Commented Jun 11, 2013 at 16:10

user2140261 · Accepted Answer · 2013-06-11 16:20:02Z

1

Try this on a COPY of the workbook you are working with:

Sub Sample()
Dim intCurrentSheet As Integer
Dim lngLastRow As Long

For intCurrentSheet = 2 To 31

    lngLastRow = Sheets(intCurrentSheet).Range("U1048576").End(xlUp).Row

    Sheets(intCurrentSheet).Range("A2:U" & lngLastRow).Name = Sheets(intCurrentSheet).Name & _
                                                           "_" & Chr(intCurrentSheet + 63) & _
                                                           Chr(intCurrentSheet + 63) & _
                                                           Chr(intCurrentSheet + 63)
    Sheets(intCurrentSheet).Visible = False

Next intCurrentSheet

End Sub

answered Jun 11, 2013 at 16:20

user2140261

8,0037 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Macro Loop with Dynamic Range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related