Run export macro on different sized ranges in every worksheet with same starting cells

I have a macro that I run repeatedly in a certain type of spreadsheet that does an export on a selection, pulls up a "Save As" message box and saves as a text file. There are 9 sheets in the workbook that contain the same data analysis for a set of samples, the only difference between them being the number of data (e.g. some may have 1500, 1400, 1600, etc.). I only select three columns of data to export (I6:K????) that contain counts (1,2,3,....), x-coordinates and y-coordinates. The counts column (I) uses a formula that only counts if there is an x-coordinate next to it (Col J).

Please Login or Register  to view this content.

Generally, what I do to make quick work out of selecting the variable ranges is to select Sheets 03-11 (the first nine sheets) and select K6:I6 (starting w/ K6). Then, on each individual sheet, I do CTRL+SHIFT+DOWN to select all the relevant data (if I started my selection with I6, then it would select all cells that contained formulas which may or may not have x,y-coordinates adjacent to them). Once the data is selected (I6:K????), I run the export macro and save the data as text. I would like a macro that can automate the selection for each Sheet 03-11, excluding Sheets "all", "data" and "summary", and run the export macro, first prompting me for a file location and a file prefix. When the text file is saved, it uses the file prefix and Worksheet name to build the filename, i.e. "path\prefix_wkshtname". I'd greatly appreciate anybody's help on this.

I've attached an example workbook.
Here are the export codes, which were taken from

HTML Code: 

:

Please Login or Register  to view this content.

For example in the 03 worksheet of your sample workbook, what is the range address to export ?

for sheet 03 in the example, the range to export will be I6:K42. The starting row will be the same for all sheets (row 6) but the last row depends on the number of data in col J & K. So, for sheet 04, the range will be I6:K23, I6:K36 for sheet 05 and so on. For the data I am working with, the number of rows in each export range will never likely be the same.

One example to select the range to export :

Please Login or Register  to view this content.

thanks! that works for selecting the desired range. now I just need the export code run on that selection, and then looped over every sheet.

well, after checking some other references and building a Frankenstein macro, I believe I have my issue solved, so far. The code is below. Hopefully the comments are helpful enough. If anyone has suggestions on cleaning up the code, I'm all ears.
I still might prefer being prompted for a folder to save to and give a filename prefix, though, if there are any suggestions.

Thanks Marc L for helping me over the first barrier, I was really stuck on that part. FYI, I changed the column reference in your lines of code from 9 to 11 (col K). This way, the code starts counting rows from the bottom of the data in col K rather than the pre-filled formulas in col I, thus making it more efficient.

Code:

Please Login or Register  to view this content.

With column K, Do … Loop unnecessary, you just need this line :

Range("I6", Cells(Rows.Count, 11).End(xlUp)).Select …


To loop worksheets, just needs a basic counter from 1 to 9, Select Case block unnecessary :

Please Login or Register  to view this content.

With a range to export argument in the export sub,
no need to select either worksheet nor range, no screen blinking …

And I don't see my reputation growing !

Thanks Marc. I knew the range selection should have been simpler; "Cells(Rows.Count, 11)." is what I couldn't figure out before....a bit of a 'duh' moment.
As for the worksheet looping, I'm choosing to keep the "Select Case" b/c sometimes there are more/fewer sheets to process, but the same three sheets will always be excluded.