0

I'd like a button that asks user for the number of rows they want to add. That number of rows will then be added to multiple sheets (I want to define this), in the exact same place for each - A5 xldown offset 1,0. Copy formulas and formatting from above rows in each sheet. I can do it manually for each but can't grasp how to define the array and do the same in each sheet.

'for each worksheet that I define

Range("A5").Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Insert 
  Shift:=xlShiftDown,CopyOrigin:=xlFormatFromLeftOrAbove

'does copy origin copy both formulae and formatting?

Apologies in advance if code is poorly formatted

sej27
  • 3
  • 2

1 Answers1

0

I can do it manually for each but can't grasp how to define the array and do the same in each sheet.

Below how to loop multiple worksheets in an defined array of sheets.

Sub LoopSheets()

Dim ws as Worksheet
Dim Rws as long: Rws = Application.InputBox(prompt:="How many rows?", Type:=1)

For Each ws In Sheets(Array("Sheet1", "Sheet2")) 'Etc
    'Your code to do something *
Next ws

End Sub

'* For this part of your code you should first refer to this post on SO on how to avoid .Select amongst other tips.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you. How how would you put the other code in without using Select? And is there a way I can request the number of rows to be entered by the user? – sej27 Oct 21 '19 at 11:43
  • @sej27, I have added some code you could use to ask the user for a number of rows. And the link I have included to the other post is a very good starting point for you to learn how to avoid to use `.Select`. – JvdV Oct 21 '19 at 13:14