Need help with macro to insert rows and copy data

[GMM]

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.

[CBrine]

GMM,
This code should work for you, as long as Column A of you table always has data in the cells of column A.

Sub Copy36()
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
ws.Range("36:36").Copy ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0)
End Sub


HTH
Cal

[CBrine]

AGGGGGHHHHHH, again!!!!!

[lucas]

Got yer back Cal....All deleted.

[GMM]

GMM,
This code should work for you, as long as Column A of you table always has data in the cells of column A.

Sub Copy36()
Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet
ws.Range("36:36").Copy ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1, 0)
End Sub


HTH
Cal

Thanks for your help but it didn't work. One problem is that data begins in column I, not A. So, I replaced "A" with "I" in the code. Still didn't work. Do I need to replace "(1,0)" with "(1,9)"? Also, I'm not sure that code would paste the way I want to paste. I want to paste only the values, i.e. "Paste Special...Values".

Thanks again.

[CBrine]

GMM,
I will need to know the extact range you want to copy(ie. I36:M36??)
and the column you want to paste it to. The change you've made will not fix it, since it's still trying to copy an entire row of data.

Cal



Got yer back Cal....All deleted.

Thanks Lucas!!

[CBrine]

Lucas,
I give up:dunno
:-)

PS-Not for you GMM, it's a message for lucas 'cause of my multiple posts.

[lucas]

Don't give up Cal....it's happened to me a couple of times and others too. One time when it happened to me I later noticed that my enter key was stuck down......doubt it thats it for you though. Sometimes there doesn't seem to be a reason....server..one or the other maybe. Who knows. Don't worry too much about it though.