Consolidating/Improving VBA

[anichols]

Hello all,

I have this VBA code:

    Sheets(1).Select
    Rows("1:1").Copy
    Sheets(4).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(5).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(7).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(8).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(9).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(10).Rows("1:1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(4).Cells.EntireColumn.AutoFit
    Sheets(5).Cells.EntireColumn.AutoFit
    Sheets(6).Cells.EntireColumn.AutoFit
    Sheets(7).Cells.EntireColumn.AutoFit
    Sheets(8).Cells.EntireColumn.AutoFit
    Sheets(9).Cells.EntireColumn.AutoFit
    Sheets(10).Cells.EntireColumn.AutoFit

This works great, no problems whatsoever, but I was curious, in the interest of becoming better with my code, is there a way to shorten that code as I am doing the same thing to the same range of sheets?

Many thanks

 

[Fluff]

How about

   Sheets(1).Rows(1).Copy
   Sheets(Array(4, 5, 6, 7, 8, 9, 10)).Select
   Selection.Rows(1).PasteSpecial xlPasteValues
   For i = 4 To 10
      Sheets(i).Columns.AutoFit
   Next i
   Sheets(1).Select

 

[anichols]

How about

   Sheets(1).Rows(1).Copy
   Sheets(Array(4, 5, 6, 7, 8, 9, 10)).Select
   Selection.Rows(1).PasteSpecial xlPasteValues
   For i = 4 To 10
      Sheets(i).Columns.AutoFit
   Next i
   Sheets(1).Select

That's very nice and consolidated, however, it is pasting to row 2 and not row 1 on all the sheets.

 

[anichols]

This seemed to fix it though.

   Sheets(1).Rows(1).Copy
   Sheets(Array(4, 5, 6, 7, 8, 9, 10)).Select
   Selection.Rows(0).PasteSpecial xlPasteValues
   For i = 4 To 10
      Sheets(i).Columns.AutoFit
   Next i
   Sheets(1).Select

 

[Fluff]

That should fail as there is no such thing in Excel as row zero.

 

[anichols]

That should fail as there is no such thing in Excel as row zero.

So what is supposed to work in place? (1) and ("1:1") result in row 2 being filled in.

 

[Fluff]

It should be 1 as I showed, using 0 should result in a RTE 1004 "Application-defined or object-defined error"

 

[anichols]

That's really odd, is it possible that there's some VBA setting that I'm unaware of?

 

[Fluff]

Not that I'm aware of.