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

Hi everyone. I am updating a macro that looks like this:

Please Login or Register  to view this content.

.....and so one for quite a while. My revision will triple the amount of codes. I am looking to write any kind of loop that will reduce the amount of code.
The can be a loop for each window, or anything will help

Hi sdb

Housekeeping thing first - please could you edit your post and surround your code with code tags (start with [ c o d e ] and end with [ / c o d e ] no spaces.)? Makes it easier to read, and is a forum rule. Thanks.

You don't need to swap between workbooks. You can just refer to the workbook, worksheet and range from where you are. Unfortunately, the macro recorder doesn't really help in learning this!

Also, set a variable as any workbook or worksheet you are going to use frequently - saves on typing!

Anyway, here's a loop for you:

Please Login or Register  to view this content.

Put this in a module in your workbook. For the Checkbox click code, just send the sheet to the above code:

Please Login or Register  to view this content.

I'm assuming it will be the same sheet in every workbook, e.g. "1" or "10", etc.

Cheers, Rob.

Thank you for the house keeping tips! I was not aware of the protocol.

That code is awesome by the way it even got rid of that incessant screen seizure!

I have one more question to ask. So you are familiar with the output format, one long list. I was wondering if you knew how to automatically populate columns that will take the sheet say "daily shift.xls" and put a column for shift and have a 1 for that and say 2 for "second shift.xls" etc. Also, the day of the month from the sheet number. so check box one would autofill the columns for that day as 1, and 2 for all the sheets "2" etc.?

Also, what if I wanted the output data to be transposed, what would the code need to be?

Please Login or Register  to view this content.

does not work

Hi sdb

The Transpose option is only available as an option in PasteSpecial. You will need the following code:

Please Login or Register  to view this content.

If you know what range you want to put it in, the code to fill a column of cells looks something like:

Please Login or Register  to view this content.

The question is how to calculate the range. If you know where it relative to the 'destn' cell, you could use offset and resize. The following starts the range at a cell offset(0,-1), which is one cell to the left of the destn cell. Next, it resizes the range to 40 long - which is how many rows you are copying. It puts in the Val of the sheet number. If you didn't use the Val command, you would end up with a string in the cell, i.e. not a number. It would have value 0. The Val command turns that in to a number, i.e. 1 This means that, if you sort, the order will be 1,2,3,...10,11.. If you put in the text, the sort order would be 1,10,11,...,2,20,...

Please Login or Register  to view this content.

If you wanted to insert the shift number from the workbook name:

Please Login or Register  to view this content.

This code takes the first 1 characters from the left of the workbook name (wb is the workbooks you are looping through). Note - at present, both pieces of code above are using the same destination range. If you want both pieces of info, you will need to find somewhere different (e.g. Offset(0,-2)).

I hope this is enough of a clue. If not, upload the files using the Go Advanced button below, then hit Manage Attachments. Include a couple of files, and some clues on what information you want to appear where, and where the info comes from. A 'before' and 'after' is a good way to do it, including the source files.

Cheers, Rob.

Hey Rob,

Do you know how to
(a) make the "select all" button work
(b) I want the form to automatically populate the columns for year and month, only up to the bottom of the list that the macro created. I have the day populate from the macro, but then I would also like a tab that has mm/dd/yyyy. Is this possible to do on auto?
(c) say I wanted to do exactly the same thing as

Please Login or Register  to view this content.

but instead of the name I just want to be able to type into the macro what I want the cells to say, like "1" or "first"

I set up another loop "mod2" just like the transpose one to get another section of data from the same tabs on to another sheet, but the data needs to be transposed. Will the set destn. code work with the autofill code?

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:

Please Login or Register  to view this content.