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.

 

[6StringJazzer]

If this works, what improvement are you trying to get? This code is efficient. That is, it cannot be made to significantly run faster or use less memory. What efficiency are you looking for?

 

[kanadaaa]

You can achieve it without having to write all the sheets' names manually if you use an array and a for loop as in the following:

Sub GetSheetNames()
    Dim i As Long, shNames() As String
    ReDim shNames(Sheets.Count - 1)
    For Each ws In Sheets
        shNames(i) = ws.Name
        i = i + 1
    Next ws
    Debug.Print Join(shNames, vbCrLf)
End Sub

 

[dmt32]

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.

Hi
If you have all valid sheet names listed in a range then maybe something like following will do what you want

Sub CopySheetsListedInRange()
    Dim arr As Variant
    arr = Application.Transpose(Worksheets("HSheet").Range("A4:A17").Value)
    Sheets(arr).Copy
End Sub

Dave

 

[omairhe]

Hi
If you have all valid sheet names listed in a range then maybe something like following will do what you want

Sub CopySheetsListedInRange()
    Dim arr As Variant
    arr = Application.Transpose(Worksheets("HSheet").Range("A4:A17").Value)
    Sheets(arr).Copy
End Sub

Dave

Hello Dave and thanks.
What will change in this code if my range was dynamic? It will start from A4 always but will end at last data cell in column A, which will not always be the 17th row.

 

[kanadaaa]

Sub CopySheetsListedInRange()
    Dim arr As Variant, lr as Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    arr = Application.Transpose(Worksheets("HSheet").Range("A4:A" & lr).Value)
    Sheets(arr).Copy
End Sub

 

[dmt32]

Hello Dave and thanks.
What will change in this code if my range was dynamic? It will start from A4 always but will end at last data cell in column A, which will not always be the 17th row.

Try

Sub CopySheetsListedInRange()
    Dim arr     As Variant
    Dim lastRow As Long
   
    With Worksheets("HSheet")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        arr = Application.Transpose(.Range("A4:A" & lastRow).Value)
    End With
    Sheets(arr).Copy
End Sub

Dave

 

[omairhe]

If I have a formula that returns a blank, I will get the "subscript out of range" message. To get past this I made use this string from other forum.

lastRow = Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

 

[Fluff]

You would be better of using

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

otherwise you may sometimes have problems

 

[omairhe]

You would be better of using

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

otherwise you may sometimes have problems

I am unable to run the codes when my active sheet is not "HSheet". How to solve this please.