Hi Everyone

I have created a macro that opens up to 15 files and then adds a new column at the end of each one. It is then supposed to copy this into a master workbook. Unfortunately, I have only been able to write the code so that it copies the data over for one of these workbooks. This is in Excel 2003. Below is the code I am working with:

'Loading .xls files in to the form

Code:
Private Sub Artist_1_Click()
Artist_1LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_2_Click()
Artist_2LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_3_Click()
Artist_3LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub

Private Sub Artist_4_Click()
Artist_4LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_5_Click()
Artist_5LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_6_Click()
Artist_6LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_7_Click()
Artist_7LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_8_Click()
Artist_8LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_9_Click()
Artist_9LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_10_Click()
Artist_10LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_11_Click()
Artist_11LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_12_Click()
Artist_12LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_13_Click()
Artist_13LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_14_Click()
Artist_14LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Artist_15_Click()
Artist_15LB = Application _
    .GetOpenFilename("Artist Files (*.xls), *.xls")
End Sub
Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
End Sub
Private Sub Image2_Click()
End Sub
Private Sub Start_Painting_Click()
Dim wb As Workbook

Dim masterSheet As Worksheet
Dim LastRow As Long
Dim lastMasterRow As Long
Dim AlreadyPasted As Boolean

LastRow = 1

Set masterSheet = Application.ActiveSheet


'Here we loop through every textbox and figure out what is filled:
Dim ctl As Control
For Each ctl In Me.Controls
    If Right(ctl.Name, 2) = "LB" Then           '<-- example of checking name of control
        'This opens the workbook if the value is not space:
        If ctl.Value <> "" Then 'filled
            Workbooks.Open (ctl.Value)
        End If
    End If
Next

 

' For each open Workbook, select the data, copy it to the final workbook
' at the end of the data which is already there.

For Each wb In Workbooks

    'If this sheet is the master sheet, we exit, as we dont want to copy
    'the main sheet into itsself
    If wb.Name = "Macro.xls" Then
       'Do nothing
    Else
        
        Set CurrentWorkbook = wb
        '  First we extract the Artist Name from the filename and stick it in cell Z2
        Range("Z2").Select
        ActiveCell.FormulaR1C1 = "=MID(CELL(""filename""),SEARCH(""["",CELL(""filename""))+1, SEARCH(""]"",CELL(""filename""))-SEARCH(""["",CELL(""filename""))-1)"
    
        'Find last row:
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
     
        Selection.AutoFill Destination:=Range("Z2:Z" & LastRow)
        ' Force a re-calculation (just in case)
        ActiveSheet.Calculate
        
        'Paste the formula as values:
        Columns("Z:Z").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
     
        '   Copy the entire sheet
        Range("A2:Z" & LastRow).Select
        Selection.Copy
        
    '   Find next blank cell in master book: - replace:
        lastMasterRow = masterSheet.UsedRange.Rows.Count
        
    '   Place it in the master workbook in next cell:
        masterSheet.Range("A2:Z" & lastMasterRow).PasteSpecial xlPasteAll
    
    '   Offsets down one row for next empty cell
        Cells(NextRow + 1, 6).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    
    '   Close workbook and do not save changes:
        wb.Close vbNo
 
End If

Next

End Sub
If anyone can provide some assistance with this so that the macro will copy over multiple workbooks to the master which is currently titled macro.xls that would be greatly appreciated.

Thanks

Eric