I have set a data on which i am running historical regression and correlation analysis on. I would like to create a macro that would copy my results to another worksheet, then delete the top row of data, run the analysis over again for the remaining data and repeat. i have attached a spreadsheet to help clarify what i am trying to do.
The data tab contains the data and analysis i am performing. In this case, I would like to copy cell b1 to worksheet "historical analysis" where it would be placed in column B, next to its corresponding date, e.g. cell B5. Then i would go ahead and delete the data in worksheet "data", row "5", with everything moving up one row, and the copy the analysis from B1 and paste in worksheet "historical analysis", cell "B6", so on and so forth.
The file i have provided is a small microcosm of what i am trying to do. Here is what i was able to come up with, though it doesnt work:
Dim LastRow As Long
For i = 17 To LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets("historical analysis").Range("B40000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False