Problem 1: two issues- To select a cell on a non-active sheet, first select the sheet. Then select the cell
- You don't have to .Select a sheet or cell to copy\paste\insert with it.
Try this...
Private Sub CommandButton1_Click()
Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet 1", "Sheet 2"))
ws.Rows(9).Insert Shift:=xlDown
'This formats the cells
With ws.Range("C9:AG9")
.Borders.Weight = xlThin
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
End With
Next ws
End Sub
Problem 2:
Dim y As Long
y = Application.InputBox("How many rows do you want to add?", "", Type:=1)
If y = 0 Then Exit Sub
With Sheets("Sheet 3").[A65536].End(xlUp)
.EntireRow.Copy
.Resize(y).EntireRow.Insert
End With
Application.CutCopyMode = False
Bookmarks