Shuffle through sheets in a loop?
 

Hi,

I would like to copy an identical selection from multiple sheets
automatically, and then pasting to another sheet. How can i code a loop
that shuffles through the sheets without knowing their names, and then
stopping when it reaches the "paste" sheet? Is there some kind of
numerical sheet "category" that i can use?


--
erikhs
------------------------------------------------------------------------
erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
View this thread: http://www.excelforum.com/showthread...hreadid=562405

Shuffle through sheets in a loop?
 

Erikhs,

Try this:

Sub SheetsLoop()

Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Integer

Set wkb = Application.ActiveWorkbook
For i = 1 To wkb.Worksheets.Count
Set wks = wkb.Worksheets(i)
If wks.Name < "Paste" Then
'Add your copy paste code here
Else
'Do nothing or add other code here
End If
Next i

End Sub

The above code checks the sheet name so you'll need to change the "Paste"
name to whatever you're clling your target sheet.

Hope that helps

Best regards

John



"erikhs" wrote in
message ...

Hi,

I would like to copy an identical selection from multiple sheets
automatically, and then pasting to another sheet. How can i code a loop
that shuffles through the sheets without knowing their names, and then
stopping when it reaches the "paste" sheet? Is there some kind of
numerical sheet "category" that i can use?


--
erikhs
------------------------------------------------------------------------
erikhs's Profile:
http://www.excelforum.com/member.php...o&userid=32788
View this thread: http://www.excelforum.com/showthread...hreadid=562405

Shuffle through sheets in a loop?
 

Hi erikhs

Try This

ActiveWorkbook.Sheets(x)

"x" Refers to the Tabs at the bottom

e.g. :

If your looking for the third sheet along the bottom the "x" equals 3.

Jason Zischke


"erikhs" wrote:


Hi,

I would like to copy an identical selection from multiple sheets
automatically, and then pasting to another sheet. How can i code a loop
that shuffles through the sheets without knowing their names, and then
stopping when it reaches the "paste" sheet? Is there some kind of
numerical sheet "category" that i can use?


--
erikhs
------------------------------------------------------------------------
erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
View this thread: http://www.excelforum.com/showthread...hreadid=562405

Shuffle through sheets in a loop?
 

Hi, i cant get it to work, it seems it copies the data on the first
sheet over and over again, here is my code:

Sub Macro1()
'The data i want to copy
Dim MyRange As Range

'
Dim wkb As Workbook
Dim wks As Worksheet
Dim i As Integer

'Dynamic range for the separate sheets
Range("a6", Range("v6").End(xlDown)).Name = "MyRange"
On Error Resume Next

'Add title and "paste" sheet"
Rows("5").Copy
Worksheets.Add.Name = "Edited"

'Your code
Set wkb = Application.ActiveWorkbook
For i = 1 To wkb.Worksheets.Count
Set wks = wkb.Worksheets(i)
If wks.Name < "Redigeret" Then
'My copy/pasté
Range("MyRange").Copy
Sheets("Edited").Select
'Paste at first blank cell in column A. This doesn't work either by
'the way. Any suggestions?
Set firstBlank = Range("A1").End(xlDown).Offset(1, 0)
Range("firstblank").Select
ActiveCell.PasteSpecial
Else

End If
Next i

End Sub


What could be wronh? Anyone else? Help would really be appreciated!


--
erikhs
------------------------------------------------------------------------
erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
View this thread: http://www.excelforum.com/showthread...hreadid=562405

Shuffle through sheets in a loop?
 

Jason Zischke Wrote:
Hi erikhs

Try This

ActiveWorkbook.Sheets(x)

"x" Refers to the Tabs at the bottom

e.g. :

If your looking for the third sheet along the bottom the "x" equals 3.

Jason Zischke


[/color]

Thank you for your suggestion, but how can i incorporate this into a
loop?
Any ideas?


--
erikhs
------------------------------------------------------------------------
erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
View this thread: http://www.excelforum.com/showthread...hreadid=562405

Shuffle through sheets in a loop?
 

Sub Foo_Test()

Dim x As Long
Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook
For x = 1 To wkb.Worksheets.Count
Set wks = wkb.Worksheets(x)
' Do something
Next x

End Sub

--
Ed
Chief Chef,
Kludge Code Cafe
"Spaghetti Is Our Specialty!"
'
"erikhs" wrote in
message ...

Jason Zischke Wrote:
Hi erikhs

Try This

ActiveWorkbook.Sheets(x)

"x" Refers to the Tabs at the bottom

e.g. :

If your looking for the third sheet along the bottom the "x" equals 3.

Jason Zischke




Thank you for your suggestion, but how can i incorporate this into a
loop?
Any ideas?


--
erikhs
------------------------------------------------------------------------
erikhs's Profile:
http://www.excelforum.com/member.php...o&userid=32788
View this thread: http://www.excelforum.com/showthread...hreadid=562405
[/color]