VBA to see cell value instead of formula

A modified VBA originally provided by Anonymous on July 2, 2004 works for what I need if the cell in column A contains a manually entered value. I am using a formula in the target cell that will result in either a blank cell or a value. I need to modify the VBA to allow for the formula but ignore a blank cell and fill down only when the formula results in a value. Here is what I currently have:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":AM" & i - 1).Copy Range("B" & i & ":AM" & i)
Application.EnableEvents = True
On Error GoTo 0
End Sub

Much appreciation for any help.

My original has multiple sheets and each sheet has many columns. Sheet1 is used to input data. The cell formulas in all other sheets extract data from Sheet1 dependent upon an index number assigned to the appropriate sheet in column A of each sheet. Some of the cell formulas are very long and complex. My original is exceedingly large and slow. For the sake of file size and calculation/open/save times, I need a VBA that will only fill down the formula rows when an index number is assigned by the existing cell formula in column A of each sheet. The present VBA does not recognize the index number unless it is manually entered negating the convenience of the auto-sorting system used to assign data to specified sheets.

I have built a highly simplified version of the original workbook as a sample that should demonstrate the problem.

Thank you for your help.

I'm sorry, I'm not following, rory. I'm a true novice at VBA.

I need the sheet VBA to see only the value if and when it is assigned in column A by the cell formula. My current code is included in my original post. I've attached a working workbook example in my last post. As can be seen in it, the index number is assigned to a sheet. However, the formulas will not fill down. If I manually enter the the value over the formula generated value, it then fills down. I'm trying to find the solution to make it dependent entirely on the formula generated data and don't understand VBA well enough to figure out how to modify what I have.

Thank you for the suggestion, Carim. Sheet 1 is the only sheet in which data is manually entered. It utilizes 30 columns and currently has a large number of rows containing manually entered data which must be retained. All new data is unique and unrelated to all previously entered data and is entered in a new row. The database and the number of rows with it is constantly expanding and will reach several thousand rows. Specifying 30 entire columns in the table range is not practical. Having to change the table range with each new entry is undesirable.

Data from each new row entered in sheet 1 is distributed to multiple sheets depending on the nature of the data. All sheets that utilize data from sheet 1 contain differing numbers of rows of data because data entered in sheet 1 may or may not be relevant to the sheet. All data from sheet 1 is used somewhere. If the sheet 1 database has 500 rows of data, sheet 2 may have 150 rows, sheet 2 may have 325 rows, sheet 3 may have 75 rows, etc. Each sheet that is collecting data currently contains 2500 rows of long complex formulas. There is a lot going on in the workbook and it's really slow.

I would like to delete the formulas in all rows that do not contain data, excluding column A, in all data collection sheets. The formula in column A of each data collection sheet determines data relevance. If new data from sheet 1 is relevant, the last empty cell in column A will show an index number referencing the data from sheet 1. This is what I need to key on. I would like to fill down formulas in the collection sheets only if an index number appears in column A of a new row in that sheet.

The VBA in my previous posts works but only if I manually enter the index number over the formula in column A.

I am not very knowledgeable in VBA. I don't understand what rory is suggesting and don't know how to accomplish what I'm trying to accomplish.

I hope this helps to explain what I need.

Thanks again.

Thank you again, Carim. One of my big objectives is to eliminate the very long and complex formulas in tens of thousands of cells until they are required, in order to speed up the workbook. This can be achieved by using the code I included in previous posts. But this code still requires that I go to each sheet and manually enter an index number in column A over the formula in the column A cell that has already assigned an index number to that cell.

I don't know how to modify the code to achieve that. Alternatively, I don't know what I'm doing in the first place when it comes to VBA and need help with code that will accomplish that.

In my mind, I don't require a module for the entire workbook since some sheets are not affected by column A index number data assignment. This is why the more simplistic example that I attached shows the code in each of the three data transfer sheets.

My original workbook is very, very large. I'm trying to shrink it down to make it faster.

Carim, you're amazing to keep going at it and I'm just not making what you're going at very clear. I greatly oversimplified the example attached in message #3. I apologize for my vagueness.

In my original file a new row of data entered in sheet 1 includes a date. New rows of data are not always entered sequentially according to the dates entered. Therefore the date entered in row 100 may be prior to the dates in the previous 10 rows. The next column in sheet 1 ranks the dates entered in all rows. The rank creates an index number for all data in that row. The rank may change depending on dates entered for data in new rows. If any row in sheet 1 contains data that is relevant to sheet 2, the formula in column A of sheet 2 assigns the current rank/index number from sheet 1 for that row of data. What occurs is, all data in sheet 2 (and other sheets) will always be sequential by date regardless of the sequence the data was entered in sheet 1.

I've attached a revised version of the message #3 sample file. The revised file includes both date and rank indexing. Dates in this example are non-sequential in sheet 1 to demonstrate the significance of the changing index number for sequencing data in other sheets. I've also set the formulas in sheet 1 columns D, E and F to key on the current rank/index number of the date for each row to demonstrate the dependency of all the data on the possibility of a changing index number. This should make it easier to understand why the formula in column 1 of other sheets must remain intact.

- Data is all manually entered in sheet 1 with the exception of the rank column and columns used for data assignment such as columns D, E and F in the example.

- The VBA code that I have can be modified to fill down formulas in sheet 1 based on the date entry so this sheet is not a problem.

- Data from previous entries may on occasion be changed and possibly including date.

- All data in sheets 2, 3 and 4 in the example must remain capable of changing with any change to data or change in ranking in sheet 1.

I have added a row of data in sheet 1 of the new example. It should be assigned to sheet 2 "X". In sheet 2 you will see that column A has picked up the current index number for the data but the code did not fill down the formulas for columns B,C and D as desired.

I hope I gave you what you need this time. Thanks for your tremendous patience.