Excel macro insert 10 blank rows at top of every tab in a workbook

[dwilson38550m]

Hi, is it possible to use a macro to insert 10 empty rows in row 1 of a workbook and do this for every tab in the workbook? Then I would like to copy a table and I insert this into the 10 blank rows created? I have 100 tabs on the workbook so want to avoid doing things manually.

Thanks.

 

[My Aswer Is This]

Inserting the rows is easy using Vba.
But you did not give us the Name of the Table and did not say where to paste it.

Do you want to paste it starting in Range("A1") of every sheet?
And on what sheet is the Table now?
We need specific details.

 

[dwilson38550m]

Hi, I would like to insert blank rows (rows 1 to 10) on all tabs of the workbook other than the tab called "Table" . Then I'd like to copy the table on cells A1 to P10 from the "Table" tab and paste onto very tab on the w/book (cell A1 to P10) other than the Table tab.

Thanks

 

[DanteAmor]

Try this

Sub insert_blank_rows()
  Dim sh As Worksheet
  For Each sh In Sheets
    If LCase(sh.Name) <> LCase("Table") Then
      sh.Rows("1:10").Insert
      sh.Range("A1:P10").Value = Sheets("Table").Range("A1:P10").Value
    End If
  Next
End Sub

 

[dwilson38550m]

Thanks, will this enter the Table in all the sheets in the workbook (apart from the Table tab itself)? Thanks I'll give it a go.

 

[DanteAmor]

In all tables except the "Table" sheet. Try and tell me.

 

[Peter_SSs]

Perhaps we could do all the tabs at once? (Assuming no formulas of formatting required from the copied table, only values)

Sub InsertAndCopy()
  Sheets.Select
  Rows("1:10").Select
  Selection.Insert
  Range("A1:P10").Select
  Selection.Value = Sheets("Table").Range("A11:P20").Value
  Sheets("Table").Rows("1:10").Delete
  ActiveSheet.Select
End Sub