Making a Command Button in A Userform Copy & Paste Column from One Workbook Into Anot

[geotex2]

Hi All:

Here are the requirements:

1) Target workbook is "lsat_v26.xlsm" and is the main program.

2) Source workbook is "fake_master_db5.xls".

3) Target worksheet is "LookupLists".

4) Source worksheet is "PROJECT LIST".

5) Another target workbook is "test8_trial_2 (works).xlsm" and contains the isolated copy/paste macro which is tested and is working.

6) A Userform has a command button which invokes a browse-for-file window for the user to select source workbook directory. Once selected, macro takes over and copies column A1:A from source worksheet of source workbook. Copied column is pasted into target worksheet of target workbook. Then, another macro sorts that column in a descending order. Then, a combobox pulls all cells in this sorted column and turns them into drop-down options a user can select in that combobox.

By itself, the browse/copy/paste macro works perfectly. But when attempting to merge it with the Userform code I get a bug. See copy/paste macro below. See my other post from yesterday. This is the same macro as the one from that post.

About the bug....the source excel file opens up and stays open in forefront of the target excel file. Userform also stays open and allows me to enter data into its many textbox, combobox, etc. Other command buttons work as long as they influence only the userform. One command button that adds data from userform into the target workbook produces an error on clicking....implying that the target worksheet/workbook gets locked by the copy/paste macro (see code below).

Questions/hypothesis: Should I be injecting this macro into a particular section of the userform code? Is the incorrect sequence of code breaking my program? Is the copy/paste macro locking the target workbook/worksheet and thus preventing the userform from operating on it?

'MACRO TO READ-IN EXTERNAL EXCEL FILE FROM WHICH JOB NO.'S ARE EXTRACTED INTO USERFORM        
        Sub ReadDataFromCloseFile()
 
            'IN CASE OF ERROR SEND TO ERROR FUNCTION
                On Error GoTo ErrHandler
 
            'PREVENT OPENED EXCEL SOURCE FILE FROM SHOWING TO USER
                Application.ScreenUpdating = False
 
            'OPEN SOURCE EXCEL WORKBOOK IN "READ ONLY MODE"
                Dim SrcName As String
                Dim src As Workbook
                SrcName = Application.GetOpenFilename()
                Set src = Workbooks.Open(SrcName, True, True)
 
            'GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK
                Dim iTotalRows As Integer
                iTotalRows = src.Worksheets("PROJECT LIST").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count
 
            'COPY DATA FROM SOURCE WORKBOOK  -> DESTINATION WORKBOOK
                Dim iCnt As Integer     '(COUNTER)
                For iCnt = 1 To iTotalRows
                    Worksheets("Test_File_8").Range("B" & (iCnt + 1)).Formula = src.Worksheets("PROJECT LIST").Range("A" & (iCnt + 1)).Formula
                Next iCnt
 
            'CLOSE THE SOURCE WORKBOOK FILE
                src.Close False             'FALSE = DONT SAVE THE SOURCE FILE
                Set src = Nothing           'FLUSH DATA
 
            'ERROR FUNCTION
ErrHandler:
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End Sub

After modding above code for work in context of a userform, here is the Command Button code.

Private Sub CommandButton1_Click()
'MACRO TO READ-IN EXTERNAL EXCEL FILE FROM WHICH JOB NO.'S ARE EXTRACTED INTO USERFORM
 
            'IN CASE OF ERROR SEND TO ERROR FUNCTION
                On Error GoTo ErrHandler
 
            'PREVENT OPENED EXCEL SOURCE FILE FROM SHOWING TO USER
                Application.ScreenUpdating = False
 
            'OPEN SOURCE EXCEL WORKBOOK IN "READ ONLY MODE"
                Dim SrcName As String
                Dim src As Workbook
                SrcName = Application.GetOpenFilename()
                Set src = Workbooks.Open(SrcName, True, True)
 
            'GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK
                Dim iTotalRows As Integer
                iTotalRows = src.Worksheets("PROJECT LIST").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Rows.Count
 
            'COPY DATA FROM SOURCE WORKBOOK  -> DESTINATION WORKBOOK
                Dim iCnt As Integer     '(COUNTER)
                For iCnt = 1 To iTotalRows
                    Worksheets("LookupLists").Range("B" & (iCnt + 1)).Formula = src.Worksheets("PROJECT LIST").Range("A" & (iCnt + 1)).Formula
                Next iCnt
 
            'CLOSE THE SOURCE WORKBOOK FILE
                src.Close False             'FALSE = DONT SAVE THE SOURCE FILE
                Set src = Nothing           'FLUSH DATA
 
            'ERROR FUNCTION
ErrHandler:
            Application.EnableEvents = True
            Application.ScreenUpdating = True
 
End Sub

Any help would be greatly appreciated! See attached source file.