End-Down type function?

[smashclash]

I want to create a macro for a group of workbooks that will copy data in a cell and then paste that data down in an end down type style. I know how to write a macro to do this. The problem I will run into is when a workbook has only 1 row of data that needs to be changed. If I use the end down function when I have only 1 row of data it will copy a useless formula all the way down to the total at the bottom of the column. Is there a way around using an end-down function to do this?

Below is the code for a sheet with only 1 row: (an end-down function won't work here).

 

[Joe4]

Yes, you need to tell it what to do in both instances. Can you explain, in plain English, exactly what it should do in each situation (copy what? to where?).

 

[smashclash]

Basically what i want to do is this:

Cell E14 should add up for months Jan - Dec. Then if there is data in cell E15, the formula could be copied down from cell E14. and so on and so on.

If there isn't data in cell E15, I do not want it to copy the formula or do anything at all. I then want to do the same type of formula for column G. I figure if I can figure out how to get this to work in Column E, it should be no problem for the other columns.

 

[Joe4]

OK, I am confused now.

What column are we using to determine how many rows to copy down (which column determines the criteria, the rows with data)?

Then, which column do we want to copy down?

If they are both one in the same, (i.e. column E), it doesn't make sense, unless you want to overwrite the formulas already in there.

For example, let's say there are values in E14:E16. The formula would then copy over E15 and E16 with the formula in E14, thus overwriting what is currently in these cells? Is that what you are intending to do?

 

[smashclash]

OK, I am confused now.

What column are we using to determine how many rows to copy down (which column determines the criteria, the rows with data)?

Then, which column do we want to copy down?

If they are both one in the same, (i.e. column E), it doesn't make sense, unless you want to overwrite the formulas already in there.

For example, let's say there are values in E14:E16. The formula would then copy over E15 and E16 with the formula in E14, thus overwriting what is currently in these cells? Is that what you are intending to do?

I do want to copy over the data that is already there. The data that is already there is only February data. However, the worksheet is setup to fill in each cell with some type of number. A 0 is used if there is no activity. This means that I can use the end down functions without a problem because any cell that needs to be changed will be picked up using an end down function.

Each column could technically have a different amount of data. By copying the February sheet as my YTD template, I am ensuring the that any data that needs a formula containing Jan - Dec is there. So, my Feb sheet has some type of data in ALL applicable cells. This means that I can simply click on the top row of a particular column, change the data to the Jan-Dec forumla. Then if there is any data filled in below it, it will copy the top one and paste it down.

The current code I have now works fine for templates that have more than one row of data. I get in trouble when there is only one row of data because my current macro doesn't know what to do if it doesn't have to copy the formula down.

 

[Joe4]

OK, it makes sense now. All we have to do is move all the copy steps inside the IF statement.

    Range("E14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("E14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If

 

[smashclash]

OK, it makes sense now. All we have to do is move all the copy steps inside the IF statement.

    Range("E14").Select
    ActiveCell.FormulaR1C1 = _
        "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
    Range("E14").Select
    If Len(ActiveCell.Offset(1, 0)) > 0 Then
        ActiveCell.Copy
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If

awesome, works great. now I'm going to set it up for all the other columns and I should be good to go. Thanks for your help and patience.

 

[smashclash]

actually one last thing. I know there is a way to get this macro to run on all open workbooks. You wouldn't happen to know that code would you?