array sintax
 

i have recorded a macro. and that's exactly what i want to do, but need it to
be a little more versatile.

Sheets(" DB ").Select
Rows("1:6").Select
Selection.Copy
Sheets(Array("asdd", "Plan9", "Plan10", "Plan11", "Plan7")).Select
Sheets("asdd").Activate
Rows("1").Select
Selection.Insert Shift:=xlDown

i would like to select the arrey using the sheets number insted of the
"names"
something like:
Sheets(Array("1 : worksheet.count").Select

i thank in advance

array sintax
 

Hi Paulo,

Maybe you can adapt this to suit...

Sub SelectAllSheets()
Dim i As Long
Dim arrSheets() As Long

For i = 0 To Worksheets.Count - 1
ReDim Preserve arrSheets(i)
arrSheets(i) = i + 1
Next i

Sheets(arrSheets).Select
End Sub

Br,
NickH

array sintax
 

NickH
thank you very much for your help thats exactly what i was looking for.

while i was waiting for your answer i was able to figure it out doing a
copyN'paste loop

would your way be faster than mine?
i think so, thats why i needed it.
i am working with some serious amount of worksheeet...

then again thaks for the help.
Sub TopCaption()

Dim x As Long
Const StartingSheet As Long = 1

For x = StartingSheet To Worksheets.Count
Worksheets(x).Activate
If Range("A4").Value = "Nº" Then
If x = "1" Then
x = 2
End If
Else
Rows("1").Select
Selection.Insert Shift:=xlDown
End If
Sheets(" DB ").Select
Rows("1:6").Select
Selection.Copy

Next
End Sub



"NickH" wrote:

Hi Paulo,

Maybe you can adapt this to suit...

Sub SelectAllSheets()
Dim i As Long
Dim arrSheets() As Long

For i = 0 To Worksheets.Count - 1
ReDim Preserve arrSheets(i)
arrSheets(i) = i + 1
Next i

Sheets(arrSheets).Select
End Sub

Br,
NickH

array sintax
 

Hi Paulo,

Yes the array method is probably faster than a loop but without
knowing exactly what you're trying to do (I can see you are
propogating headers but I can't deduce you're workbook structure from
the code) I wouldn't like to suggest which is the best way to go. If
speed is an issue the following should do pretty much the same as the
routine you posted above but without selecting anything or activating
any sheets, which is rarely necessary...

Sub TopCaption2()
Dim x As Long
Const StartingSheet As Long = 1

For x = StartingSheet To Worksheets.Count
With Worksheets(x)
If .Range("A4").Value = "Nº" Then
If x = 1 Then
x = 2
End If
Else
.Rows(1).Insert Shift:=xlDown
End If
End With

Sheets(" DB ").Select
Rows("1:6").Copy
Next x

Application.CutCopyMode = False
End Sub


HTH NickH