Need to copy cell range from multiple workbooks into multiple sheets of single workbook

I am new to the VB/Macro world, so forgive me if I'm barking up the wrong tree here.

We currently use a web-based survey tool to get feedback on 80+ different training courses. Once a month, we generate a sheet with percentages of favorable responses.

The workbook in which this is run contains a summary report sheet, a sheet with one table with a Named data range for each course, and then an individual worksheet for the raw data from each course.

Each table/Named range generates percentages via a formula that averages values from the corresponding course sheet, like this:

=A10_LBS!$J$1/SUM(A10_LBS!$J$1:$J$5)

The percentages of favorable responses (2 responses out of 5 possible responses) are added together with a simple SUM statement in another cell. That SUM cell is then linked to the appropriate cell on the main page. Admittedly, there's probably some redundancy in there, but with 80+ sheets, tables, and who know how many linked cells, I want to try to avoid changing that, as it works in its current state.

The problem comes in the process of populating the individual course sheets every month. The web site from which we get our data offers it in two forms: (1) tables generated and displayed on the site itself, and (2) downloadable Excel files in two formats, .CSV and .XLS.

Prior to my inheritance of this process, the individual course sheets were populated monthly by the time-tested method of displaying the web-generated table on one monitor, and typing the values into the sheet on another. Obviously crude, slow, and hundreds of opportunities for error. Worst part is, the survey page offers downloadable (or for that matter, displayable) data only one survey at a time, so we're either manually copying 80+ tables of data, or downloading 80+ individual spreadsheets. No way around this, I have exhausted every avenue.

So, I figure if we're going to touch 80+ individual surveys, we might as well cut-and-paste rows of data rather than retype every individual cell. The 80+ worksheets are generated, and, rather than save to disk, we open them, highlight the rows we need (all the entries for a given month), and paste them into the appropriate course sheet. Once all courses are copied, we're done.

The spreadsheet generated by the web site contains all survey data up to the time at which it is run. It is possible for it to generate a report with a specific date range, but the parameters would have to be entered every month (nope, no useful interface to speak of), so it's faster to download them all and highlight the range needed.

All my long-windedness aside, I would like to download each web-generated workbook, giving each one the name of its corresponding course sheet in the master. When all are copied, run a macro (or VB application) to prompt for a month or date range, then open each file, copy the rows of data within the date range, and paste them into the corresponding course sheet.

So, my first question is: is this possible?

If so, can this be done via macros, or am I looking at creating a separate VB app?

Other than formulas and some very rudimentary macros, the last programming I did was a BASIC course on a TRS-80. I've tried searching the forums to see if something similar has been done, but I believe my lack of understanding of the terminology is not producing fruitful searches.

If anyone can point me in the right direction, it would be most appreciated. Thanks!

Well, I managed to get almost all the way through this with a lot of reading and even more trial and error. I've managed to put together code that will open the files, copy the rows, then paste the rows.

I just need to figure out how to filter out the rows I need, based on a start date and end date that I manually enter prior to running the macro. The only problem is, while it seems I should be able to run a simple For-Next loop to delete any rows that fall out of my date range, I can't get my head around how to express dates in an Autofilter statement (or any statement, for that matter). Is it done as mm/dd/yyyy, three separate variables, or am I just making it too hard? Seems like it shouldn't be that difficult, but I'm stuck.

Any help is greatly appreciated. Thanks! Code below

Please Login or Register  to view this content.