Hi All,

Iam trying to make an button when clicked all the different modules and macros should run.I have tried this but giving an error because of Public Class are mentioned.

Could someone please help.



Public colNames, colStart

'Play from the Activesheet to insert parsed text file's contents
Sub SecondMyFile1()
  Dim inFile As String
  Dim iStr As String
  Dim fni As Integer
  
On Error GoTo Cleanup
 ' SpeedOn
'******************************** Set the path and name of the text file to parse ***********
  inFile = ThisWorkbook.Path & Application.PathSeparator & _
    "changereq.txt"
  
  If Dir(inFile) = "" Then
    MsgBox inFile & " does not exist.", vbCritical, "Macro Ending"
    GoTo Cleanup
  End If
  
  colNames = Array("Country", "CLS", "Security", "Description", "Sedol", _
    "Cusip", "In-House", "CUR", "Account(s)", "SP First", "SP Last")
  'Add one more element to this array than colNames.  Makes iteration easier.
  colStart = Array(1, 5, 9, 22, 63, 71, 84, 97, 101, 114, 123, 132)
  
  'Write field/column names to row 1 if A1 is empty
  If IsEmpty(Range("A1")) Then
    Range("A1").Resize(1, UBound(colNames) + 1).Value = colNames
  End If
  
  'Open inputfile.  Parse input file's parts.
  fni = FreeFile
  Open inFile For Input As #fni
  Line Input #fni, iStr
  Do While Not EOF(fni)
    'Parse lines and input to Range
    ParseLine iStr
    Line Input #fni, iStr
  Loop
  'Set formats and reset text strings to values
  'SetFormats

  'Autofit columns
  Columns("1:" & (UBound(colNames) + 1)).EntireColumn.AutoFit
  
Cleanup:
  On Error Resume Next
  'SpeedOff
  If Err.Number <> 0 Then MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, , "Error"
  Close #fni

ActiveSheet.Select
Range("a:k").Select
Selection.Copy
ChDir "M:\COLLMAN\ISC\SBL"
    Workbooks.Open Filename:="M:\COLLMAN\ISC\SBL\concate the sec not rec file macro-Updated", UpdateLinks:=0
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Sub checkedforconc()


    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
       
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D600")
    Range("D2:D600").Select
    Columns("D:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E600")
    Range("E2:E600").Select
    Columns("E:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(RC[-2])=7,"" EQUITY SEDOL1"",IF(LEN(RC[-2])=12,"" EQUITY ISIN"","" EQUITY CUSIP""))"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F600")
    Range("F2:F600").Select
    ActiveWindow.SmallScroll Down:=-6
    Columns("F:F").Select
    ActiveSheet.Calculate
    ActiveWindow.SmallScroll Down:=39
    Range("F59").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(LEN(RC[-2])=7,"" EQUITY SEDOL1"",IF(LEN(RC[-2])=12,"" EQUITY ISIN"","" EQUITY CUSIP""))"
    Range("F59").Select
    ActiveWindow.SmallScroll Down:=-75
    Columns("F:F").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-1])"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G600")
    Range("G2:G600").Select
    Columns("G:G").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("D1").Value = "Securitys Concated"
    Range("D2").Select
    
   For i = 2 To Range("I2").End(xlDown).Row
   
   Range("M1").Value = "BB Price"
   If Range("I" & i) = "EUR" Then
       Range("M" & i).Select
         ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
   
       ElseIf Range("I" & i) = "USD" Then
         Range("M" & i).Select
           ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
     
          ElseIf Range("I" & i) = "GBP" Then
            Range("M" & i).Select
             ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
       
               ElseIf Range("I" & i) = "CAD" Then
                 Range("M" & i).Select
                  ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
          
                   ElseIf Range("I" & i) = "CHF" Then
                     Range("M" & i).Select
                       ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
          
                        ElseIf Range("I" & i) = "ZAR" Then
                          Range("M" & i).Select
                             ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
          
                            ElseIf Range("I" & i) = "RUB" Then
                               Range("M" & i).Select
                                  ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
                   
                                   ElseIf Range("I" & i) = "ITL" Then
                                       Range("M" & i).Select
                                           ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
          
                                          ElseIf Range("I" & i) = "ILS" Then
                                             Range("M" & i).Select
                                                 ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
          
                                                 ElseIf Range("I" & i) = "NOK" Then
                                                    Range("M" & i).Select
                                                         ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
          
                                                     ElseIf Range("I" & i) = "BMD" Then
                                                       Range("M" & i).Select
                                                         ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
                                                                                       
                                                            ElseIf Range("I" & i) = "ESP" Then
                                                            Range("M" & i).Select
                                                            ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""yest_last_trade"")"
   
                                                                                                                              
                                                              ElseIf Range("I" & i) = "KRW" Then
                                                              Range("M" & i).Select
                                                              ActiveCell.FormulaR1C1 = "=bdp(RC[-9],""px last"")"
     
         
  End If
  Next i
     
    Range("N1").Value = ("Bloomberg Currency")
    
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=blp(RC[-10],""crncy"")"
   Selection.AutoFill Destination:=Range("N2:N600")
        
    
    Range("O1").Value = ("STATUS")
    
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(COUNTIF(RC[-6]:RC[-1],""#N/A Sec""),""Security Not Found"",IF(RC[-1]=RC[-6],""Matched"",""CCY Mismatch""))"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O600")
    Range("O2:O600").Select
'Workbook.Close
End Sub

Sub CHECK()
Dim x As Long, a As Long, d As Long
d = 2
x = Sheets("Sheet1").Cells(Rows.Count, 13).End(xlUp).Row
Sheets("Sheet2").Range("K:L").ClearContents
For a = 2 To x
If Sheets("Sheet1").Cells(a, 13) <> "#N/A N/A" And Sheets("Sheet1").Cells(a, 13) <> "#N/A Invalid Security" And Sheets("Sheet1").Cells(a, 13) <> "#N/A Field Not Applicable" Then
Sheets("Sheet2").Cells(d, 12) = Sheets("Sheet1").Cells(a, 3)
Sheets("Sheet2").Cells(d, 11) = Sheets("Sheet1").Cells(a, 13)
d = d + 1
End If
Next a



MsgBox "Transfer is complete"
   
   
     'Sheets("Sheet1").Select
     'Sheets("Sheet1").Name = "Price Analysis"
     'Sheets("Sheet2").Select
     'Sheets("Sheet2").Name = "CCY Mismatch"
     'Sheets("Sheet3").Select
    ' Sheets("Sheet3").Name = "Security Not Found"
     
     
End Sub
Regards,
Shekar.