VBA code in Excel to add a row to multiple sheets and then copy formula from adjacent row

Question

I'm really hoping someone can help me with this one. I have recorded a macro to use within a sheet that needs to create a row at the same position on 2 worksheets and then, on one of them, copy the formula's in the cells from the row below it. The code I have looks like this -

Sub Macro1()
Sheets(Array("SCHEDULE", "ANNUAL SUMMARY")).Select
Sheets("SCHEDULE").Activate
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets("ANNUAL SUMMARY").Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.AutoFill Destination:=ActiveCell.Offset(-1, 0).Rows("1:2").EntireRow _
    , Type:=xlFillDefault
ActiveCell.Offset(-1, 0).Rows("1:2").EntireRow.Select
Sheets("SCHEDULE").Select
ActiveCell.Select

My problem is, when I run it manually and then record the macro, it does exactly what I want it to, but when I run this from a button on the "SCHEDULE" sheet it does not copy the formula's from the row below the one on the "ANNUAL SUMMARY" sheet.

Can anyone help to get this working with me?

Thanks all in advance

Mark

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 27 '13 at 19:09

Sam · Accepted Answer · 2013-11-27T20:23:11.717

0

The problem with the macro recorder is that although it can give you a good indication of what code you need, it also generates very inefficient code and includes all of the select and activate statements that you need to try and avoid using.

Any reference in the code to ActiveCell is referring to the cell that is currently selected and ActiveSheet is the sheet that is currently selected. This can give you undesired results if you run the macro from a different sheet that the macro was recorded from...

If you wanted to copy row 1 from SCHEDULE sheet then you can use

Sheets("SCHEDULE").Rows(1).Copy Sheets("ANNUAL SUMMARY").Rows(1)

If you want to auto fill a range, then this can be accomplished with a single line of code

This will auto fill the contents of row1 (column A - E) down to row 100 in your ANNUAL SUMMARY sheet

Sheets("ANNUAL SUMMARY").Range("A1:E100").FillDown

So if we put it all together and include some declarations for our source and destination sheet to make the sub more readable..

Sub CopyAndFillDownExample()
    Dim rowNumber As Long, offset As Long

    Dim sourceSht As Worksheet, destinationSht As Worksheet

    'set the source and destinationsheets
    Set sourceSht = Sheets("SCHEDULE")
    Set destinationSht = Sheets("ANNUAL SUMMARY")

    'number of rows to copy down
    offset = 100

    'get currently selected row
    rowNumber = ActiveCell.Row

    'copy the selected row from the source sheet to the destination sheet
    sourceSht.Rows(rowNumber).Copy destinationSht.Rows(rowNumber)

    'fill down the formulas
    destinationSht.Rows(rowNumber & ":" & rowNumber + offset).FillDown

End Sub

edited Nov 27 '13 at 20:23

answered Nov 27 '13 at 19:30

Sam

7,245
3
25
37

Thanks Sam. How would I use this in this instance? If the macro creates the new row (and it does this fine) on both sheets how would I then target just the specific row that I need to populate using the code you provide? – Mark Hirst Nov 27 '13 at 19:51
I just added a line of code to my answer that demonstrates copying a row. So you would copy your row to the new sheet, then auto-fill down on the sheet that you copied to. Do you always copy the data to a fixed position in your target sheet? ie row 1 – Sam Nov 27 '13 at 19:54
So the way this works is that the person using the workbook selects a cell on the row below which they wish to create the new row (usually in column A) on Sheet 1. They then need to run the macro that creates this row and a row on another worksheet (Sheet 2) who's row number corresponds with the row on sheet 1. The row on Sheet 2 then needs to be populated with the formula below the row that has been created (therefore keeping the contiguous pattern of the formula) - does that make sense? – Mark Hirst Nov 27 '13 at 20:00
There are formula in several columns that need to be copied – Mark Hirst Nov 27 '13 at 20:04
@MarkHirst I've added a more complete code sample into my answer, I don't think that it's exactly what you want but you should be able to get an understanding of how it works and what it's doing... – Sam Nov 27 '13 at 20:29
1

Massive thank you @Sam. Your code really got me thinking about a different (easier) way of doing what I ultimately needed this tool to do and having thought about it in a different way this became for more effective. I ended up just having a button on sheet 1 that inserted a row and then a worksheet_activate macro that just copied down a reference row to the next 50 rows - worked like a charm. – Mark Hirst Nov 29 '13 at 09:13

VBA code in Excel to add a row to multiple sheets and then copy formula from adjacent row

1 Answers1