Hi Brian B
Still gave the error - though I am now using the folowing code with choice being made by which button I press as to which sheet is sucked out of all the workbooks.
Module code
Dim ToBook As String
Dim ToSheet As Worksheet
Dim NumColumns As Integer
Dim ToRow As Long
Dim FromBook As String
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim LastRow As Long
'-
'=========================================================
'- MAIN ROUTINE
'=========================================================
Sub FILES_FROM_FOLDER2(WEEKNO)
Application.Calculation = xlCalculationManual
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
ToBook = ActiveWorkbook.Name
'---------------------------
'- MASTER SHEET
'---------------------------
Set ToSheet = ActiveSheet
NumColumns = ToSheet.Range("A1").End(xlToRight).Column
ToRow = ToSheet.Range("A500").End(xlUp).Row
'- clear master
If ToRow <> 1 Then
ToSheet.Range(ToSheet.Cells(2, 1), _
ToSheet.Cells(ToRow, NumColumns)).ClearContents
End If
ToRow = 2
'------------------------------------------
'- main loop to open each file in folder
'------------------------------------------
FromBook = Dir("*.xls")
While FromBook <> ""
If FromBook <> ToBook Then
Application.StatusBar = FromBook
Transfer_data2 (WEEKNO) ' subroutine below
End If
FromBook = Dir
Wend
'-- close
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
End Sub
'
Private Sub Transfer_data2(WEEKNO2)
Workbooks.Open Filename:=FromBook
Sheets(WEEKNO2).Select
Sheets(WEEKNO2).Copy After:=Workbooks("Summary bezel.xls").Sheets(1 _
)
Workbooks(FromBook).Close savechanges:=False
End Sub
and button code
Private Sub CommandButton1_Click()
FILES_FROM_FOLDER2 ("W1")
FILES_FROM_FOLDER2 ("W2")
FILES_FROM_FOLDER2 ("W3")
FILES_FROM_FOLDER2 ("W4")
FILES_FROM_FOLDER2 ("Monthly Summary")
MsgBox ("Done.")
End Sub
Private Sub CommandButton2_Click()
FILES_FROM_FOLDER2 ("W1")
MsgBox ("Done.")
End Sub
Private Sub CommandButton3_Click()
FILES_FROM_FOLDER2 ("W2")
MsgBox ("Done.")
End Sub
Private Sub CommandButton4_Click()
FILES_FROM_FOLDER2 ("W3")
MsgBox ("Done.")
End Sub
Private Sub CommandButton5_Click()
FILES_FROM_FOLDER2 ("Monthly Summary")
MsgBox ("Done.")
End Sub
Private Sub CommandButton6_Click()
FILES_FROM_FOLDER2 ("W4")
MsgBox ("Done.")
End Sub
And it seems to work just fine - though I think some of the module code is redundent
Cheers for the help BrianB
Tim