none
How to put correct name for sheet in loop RRS feed

  • Question

  • Hi

    I posted you my code , I need you to correct one line . 

    I want to read A1 to A137 into   sheets().activate  by counting i . But when I type  sheet(A & i ).Activate  it errors me "Subscript out of Range" and A is empty .

    Thanks 

    Sub CopymMonthlydatainForecastedGrid()
    
    Dim j As Integer
    
    A1 = "30-48.75"
    A2 = "30-48.5"
    A3 = "30.25-48.5"
    A4 = "30.25-48.25"
    A5 = "30.25-51.75"
    A6 = "30.5-51.5"
    A7 = "30.5-48.25"
    A8 = "30.5-48"
    A9 = "30.5-51.75"
    A10 = "30.5-51.25"
    A11 = "30.75-48.5"
    A12 = "30.75-48.25"
    A13 = "30.75-48"
    A14 = "30.75-51.75"
    A15 = "30.75-51.5"
    A16 = "30.75-51.25"
    A17 = "31-52"
    A18 = "31-51.75"
    A19 = "31-51.5"
    A20 = "31-51.25"
    A21 = "31-51"
    A22 = "31-48.75"
    A23 = "31-48.5"
    A24 = "31-48.25"
    A25 = "31-48"
    A26 = "31.25-52"
    A27 = "31.25-51.5"
    A28 = "31.25-50.75"
    A29 = "31.25-48.5"
    A30 = "31.25-51.75"
    A31 = "31.25-51.25"
    A32 = "31.25-51"
    A33 = "31.25-50.5"
    A34 = "31.25-48.75"
    A35 = "31.25-48.25"
    A36 = "31.5-49.75"
    A37 = "31.5-49.25"
    A38 = "31.5-51.75"
    A39 = "31.5-51.5"
    A40 = "31.5-51.25"
    A41 = "31.5-51"
    A42 = "31.5-50.75"
    A43 = "31.5-50.5"
    A44 = "31.5-50.25"
    A45 = "31.5-49.5"
    A46 = "31.5-49"
    A47 = "31.5-48.75"
    A48 = "31.5-48.5"
    A49 = "31.75-51.5"
    A50 = "31.75-48.75"
    A51 = "31.75-48.5"
    A52 = "31.75-49.5"
    A53 = "31.75-49.25"
    A54 = "31.75-49"
    A55 = "31.75-50.25"
    A56 = "31.75-50"
    A57 = "31.75-49.75"
    A58 = "31.75-51"
    A59 = "31.75-50.75"
    A60 = "31.75-50.5"
    A61 = "31.75-51.75"
    A62 = "31.75-51.25"
    A63 = "32-50.75"
    A64 = "32-49.25"
    A65 = "32-48.5"
    A66 = "32-50"
    A67 = "32-48.75"
    A68 = "32-48.25"
    A69 = "32-49.5"
    A70 = "32-49"
    A71 = "32-50.25"
    A72 = "32-49.75"
    A73 = "32-51"
    A74 = "32-50.5"
    A75 = "32-51.25"
    A76 = "32.25-48.75"
    A77 = "32.25-48.5"
    A78 = "32.25-48.25"
    A79 = "32.25-49.5"
    A80 = "32.25-49.25"
    A81 = "32.25-49"
    A82 = "32.25-50.25"
    A83 = "32.25-50"
    A84 = "32.25-49.75"
    A85 = "32.25-51"
    A86 = "32.25-50.75"
    A87 = "32.25-50.5"
    A88 = "32.25-51.25"
    A89 = "32.5-50.75"
    A90 = "32.5-51"
    A91 = "32.5-50.5"
    A92 = "32.5-50.25"
    A93 = "32.5-50"
    A94 = "32.5-49.75"
    A95 = "32.5-49.5"
    A96 = "32.5-49.25"
    A97 = "32.5-49"
    A98 = "32.5-48.75"
    A99 = "32.5-48.5"
    A100 = "32.5-48.25"
    A101 = "32.75-50"
    A102 = "32.75-49.25"
    A103 = "32.75-48.5"
    A104 = "32.75-50.25"
    A105 = "32.75-49.75"
    A106 = "32.75-49.5"
    A107 = "32.75-49"
    A108 = "32.75-48.75"
    A109 = "32.75-48.25"
    A110 = "33-50"
    A111 = "33-49.75"
    A112 = "33-49.5"
    A113 = "33-49.25"
    A114 = "33-49"
    A115 = "33-48.75"
    A116 = "33-48.5"
    A117 = "33-48.25"
    A118 = "33.25-48.5"
    A119 = "33.25-48.75"
    A120 = "33.25-49.5"
    A121 = "33.25-49.25"
    A122 = "33.25-49"
    A123 = "33.25-50"
    A124 = "33.25-49.75"
    A125 = "33.5-49.75"
    A126 = "33.5-49.25"
    A127 = "33.5-48.75"
    A128 = "33.5-49.5"
    A129 = "33.5-49"
    A130 = "33.75-48.75"
    A131 = "33.75-48.5"
    A132 = "33.75-49.5"
    A133 = "33.75-49.25"
    A134 = "33.75-49"
    A135 = "33.75-49.75"
    A136 = "34-48.5"
    A137 = "34-48.75"
    B = "Attribute("
    C = ")"
    
    
    
    Windows("16-2008Jan.xlsx").Activate
    
    For i = 1 To 137
    
    'here I have problem
    Sheets(A & i).Activate
    
    
    For j = 1 To 50
    Range("F1:F1551").AutoFilter Field:=1, Criteria1:=(j)
    Range("M6").Select
    Selection.Formula = "=Subtotal(109,C:C)"
    Selection.Copy
    Windows("Forecasted-grid-tiessen_mask_in_karun 2008Jan.xlsx").Activate
    Sheets(B & j & C).Activate
    Columns("F:F").Select
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).PasteSpecial Paste:=xlPasteValues
    Windows("16-2008Jan.xlsx").Activate
    Range("F1:F1551").AutoFilter
    Range("M6").Clear
    Next j
    Next i
    
    End Sub


    Friday, February 20, 2015 12:57 AM

Answers

  • Hi,

    You have to use an array instead of single variables to put your sheet names.

    Here a simple example for 3 sheets.

    Option Base 1    ' sets first index of array to 1 
    
    Sub CopyMonthlyDataInForecastedGrid()
        Const NumberOfSheets = 3    ' set to number of sheets to change
        Dim A(NumberOfSheets) As String
        Dim j As Integer
        
        A(1) = "30-48.75"
        A(2) = "30-48.5"
        A(3) = "30.25-48.5"
    
        For j = 1 To NumberOfSheets
            Sheets(A(j)).Activate
            ActiveSheet.Range("A1").Value = Time  ' writes time to cell A1
        Next
        
    End Sub
    

    Notes:

    • You have to place the statement Option Base 1 on top of the module (if you don't use "Option Base 1", the array starts with index 0 (Zero)).
    • You have to addapt the constant NumberOfSheets to your needs.
    • The declarition of the constant NumberOfSheets has to be before the declaration of the array.

    Best regards

    Markus


    Markus Schmid

    • Proposed as answer by L.HlModerator Friday, February 27, 2015 8:26 AM
    • Marked as answer by L.HlModerator Monday, March 2, 2015 1:31 AM
    Wednesday, February 25, 2015 8:35 AM

All replies

  • Hi

    I posted you my code , I need you to correct one line . 

    I want to read A1 to A137 into   sheets().activate  by counting i . But when I type  sheet(A & i ).Activate  it errors me "Subscript out of Range" and A is empty .

    Thanks 

    Sub CopymMonthlydatainForecastedGrid()
    
    Dim j As Integer
    
    A1 = "30-48.75"
    A2 = "30-48.5"
    A3 = "30.25-48.5"
    A4 = "30.25-48.25"
    A5 = "30.25-51.75"
    A6 = "30.5-51.5"
    A7 = "30.5-48.25"
    A8 = "30.5-48"
    A9 = "30.5-51.75"
    A10 = "30.5-51.25"
    A11 = "30.75-48.5"
    A12 = "30.75-48.25"
    A13 = "30.75-48"
    A14 = "30.75-51.75"
    A15 = "30.75-51.5"
    A16 = "30.75-51.25"
    A17 = "31-52"
    A18 = "31-51.75"
    A19 = "31-51.5"
    A20 = "31-51.25"
    A21 = "31-51"
    A22 = "31-48.75"
    A23 = "31-48.5"
    A24 = "31-48.25"
    A25 = "31-48"
    A26 = "31.25-52"
    A27 = "31.25-51.5"
    A28 = "31.25-50.75"
    A29 = "31.25-48.5"
    A30 = "31.25-51.75"
    A31 = "31.25-51.25"
    A32 = "31.25-51"
    A33 = "31.25-50.5"
    A34 = "31.25-48.75"
    A35 = "31.25-48.25"
    A36 = "31.5-49.75"
    A37 = "31.5-49.25"
    A38 = "31.5-51.75"
    A39 = "31.5-51.5"
    A40 = "31.5-51.25"
    A41 = "31.5-51"
    A42 = "31.5-50.75"
    A43 = "31.5-50.5"
    A44 = "31.5-50.25"
    A45 = "31.5-49.5"
    A46 = "31.5-49"
    A47 = "31.5-48.75"
    A48 = "31.5-48.5"
    A49 = "31.75-51.5"
    A50 = "31.75-48.75"
    A51 = "31.75-48.5"
    A52 = "31.75-49.5"
    A53 = "31.75-49.25"
    A54 = "31.75-49"
    A55 = "31.75-50.25"
    A56 = "31.75-50"
    A57 = "31.75-49.75"
    A58 = "31.75-51"
    A59 = "31.75-50.75"
    A60 = "31.75-50.5"
    A61 = "31.75-51.75"
    A62 = "31.75-51.25"
    A63 = "32-50.75"
    A64 = "32-49.25"
    A65 = "32-48.5"
    A66 = "32-50"
    A67 = "32-48.75"
    A68 = "32-48.25"
    A69 = "32-49.5"
    A70 = "32-49"
    A71 = "32-50.25"
    A72 = "32-49.75"
    A73 = "32-51"
    A74 = "32-50.5"
    A75 = "32-51.25"
    A76 = "32.25-48.75"
    A77 = "32.25-48.5"
    A78 = "32.25-48.25"
    A79 = "32.25-49.5"
    A80 = "32.25-49.25"
    A81 = "32.25-49"
    A82 = "32.25-50.25"
    A83 = "32.25-50"
    A84 = "32.25-49.75"
    A85 = "32.25-51"
    A86 = "32.25-50.75"
    A87 = "32.25-50.5"
    A88 = "32.25-51.25"
    A89 = "32.5-50.75"
    A90 = "32.5-51"
    A91 = "32.5-50.5"
    A92 = "32.5-50.25"
    A93 = "32.5-50"
    A94 = "32.5-49.75"
    A95 = "32.5-49.5"
    A96 = "32.5-49.25"
    A97 = "32.5-49"
    A98 = "32.5-48.75"
    A99 = "32.5-48.5"
    A100 = "32.5-48.25"
    A101 = "32.75-50"
    A102 = "32.75-49.25"
    A103 = "32.75-48.5"
    A104 = "32.75-50.25"
    A105 = "32.75-49.75"
    A106 = "32.75-49.5"
    A107 = "32.75-49"
    A108 = "32.75-48.75"
    A109 = "32.75-48.25"
    A110 = "33-50"
    A111 = "33-49.75"
    A112 = "33-49.5"
    A113 = "33-49.25"
    A114 = "33-49"
    A115 = "33-48.75"
    A116 = "33-48.5"
    A117 = "33-48.25"
    A118 = "33.25-48.5"
    A119 = "33.25-48.75"
    A120 = "33.25-49.5"
    A121 = "33.25-49.25"
    A122 = "33.25-49"
    A123 = "33.25-50"
    A124 = "33.25-49.75"
    A125 = "33.5-49.75"
    A126 = "33.5-49.25"
    A127 = "33.5-48.75"
    A128 = "33.5-49.5"
    A129 = "33.5-49"
    A130 = "33.75-48.75"
    A131 = "33.75-48.5"
    A132 = "33.75-49.5"
    A133 = "33.75-49.25"
    A134 = "33.75-49"
    A135 = "33.75-49.75"
    A136 = "34-48.5"
    A137 = "34-48.75"
    B = "Attribute("
    C = ")"
    
    
    
    Windows("16-2008Jan.xlsx").Activate
    
    For i = 1 To 137
    
    'here I have problem
    Sheets(A & i).Activate
    
    
    For j = 1 To 50
    Range("F1:F1551").AutoFilter Field:=1, Criteria1:=(j)
    Range("M6").Select
    Selection.Formula = "=Subtotal(109,C:C)"
    Selection.Copy
    Windows("Forecasted-grid-tiessen_mask_in_karun 2008Jan.xlsx").Activate
    Sheets(B & j & C).Activate
    Columns("F:F").Select
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).PasteSpecial Paste:=xlPasteValues
    Windows("16-2008Jan.xlsx").Activate
    Range("F1:F1551").AutoFilter
    Range("M6").Clear
    Next j
    Next i
    
    End Sub


    What do these numbers (for instance 33-50) represent. Are these sheet names?


    • Edited by John Martel Friday, February 20, 2015 1:32 AM
    Friday, February 20, 2015 1:29 AM
  • Yes they are sheet names but the do not have any special format to read them in a loop  , and then I decided to give them a special format by defining them as variable with a letter (here is A) and a figure ( hear is 1 to 137 ) . 

    and I want to activate each of them by changing i from 1 to 137 in sheet name . How should I do !?

    Thanks

    Friday, February 20, 2015 8:08 AM
  • Yes they are sheet names but the do not have any special format to read them in a loop  , and then I decided to give them a special format by defining them as variable with a letter (here is A) and a figure ( hear is 1 to 137 ) . 

    and I want to activate each of them by changing i from 1 to 137 in sheet name . How should I do !?

    Thanks

    You don't need to activate the sheet. Just use the following:

        Dim sh As Worksheet
        For Each sh In ThisWorkbook
            If Mid(sh.Name, 1, 1) = "3" Then
                ....

    It basically loops through all the sheets in the workbook and checks if the name starts with a 3. Then instead of activating a sheet. Use the referenced object by "sh". So

    Range("F1:F1551").AutoFilter

    Becomes

    sh.Range("F1:F1551").AutoFilter

    Friday, February 20, 2015 1:21 PM
  • But there are many sheets that start with "3" , I just want to call exact mentioned sheets and run a macro .

    Is not really a way to improve my code , because in that way I will read them exactly . If so I will appreciate any help from you . here is my macro that I want to run for mentioned sheets

    For j = 1 To 50
    Range("F1:F1551").AutoFilter Field:=1, Criteria1:=(j)
    Range("M6").Select
    Selection.Formula = "=Subtotal(109,C:C)"
    Selection.Copy
    Windows("Forecasted-grid-tiessen_mask_in_karun 2008Jan.xlsx").Activate
    Sheets(B & j & C).Activate
    Columns("F:F").Select
    Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).PasteSpecial Paste:=xlPasteValues
    Windows("16-2008Jan.xlsx").Activate
    Range("F1:F1551").AutoFilter
    Range("M6").Clear
    Next j

     

    Thanks


    Friday, February 20, 2015 2:43 PM
  • Hi,

    >>But when I type  sheet(A & i ).Activate  it errors me "Subscript out of Range" and A is empty

    The error message "Subscript out of Rang" always means that the sheet is not exist. A&i is ont element in an array or the sheetname?If it is the sheet name,you shall use Sheets("A"&i) instead of Sheet(A&i) to get the sheet.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, February 25, 2015 2:05 AM
    Moderator
  • Hi,

    You have to use an array instead of single variables to put your sheet names.

    Here a simple example for 3 sheets.

    Option Base 1    ' sets first index of array to 1 
    
    Sub CopyMonthlyDataInForecastedGrid()
        Const NumberOfSheets = 3    ' set to number of sheets to change
        Dim A(NumberOfSheets) As String
        Dim j As Integer
        
        A(1) = "30-48.75"
        A(2) = "30-48.5"
        A(3) = "30.25-48.5"
    
        For j = 1 To NumberOfSheets
            Sheets(A(j)).Activate
            ActiveSheet.Range("A1").Value = Time  ' writes time to cell A1
        Next
        
    End Sub
    

    Notes:

    • You have to place the statement Option Base 1 on top of the module (if you don't use "Option Base 1", the array starts with index 0 (Zero)).
    • You have to addapt the constant NumberOfSheets to your needs.
    • The declarition of the constant NumberOfSheets has to be before the declaration of the array.

    Best regards

    Markus


    Markus Schmid

    • Proposed as answer by L.HlModerator Friday, February 27, 2015 8:26 AM
    • Marked as answer by L.HlModerator Monday, March 2, 2015 1:31 AM
    Wednesday, February 25, 2015 8:35 AM