Gathering data from multiple worksheets and compiling it into one

[bjvick]

Hello,
As a heads up I have some experience with Excel and virtually none with VBA.

I have 6 worksheets named Mon,Tues,Wed,Thurs,Fri,and Sat. I need to copy a range of data from each worksheet and compile it into a final worksheet named "DTTracker". The amount of data input into the days worksheets varies from day to day (one day there could be 15 rows of data and another day only 2), but I need to it compile one on top of the other in DTTracker. Any help is greatly appreciated.

[JBeaucaire]

This assumes the DTTracker already has the headers in row 1 that you need.

Please Login or Register  to view this content.

[bjvick]

I'm getting a "subscript out of range" error for this line:

For Each wsDay In Sheets(Array("Mon", "Tues", "Wed", "Thurs", "Fri", "Sat"))

Also, I should have mentioned before that the data doesn't start until Cell B10 in each days wkst.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[JBeaucaire]

A subscript out of range error usually means spelling errors. Check the example array I showed you against your actual worksheet and make sure all the sheetnames are spelled accurately. Look for hidden spaces.

My code shows how to copy from A2, you can certainly fiddle with that to change it to B10, no?

[bjvick]

Here's what it looks like

[bjvick]

It was indeed a hidden space. Its working now but pasting over itself every time I believe as Sat. is the only info that shows up in DTTracker

[royUK]

Try this slight amendment

Please Login or Register  to view this content.

[bjvick]

Thanks, the code runs but only shows Sat. in DT tracker worksheet. I think each day is pasting over the previous. Ideas?

[JBeaucaire]

1) Why is column A empty on every sheet? Data sheets should not be "designed" in funky ways. It's data. Start at at the top, fill in data, then create reports from the data on your report sheets.

2) Like so:

Please Login or Register  to view this content.

[bjvick]

Thank you very much it works perfectly now.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.