Compile Error: Sub or Function not defined after adding form button

Question

Below is the code that I have written to copy data from multiple sheets into a new master sheet and it was working fine until I made a little changes to my code to add a form button to import data now when I click the button I get the error

Compile Error: Sub or Function not defined

can anyone help me ? here is the code for the btnImport button in the ImportForm:

Private Sub btnImport_Click()

  Dim bookList As Workbook
  Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object

  Dim folderpath As String

  folderpath = Range("I5").Value

  If Range("I5").Value = "" Then
    MsgBox "Select the folder which contains the reports.", vbInformation, "Cannot Import."
    Range("I2").Select
  ElseIf FileFolderExists(folderpath) = False Then
  MsgBox "Selected Folder Does Not Exist.", vbInformation, "Cannot Import."
    Range("I5").Select
  ElseIf Dir(folderpath, vbDirectory) = "" Then
    MsgBox "Selected Folder Not Found.", vbInformation, "Invalid Folder Name."
    Range("I5").Select

  Else
    Me.lblWait.Visible = True
    Me.btnCancel.Visible = False
    Me.btnImport.Visible = False

    Application.ScreenUpdating = False
    Application.StatusBar = "Collecting Data, Please Wait..."
    Set mergeObj = CreateObject("Scripting.FileSystemObject")

    'change folder path of excel files here
    Set dirObj = mergeObj.Getfolder("folderpath")
    Set filesObj = dirObj.Files
    For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)

    On Error Resume Next

    'Change B3:H to the range your working on and also B in B65536 to any column required.
     bookList.Worksheets(1).Range("B3:H350" & bookList.Worksheets(1).Range("B65536").End(xlUp).Row).Copy
     ThisWorkbook.Worksheets(1).Activate

  'Below only change "B" column name to your required column name
  Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  bookList.Close
  Next

  End If
End Sub

@user3598756 while the VBA goes in debugging it highlights this line: Private Sub btnImport_Click() — Hashmatullah Noorzai
– Hashmatullah Noorzai, Commented Jul 24, 2016 at 11:59
@Hazmat:"while the VBA goes in debugging it highlights this line: Private Sub btnImport_Click()": This line is highlighted yellow. What is highlighted blue? — Axel Richter
– Axel Richter, Commented Jul 24, 2016 at 12:05

KyloRen · Accepted Answer · 2016-07-24 12:54:40Z

2

In this line please remove the quotation marks,

Set dirObj = mergeObj.Getfolder("folderpath")

This will cause the error due to it being a variable.

It should be like this,

Set dirObj = mergeObj.Getfolder(folderpath)

answered Jul 24, 2016 at 12:54

KyloRen

2,7715 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compile Error: Sub or Function not defined after adding form button

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related