Insert lines in two or more sheets at the same time

[brunces]

Friends,

Please, can someone tell me what's wrong with this code of mine?

Here's the code...



Sub InsertLines()

Dim LinNum As Long

While LinNum <= 0
On Error GoTo Exit
LinNum = InputBox("Type the number of lines you wish to INSERT:")
Wend

Sheets(Array("RCI28", "RCI56", "RCI84")).Select
Sheets("RCI28").Activate
Range("A22").End(xlDown).Select
Range(Selection, Selection.Offset(LinNum - 1, 0)).Select
Selection.EntireRow.Insert Shift:=xlDown

Range("A22").EntireRow.Select
Selection.Copy
Range("A22").End(xlDown).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Calculate

Range("A1").Select

Exit:
End Sub


I have three sheets (RCI24, RCI56 and RCI84) and I want to insert lines for these three sheets at the same time. When I do it manually it works pretty well, but when I run this code, it doesn't work the way it should do.

Manually, I select the three sheets, activate one of them and insert the lines. It works fine for the three sheets.

With this code, Excel inserts the rows in "active sheet" only. Do I have to activate sheets, one by one, and repeat the code two more times, one for each sheet? The way the code is now isn't the same syntax of the manually procedure?

Thanks for you attention, guys.

Hugs. :)

Bruno

[mvidas]

Hi Bruno,

Your problem is coming from the line

Sheets("RCI28").Activate
If you remove that line it will work as you want. I'm sure the macro recorder inserted it, but if all sheets are selected and a sheet(x).activate is run, only that sheet is active afterwards (little quirk in excel).

It seems to me that in your sub, two extra lines are being pasted over (if you insert 2 lines, then those 2 lines plus the one above them and the one below are filled in with A22's contents). I'm not sure if this is intentional, perhaps they're all formulas so there isn't a difference. I've re-written the sub for you to run a bit smoother, and included the way you did that (with the 2 extra lines), but commented that out and wrote it so only the inserted lines are filled with A22's contents. I'm not a big fan of GoTo statements, but I left in the way you get LinNum anyways. But I did get rid of the select statements, which I always try and avoid.


Sub InsertLines()
Dim LinNum As Long
While LinNum <= 0
On Error GoTo TheExit
LinNum = InputBox("Type the number of lines you wish to INSERT:")
Wend
Sheets(Array("RCI28", "RCI56", "RCI84")).Select
With Range("A22").End(xlDown)
Range(.Offset(0, 0), .Offset(LinNum - 1, 0)).EntireRow.Insert
End With
With Range("A22").End(xlDown)
Range("A22").EntireRow.Copy Range(.Offset(1, 0), .Offset(LinNum, 0)).EntireRow
' range("a22").entirerow.copy range(.offset(0, 0), .end(xldown)).entirerow
End With
Calculate
TheExit:
End Sub


Matt

[brunces]

Matt,

Very good, buddy! It worked very well! :)

Thank you very much for your attention.

Hugs. :)

Bruno