Message ti alert the user to link additional cells

[bearcub]

I have a spreadsheet (sheet2) that is linked to sheet (sheet1) that is imported from our database. I would like a message alert to pop up to notify me that I have less data on the linked sheet (sheet2) than the sheet that was just imported. Sheet2 is linked to an Access database to run a report. I just discovered that if I have formulas in rows without any data each will show up as a blank row in our published directory - which is a no no.

What I was thinking of is creating a message box alert to inform the user (most likely an admin who isn't savvy in Excel) that their are more rows of data in the imported sheet than on the linked sheet. And, to let them know they have to copy the formulas down to capture all the missing data.

For example, if I imported 400 rows of data and the linked sheet only has formulas that go down to row 350 I would have to copy the formulas down another 50 rows. I would like alert the admin that 400 rows were imported but she only has 350 linked rows and she needs to add an additional 50 rows of formulas to capture all the data that should be going into Access. Does that sense or should I explain further?

Is this possible and would it be attached to so sort of worksheet event?

Thank you for your help,

Michael

 

[bearcub]

Thank you,

which file share site would you recommend? I haven't done that before. But, the code looks correct, doesn't it?

Michael

 

[AlphaFrog]

DropBox is a popular one.

Code looks fine. I'm guessing the problem is with the file.

 

[bearcub]

Okay, I'll send part of those two sheets tomorrow, it's at work. Probaby somewhere between 10-11 PST. How can I notify you when it has been posted?

 

[bearcub]

I've just downloaded the file with accompanying code at Jumpshare. I don't know if you can access it but here is the link that I'm looking at on my screen:

https://jumpshare.com/

Thank you for you help with this. We really appreciate it.

Michael

 

[AlphaFrog]

You'll have to try a better link to the file. That link is to the jumpshare site only. Not to the file on that site. You sure are not making this easy.

 

[bearcub]

Sorry, don't mean to be a pain.

http://jmp.sh/pSqFZPg


First time I've used this so I apologize for the gaff. I think this is the link as well.

https://jumpshare.com/v/cJZWMCi18IEnqTJcPjXP

let me know if you can access it.

michael

 

[AlphaFrog]

The code is fine. Well below your linked cells, there are several random cells filled with old formulas and other junk. When the code looks for the last used row, it sees those rows and incorrectly determines the last used row.

To fix it:

• Select Row 237
• While holding the Ctrl+Shift keys press the Down Arrow until you are past all your colored rows.
• This will select all the rows below the data.
• Press the Delete key to clear their contents.
• The macro should work now

Autofill will mess up your alternating colored rows. If you want to have alternating colored rows, use this Conditional Formatting method.
How to shade every other row in Excel

 

[bearcub]

Slap my forehead! I saw something when I was copying over to the new file.

I think I found another issue which is something that i have to be aware of on my part. I think I had the All SCC table formatted as a table. When I ran the code using the table format nothing happened. When I converted the table to a range and deleted all the excess rows it worked fine. I guess the code considers the excees table cells as being dirtied and goes to the end of table range. Of course, if I don't have any formulas in the last table row ...

Thank you for your patience.

Michael