GMM
01-25-2007, 09:20 AM
First time I've posted here so forgive me if this problem has already been solved. I've searched and searched and haven't found it here.
What I am trying to do is to create a macro that will insert a row at the end of a range, then copy all the data from row 36, then "Paste Special... Values" in the row that was just created at the end of that range. I'm not a VBA expert so this has been quite a challenge.
The tricky part for me is that the end of the range is always changing. Each day I'm analyzing data with formulas at the top of the worksheet; this data is summarized in row 36. Then I'm copying and pasting each days' results from row 36 to the end of a range that has all the previous days' results in it. For example, today's results will need to be copied into row 82, tomorrow it will be row 83, the next day row 84, etc. FYI, the reason I want to insert a row at the end of the daily results' range is because there are more formulas below the daily results so I don't want to overwrite them.
I've created an array formula (in cell D38) that displays the correct row in which to insert a new row and paste new data. Can this be used in the macro? If not, how do I come up with VBA code that will recognize the row at which to insert a new row and then paste data from row 36?
Your help will be much appreciated. Thank you.
What I am trying to do is to create a macro that will insert a row at the end of a range, then copy all the data from row 36, then "Paste Special... Values" in the row that was just created at the end of that range. I'm not a VBA expert so this has been quite a challenge.
The tricky part for me is that the end of the range is always changing. Each day I'm analyzing data with formulas at the top of the worksheet; this data is summarized in row 36. Then I'm copying and pasting each days' results from row 36 to the end of a range that has all the previous days' results in it. For example, today's results will need to be copied into row 82, tomorrow it will be row 83, the next day row 84, etc. FYI, the reason I want to insert a row at the end of the daily results' range is because there are more formulas below the daily results so I don't want to overwrite them.
I've created an array formula (in cell D38) that displays the correct row in which to insert a new row and paste new data. Can this be used in the macro? If not, how do I come up with VBA code that will recognize the row at which to insert a new row and then paste data from row 36?
Your help will be much appreciated. Thank you.