Loop to copy from multiple files and sheets to one long list

Hi sdb

I suppose the main difference is that the code doesn't do anything until you hit Import. The benefit is, where there are lots of checkboxes close together, if you hit the wrong one you have the opportunity to undo it. Also, you no longer have a separate piece of code for each checkbox, which will hopefully make any maintenance easier.

I added some error checking (making sure someone had entered a Month before proceeding, and that one of the checkboxes is ticked).

In the Form code I added "Option Explicit" at the top. I should have added it to the Module also. This means that any variables used have to be defined (using Dim statement) before they are used. Any variables not defined will lead to errors.

This is good, because it means that the code essentially spell checks itself.
e.g. Dim strABC as String defines strABC as a string. Now, if when writing your code you accidentally type strBAC = "Rob" and strABC=strABC & " Smith" then you would get the unexpected result strABC = " Smith". With Option Explicit, when you run the code, you would get an error the strBAC is not defined.

To pass the information from the form to the LoopThrough subroutine, I had to change the arguments of the subroutine. So, instead of just the Sheet, you also pass the date and the note.

In your code above, second line to last has two typos - it should read destn.offset(0,18).

Also, I have just had another look at the form. Please check the name of the Checkbox for 31. It should be Checkbox31, not Checkbox32. Simply change it in the Checkbox Properties table.

On the '5' front, are you using the Sheet number or Left(wb.name,1) as the strNote? If it is the Sheet number, you need to change it if you want to use the Workbook name. I made some comment on this in the code (look in Sub cmddone_Click). Substitute the code below:

 'If an override note has been entered, use that, otherwise use the default notes above.
   'Currently using the strSheet to determine what the note is.
   'Could use the value of left(wb.name,1) - i.e. for 2nd Shift, it would put in strNotes(2)
   'Code would be:
   'strNote = strNotes(Val(Left(wb.Name, 1)))
   If frmdates.TextBoxNote.Value = "" Then
'     strNote = strNotes(Val(strSheet)) - No Longer Used
     strNote = strNotes(Val(Left(wb.Name, 1)))
    Else
    strNote = frmdates.TextBoxNote.Value
    End If