Shuffle through sheets in a loop?

[erikhs]

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?

[John]

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" <erikhs.2b523q_1153221906.7632@excelforum-nospam.com> wrote in
message news:erikhs.2b523q_1153221906.7632@excelforum-nospam.com...
>
> 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
>

[Jason Zischke]

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
>
>

[erikhs]

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]

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?

[Ed]

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" <erikhs.2b8pcq_1153392006.059@excelforum-nospam.com> wrote in
message news:erikhs.2b8pcq_1153392006.059@excelforum-nospam.com...
>
> 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]