I need help with a Macro to insert a specified number of rows in one worksheet and the same number but columns in another worksheet. I also need to mare sure the formulas are carried over.
The rows are inserting fine but I can't get the columns to work. Here is what I have so far:
The rows are inserting fine but I can't get the columns to work. Here is what I have so far:
Code:
Sub InsertRows_Columns()
Dim iRows As Long, rngStart As Range
On Error GoTo Canceled
iRows = InputBox("How many unit rows would you like to insert?", "Number of Rows", 5)
If iRows = 0 Then Exit Sub
With ActiveWorkbook.Worksheets("Sheet1")
Set rngStart = .Rows("10")
rngStart.Offset(1, 0).Resize(iRows, 1).EntireRow.Insert Shift:=xlDown
rngStart.EntireRow.Copy
rngStart.Offset(1, 0).Resize(iRows, 1).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
With ActiveWorkbook.Worksheets("Sheet2")
Set rngStart = .Columns("I:I")
rngStart.Offset(1, 0).Resize(iRows, 1).EntireColumn.Insert Shift:=xlToRight
rngStart.EntireColumn.Copy
rngStart.Offset(1, 0).Resize(iRows, 1).EntireColumn.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
Canceled:
End Sub