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

 

[AlphaFrog]

How do you import the data and what do you mean by "published directory"? I imagine the message could be linked to one of those two events depending on how you import and\or publish.

What columns on Sheet2 has the formulas.

 

[bearcub]

I have a macro that imports the sheet into the file. Yes, I could call this other macro when it is imported. great idea.

Sheet1 is always going to be 77 columns wide but the number of rows are going to vary because of new members being added/deleted. The number of rows could vary from 200-700 rows depending upon the office. This file is to be used as a template for 6 different offices and each office has a different amount of members associated with it. That's why I was thinking of counting the number of rows being added to Sheet 1 (less the header) and comparing it to the number of links that contain a link.

I'm just linking A2:BZ2 on Sheet2 to A2:BZ2 on Sheet1 - a simple link, no formula. So I would need to count the number of rows on Sheet2 that contain a formula to the number of rows that were just imported.

All that the admin would have to do after they got this information is just copy the last row on sheet2 that contains data and paste the number of missing rows. she could then check both sheets to make sure that last row of data on sheet1 has been added to sheet2.

As I'm writing this the thought just occurred to me would it be possible to compare the used range of sheet2 to sheet1 and then copy and paste the missing rows in sheet2 so that used ranges match?

thank you for your help,

Michael

 

[AlphaFrog]

Still not sure why you have links.

This will add a link on Sheet2 for each row of data on Sheet1

[color=darkblue]Sub[/color] Fill_Links()
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    LastRow = Sheets("Sheet1").Cells.Find("*", Sheets("Sheet1").Range("A1"), , , 1, 2).Row
    [color=darkblue]With[/color] Sheets("Sheet2")
        .UsedRange.Offset(1).ClearContents
        .Range("A2:BZ" & LastRow).FormulaR1C1 = "='Sheet1'!RC"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[AlphaFrog]

Use this instead. It only adds links for new rows added to sheet1.

[color=darkblue]Sub[/color] Fill_Links()
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    LastRow = Sheets("Sheet1").UsedRange.Rows.Count
    [color=darkblue]With[/color] Sheets("Sheet2")
        NextRow = .UsedRange.Rows.Count
        .Range("A" & NextRow & ":BZ" & LastRow).FormulaR1C1 = "='Sheet1'!RC"
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[bearcub]

The reason I have links is because only part of the data is used. Sheet2 is linked to an Access database. I know it sounds convoluted but this is the way they set it up initially. I'm just trying to streamline what they've done, not redo the whole thing because it does work okay.

So this will add any additional rows that need to added to sheet2 so it matches the last row in Sheet1 (so I don't have any missing rows of data in sheet2)?

This will copy what I have in same row 450 to row 500 providing I had 500 rows of data on sheet1?

Thank you for your help with this,

Michael

 

[AlphaFrog]

yes

 

[bearcub]

Intestestin code. How does it work without using the copy and paste methods? I just tried it and it worked, fabulous. But, how does it work

Sub Fill_Links()
Dim LastRow As Long, NextRow As Long
LastRow = Sheets("Sheet1").UsedRange.Rows.Count
With Sheets("Sheet2")
NextRow = .UsedRange.Rows.Count
.Range("A" & NextRow & ":BZ" & LastRow).FormulaR1C1 = "='Sheet1'!RC"
End With
End Sub

I do have a slight nuance to the sheet2.

I do have records that are hard coded (the cells are highlighted grayed). These are in addition to the linked cells. I have them before the linked cells because these should alwasy these are entered before I start linking the data from sheet2 to sheet1. The reason I do this that these members are never entered into the company database but show should up in the directory (these are retired personnel. the database only shows active members). Is there something that would look to find the first non gray cell as the starting row.

For example, if I have 10 retired members that the actual linking will start on row 11, not 1 or 2. If I import 500 rows of data that the used range on sheet 2 will be 512 (the 10 new members, the header row plus the current members linked to the sheet1).

I could manually adjust the range but if the non database size shrinks or expands it might create more confusion with the admins which I'm trying to avoid.

I was also just looking at the code that you sent to, I'm totally amazed at what it does but it doesn't quite do what I need it to do. It is my fault for not filling you in on all the details, I apologize for that.

The type of formula I need, in addition to what I've previously communicated, is to copy the formulas that are in the last row on sheet2 and paste it down to add the missing members. I have some array formulas in some of these columns that would be lost if I used the code your provided. Instead of having the admin copying the formulas (and links) in row 450 and then paste an additional 50 rows to add the missing members. Is there a way to do this?

I hope I'm not being a pain! I didn't realize when I was initially writing this that it was going to be more complicated (but this is the sort of things I have to deal with on a daily basis - all the one off's that require workarounds).

Thank you for your help,

Michae.

 

[AlphaFrog]

Previous code added the actual link formulas to the cells; .FormulaR1C1 = "='Sheet1'!RC"

This autofills down the existing formulas starting from the last row. Retired members (non-linked rows) accounted for.

[color=darkblue]Sub[/color] Fill_Links()
[color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color], LinkedRowsCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
LastRow = Sheets("Sheet1").UsedRange.Rows.Count
[color=darkblue]With[/color] Sheets("Sheet2")
    NextRow = .UsedRange.Rows.Count
    LinkedRowsCount = .Columns("A").SpecialCells(xlCellTypeFormulas).Count
    [color=darkblue]With[/color] .Rows(NextRow).Range("A1:BZ1")
        .AutoFill Destination:=.Resize(LastRow - LinkedRowsCount)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] With
End [color=darkblue]Sub[/color]

 

[bearcub]

Totally Amazing, wow! You never even used copy or paste though you used the AutoFill down method. Does the count property perform the same function as copy and paste? Still trying to figure out how this works. I'm blown away!

Is there a way to remove the formulas in the last row if there if the value in column A is blank? I just ran the code and it works like a charm! However, the last row on sheet 2 doesn't have anything in it because it exceeded the used range. The row I'm referring to would be row 501 on sheet 2 though there are only 500 rows on sheet 1. the autofill extended one row beyond the sheet1 used range.

I would add this line of code myself but I'm not quite sure where and how to add this line to your codes. Would this line of code appear after the End with or inside the end with after the auto fill line?

Thank you again for your help, you're fabulous!

Michael