PC Review


Reply
Thread Tools Rate Thread

array sintax

 
 
Paulo
Guest
Posts: n/a
 
      2nd Oct 2008
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
 
Reply With Quote
 
 
 
 
NickH
Guest
Posts: n/a
 
      2nd Oct 2008
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
 
Reply With Quote
 
Paulo
Guest
Posts: n/a
 
      2nd Oct 2008
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
>

 
Reply With Quote
 
NickH
Guest
Posts: n/a
 
      2nd Oct 2008
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
countblank sintax Alberto Ast Microsoft Excel Programming 2 25th Oct 2009 01:46 AM
Sintax error simon Microsoft ASP .NET 1 21st Sep 2005 11:12 AM
Sintax Help! Smonczka Microsoft Excel Discussion 4 24th May 2005 08:28 PM
ImportCarier sintax Mikhail Mintchenkov Windows XP Embedded 1 18th Mar 2005 09:28 PM
Sintax error =?Utf-8?B?Um9kb2xmbyBGb250ZXM=?= Microsoft Access Queries 1 8th Apr 2004 06:54 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:38 PM.