Index of rows in different spreadsheets

[LeMofus]

Hi all,

I've been trying to get something to work for the past few days, and something is wrong in my logic but I just can't figure it out (and it's driving me nuts - as always when things like that don't work !). Here's what I'm trying to do :

I have my first worksheet, called "Index", which will compile a list of legislative texts to be applied (uninteresting part), based on the other sheets (different sheets for different types of texts). I have a unique reference number for each text. Now, depending on some factors in-house, some texts will sometimes be used, sometimes not. So I have a column of Yes/No which is used to populate my index. Compiling a list in my index (and copying the rows) based on the "Yes" factor is easy. I can check whether the row has already been created quite simply, comparing the unique reference number. Where it gets complicated is that I want to be able to remove rows in my index when a legislative text goes from Yes to No. My code doesn't work...

Please Login or Register  to view this content.

I did some tests and the error seems to be a logic one, i.e. coming from those highlighted parts, and not my Module1 subs that delete/create fine. Some rows are created, some are deleted, but it's just not consistent, really not ideal.

Thanks for the help :D

[darknation144]

To reference another spreadsheet you usually have to use the absolute path e.g "C:\documents\spreadsheet"

[nilem]

Hi LeMofus. Maybe so? Try to click the green button in the file.

[LeMofus]

Oh WOW. I have much to learn... Thank you so much.

I'll take a look at the code and try to understand how you approached the problem, but this seems to work a treat

Hi LeMofus. Maybe so? Try to click the green button in the file.

[LeMofus]

One issue though - my N column has hyperlinks in each spreadsheets; currently my "Index" sheet doesn't copy those hyperlinks, but merely the text that "shows"...

Simple fix ?

Still struggling to figure out how this code works !

[LeMofus]

Hi LeMofus. Maybe so? Try to click the green button in the file.

No simple way? I can't seem to figure it out...

Do I have to write a separate function just for the hyperlinks?

[LeMofus]

Looking at your code, the following line

Please Login or Register  to view this content.

is the one that copies all of the data into the array. But, it copies values... i.e. not my formula. However, if I change "value" to "text", or "Formula", then I get errors down the line, when the macro updates my index sheet, line :

Please Login or Register  to view this content.

What can I use to copy that formula, ideally I'd like to keep the hyperlink formula as is and copy it into the index?

[nilem]

If the appearance of your links, for example, http://www.excelforum.com/excel-prog...eadsheets.html, then try this:

Please Login or Register  to view this content.

.Value = y() - here values are written only (no formulas, formats, etc.)

[LeMofus]

Thanks for the help