Hi! I'm fairly adept with excel but new to VBA, however when trying to make my first macro I came accross this thread through google searching: http://www.ozgrid.com/forum/showthread.php?t=198107
It's similar to what I wanted to do, but wasn't exactly what I needed, so I've attempted to tweak the macro below. What the macro is supposed to do is insert a row above the currently selected row on both sheet1, but also sheet2 and sheet4 as well, and then copy the formulas and formatting from the above row down.
Currently I have this:
Sub AddRows() Sheets(Array("sheet1", "sheet2", "sheet4")).Select Sheets("sheet1").Activate ActiveCell.Resize(1).EntireRow.Insert ActiveCell.Offset(-1, 1).EntireRow.Copy ActiveCell.Resize(1).EntireRow.PasteSpecial Paste:=xlPasteFormats ActiveCell.Resize(, 4).PasteSpecial Paste:=xlPasteFormulas Application.CutCopyMode = False Sheets(Array("sheet1")).Select End Sub
Now, as I mentioned above, if I have, for example, a cell in row 25 selected in sheet1, I want to be able to run the macro and have it insert a row above row 25 in sheets1, 2 & 4, then copy over all the formatting from the previous rows on the respective sheets along with any formula in the first four columns. I've attempted to get this working myself but it isn't quite working. It seems to look ok for the main sheet1 (though the only first four columns bit isn't working, it's copying everything over), however on the other two sheets it isn't adding new rows, instead it's just copying the formulas from the previous row onto the next row, overwriting a row of data. Naturally not ideal! If anyone could point me where I'm going wrong this would be really appreciated. Thanks!