Importing named ranges from a reference workbook (Excel 97)

Topic

New Reply

Hopefully someone here can offer me some help. Here’s what I need to do…

I want to maintain a single Excel file with many different chunks of data in named ranges that will be used to compile a dynamically created Excel file on the fly depending on what data is required in that new file. The named ranges in the ‘reference’ file will have a variety of row lengths, but generally always the same number of columns. The new “dynamic” file will either start by prompting the user for a product number, for instance, and then look up that product number in the reference file, determine all the related data that is needed (i.e. VLOOKUP) and then import the particular ranges of data into the new file, appending the ranges one after the other. The alternative is to open a dialog box to prompt the user for all the options that this product number requires (i.e. radio buttons or check boxes), and when they select “OK”, the requires ranges of data are imported. Since the reference ranges will be of various row lengths I need the import process to automatically append the ranges one after the other, regardless of what row the previously imported range ends on. BTW, some of the imported ranges will have empty cells.

MUCH THANKS to any and all who can provide me some direction here. I have difficulty learning new procedures in VBA.

Drew

Reply | Quote

Replies

linux_man

I don’t know if MS-Excel runs on LINUX, but what the heck.

I think you need to look at the following line of code

Dim lFirstAviailabeRow As Long
lFirstAviailabeRow = ActiveSheet.Range(“A1”).End(xlDown).Offset(1, 0).Row

Now what this does is that it goes from cell A1 all the way down until it finds an blanck cell. And stops. Then it advances one row, to the empty one, and return that row number.

So you can then paste at that row number and that solves the problem.

Now it is obvious that this depends on blanck cells, and it is obvious that if Column A has the most data, then you will be fine, but you need to know which column to use to keep this going without overwriting data.

Also what if column A is ALL blanck, well that sends you all the way to the last Row 65536. So then you will do the opposite, you go up:

lFirstAviailabeRow = ActiveSheet.Range(“A1”).End(xlUP).Row

OK I hope I answered your question.

Wassim

Reply | Quote

Wassim: I don’t believe that your code will properly handle the situation that was mentioned in the original question that not all cells in the used rows contain data.

Reply | Quote

That is correct. There will be cases when the previously imported data may have blank cells in the left most column.
Basically, I want to create a worksheet template and a form that will provide checkboxes for options. The user will select the various options for data that is required in a new worksheet. When the user selects OK, the code will then import the appropriate ranges of data from an external workbook and place them neatly in order within the new template. Again, some of the imported data may have empty cells here and there.

I have attached a zip file of worksheets as examples. If anyone can provide me some insight as to how to get this started, I would be very grateful.

The three files in the ZIP are a reference file, containing all the standard data ranges that may be available, the template file that will be created dynamically based on the user selecting options in a form, and the end result file which shows an example of how it should come together in a finished product.

Drew

Reply | Quote

Not sure quite how to do it all, but rather than try to find the last Row used I would make a lookup table listing the rows used in a block and calculate the row number requierd from that

HTH

Peter

Reply | Quote

Actually, I have tried something that would work well with your idea. One can use the simple ROWS function to determine the number of rows in a named range. Knowing this, for each imported range, one could simply add up the number of rows in each and use that value for further range imports. Thanks for the tip.

Reply | Quote

The code below will find the row number of the first row after the last row of data on the sheet:

Dim lNextRow As Long
    lNextRow = Worksheets("Sheet1").Range("A1").Offset(Worksheets("Sheet1").UsedRange.Rows.Count, 0).End(xlUp).Row + 1

Reply | Quote