How to write this code more efficiently..

[omairhe]

Hello,

Could someone guide me as to how can I wrtie this code more efficiently without naming each name of the sheet.

    Sheets(Array("SS", "PQ", "25", "CT", "50", " 100", " 1000", "65", "100 ", "LT ", "SMT", _
        "CD", "PNL")).Copy

Can I type in the code to copy sheets from SS to PNL, without mentioning all the sheets in between? Also if all the names of the sheets are extracted into one sheet named "HSheet" starting from A4 to A17 via formula , could the array be set to look into this range instead of naming every sheet? In both cases using the Array is a must for me.


Thanks and will appreciate.

 

[Fluff]

What is your current code?

 

[omairhe]

What is your current code?

Sub save_as_workbook()
    Dim arr     As Variant
    Dim lastRow As Long
    With Worksheets("HSheet")
        lastRow = Columns("A").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
        arr = Application.Transpose(.Range("A4:A" & lastRow).Value)
    End With
    Sheets(arr).Copy
End Sub

 

[Fluff]

Just put a full stop in-front of Columns.

 

[omairhe]

Just put a full stop in-front of Columns.

lastRow = .Columns("A").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row

Many Thanks, it works.

 

[Fluff]

Glad we could help & thanks for the feedback.