Select statement producing an error "Select method of worksheet failed"

**jeskit** · 05-10-2011, 05:26 AM

Hi

I this code below but the select satement keeps producing and error of "Select method of worksheet failed"

If bLang = vbYes Then 'if the user selected yes to importing langauge data
    Set PPOld = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_MONTH_Input_Reference_Table_V" & bVImport & ".xlsx")
    PPNew.Sheets.Add.Name = "DE": PPNew.Sheets.Add.Name = "PL"
    PPNew.Sheets("EN").Select    Sheets("EN").Select: Range("A:A").Copy: Sheets("DE").Select: Range("a1").PasteSpecial Paste:=xlPasteValues
    Sheets("EN").Select: Range("A:A").Copy: Sheets("PL").Select: Range("a1").PasteSpecial Paste:=xlPasteValues
    
    For Each shName In Array("DE", "PL")
        shFound = False
        For Each sh In Worksheets
            If LCase(sh.Name) = LCase(shName) Then
                shFound = True
            PPOld.Sheets(shName).Range("B2:P2000").Copy Destination:=PPNew.Sheets(shName).Range("AA1")
            End If
        Next sh
    Next shName
    
    PPOld.Close
    
    For Each shName2 In Array("DE", "PL")
        For Each sht In Array(27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43) 'Loop through the vlookup cells and autofill the rest of the columns
            With PPNew.Sheets(shName).cells(2, 2)
                .Formula = "=IF(A2=0,0,VLOOKUP($A2,ConvertManual!$A$2:$Y$500," & sht & ",FALSE))"
                .AutoFill .Resize(500)
            End With
        Next
    Next shName2
End If

Does anyone know why its keeps producing an error?

Thanks

Jeskit

**JBeaucaire** · 05-10-2011, 06:57 AM

Your multi-commands on a single row is difficult to read properly and you have at least one colon missing. But you should really put your commands on a separate lines AND remove all the selecting, not necessary in VBA.

If bLang = vbYes Then 'if the user selected yes to importing langauge data
    Set PPOld = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_MONTH_Input_Reference_Table_V" & bVImport & ".xlsx")
    PPNew.Sheets.Add.Name = "DE"
    PPNew.Sheets.Add.Name = "PL"
    PPOld.Sheets("EN").Copy
    PPNew.Sheets("DE").Range("A1").PasteSpecial Paste:=xlPasteValues
    PPOld.Sheets("EN").Range("A:A").Copy
    PPNew.Sheets("PL").Range("A1").PasteSpecial Paste:=xlPasteValues
    
    For Each shName In Array("DE", "PL")
        shFound = False
        For Each sh In Worksheets
            If LCase(sh.Name) = LCase(shName) Then
                shFound = True
            PPOld.Sheets(shName).Range("B2:P2000").Copy Destination:=PPNew.Sheets(shName).Range("AA1")
            End If
        Next sh
    Next shName
    
    PPOld.Close
    
    For Each shName2 In Array("DE", "PL")
        For Each sht In Array(27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43) 'Loop through the vlookup cells and autofill the rest of the columns
            With PPNew.Sheets(shName).Cells(2, 2)
                .Formula = "=IF(A2=0,0,VLOOKUP($A2,ConvertManual!$A$2:$Y$500," & sht & ",FALSE))"
                .AutoFill .Resize(500)
            End With
        Next
    Next shName2
End If

**jeskit** · 05-10-2011, 07:31 AM

Hi

Thanks for replying!

Ok i have changed it to the macro below and its fixed the first erro but i am now gettign a "Subscript out of range" error when it gets to the line i highlighted red

If bLang = vbYes Then 'if the user selected yes to importing langauge data
    Application.StatusBar = "Importing previous language data"
    Set PPOld = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_MONTH_Input_Reference_Table_V" & bVImport & ".xlsx")
        
    For Each shName In Array("DE", "PL")
        shFound = False
        For Each sh In Worksheets
            If LCase(sh.Name) = LCase(shName) Then
                shFound = True
            PPOld.Sheets(shName).Range("B2:P2000").Copy Destination:=PPNew.Sheets(shName).Range("AA1")
            End If
        Next sh
    Next shName
    
    PPOld.Close
    NCol = 2
    
    For Each shName2 In Array("DE", "PL")
        For Each sht In Array(27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43) 'Loop through the vlookup cells and autofill the rest of the columns
            With PPNew.Sheets(shName).cells(2, NCol)                .Formula = "=IF(A2=0,0,VLOOKUP($A2,ConvertManual!$A$2:$Y$500," & sht & ",FALSE))"
                .AutoFill .Resize(500)
            End With
        Next
        PPNew.Sheets(shName2).cells.Copy
        PPNew.Sheets(shName2).Range("A1").PasteSpecial Paste:=xlPasteValues
    Next shName2
          
End If

**JBeaucaire** · 05-10-2011, 07:43 AM

Shouldn't that be:

With PPNew.Sheets(shName2)

**jeskit** · 05-10-2011, 07:51 AM

Hi

Thanks its working great now but this line:

     For Each shName2 In Array("DE", "PL")
        For Each sht In Array(27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43) 
             With PPNew.Sheets(shName).cells(2, NCol)                
                .Formula = "=IF(A2=0,0,VLOOKUP($A2,ConvertManual!$A$2:$Y$500," & sht & ",FALSE))"
                .AutoFill .Resize(500)
                NCol = NCol + 1
              End With
        Next
        PPNew.Sheets(shName2).cells.Copy
        PPNew.Sheets(shName2).Range("A1").PasteSpecial Paste:=xlPasteValues
    Next shName2

This bit should be looping through the two sheets and for each sheet it should be looping through the columns in the array and for each one putting a vlookup formla into Row 2 and starting at column B and then for each column in the array it should go to a new column

And it is doing this but its starting at column S instead of B

**JBeaucaire** · 05-10-2011, 08:19 AM

             With PPNew.Sheets(shName).cells(2, NCol)                
                .Formula = "=IF(A2=0,0,VLOOKUP($A2,ConvertManual!$A$2:$Y$500," & sht & ",FALSE))"
                .AutoFill .Resize(500)
                NCol = NCol + 1
              End With

You are using a variable called NCol to designate the column. Apparently the value of NCol at the start of the is not "2", which is what it would need to be to start at column B.