Selecting all sheets but....

[percy4]

Hi All,

I am currently trying to put together a macro that will select multiple sheets and copy and paste values from one column to another. The macro has to work regardless of how many sheets there are in the workbook.

What I was thinking is that I can’t use the index no. since I don’t know how many sheets there are in that workbook. So what I want is to select all sheets but a number of fixed names.

How can I do this? I’ve tried following but I cant get it to work.

[VBA]
Sub test()
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "Article and quantities", "summary"
ws.Select
Case Else
ws.Select False
End Select
Next ws
Sheets(1).Activate
Range("A6").Select
Application.ScreenUpdating = True
End Sub
[/VBA]

Thanks for your help!

BR
Per

[omnibuster]

Try this .

[VBA]Sub CountSheets()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Activate
Range("A1").Activate
Next
End Sub
[/VBA]

[percy4]

Try this .

[vba]Sub CountSheets()
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Activate
Range("A1").Activate
Next
End Sub
[/vba]

Hi,

Thanks for your fast reply but I cant understand what this will help me with.

What I want is to select all sheets exept the one with the names "summary" "article" and "template".

[omnibuster]

Maybe i dont undestand what you want!
[VBA]
Sub CountSheets()
Dim i As Integer
Dim ws As Worksheet
For i = 1 To Sheets.Count
If Sheets(i).Name = ("summary") Or Sheets(i).Name = ("article and template") Then
GoTo 10
Else
Sheets(i).Select
Range("A6").Activate
MsgBox Sheets(i).Name
End If
10
Next
End Sub
[/VBA]

[GTO]

...What I want is to select all sheets exept the one with the names "summary" "article" and "template".

Greetings to all,

Maybe?

[vba]
Option Explicit

Sub ex()
Dim ws As Worksheet
Dim strShNames() As String
Dim i As Long

ReDim strShNames(0)
i = 0

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = "template" _
And Not ws.Name = "summary" _
And Not ws.Name = "article" Then

strShNames(UBound(strShNames)) = ws.Name
i = i + 1
ReDim Preserve strShNames(UBound(strShNames) + 1)
End If
Next
ReDim Preserve strShNames(UBound(strShNames) - 1)
ThisWorkbook.Worksheets(strShNames).Select
End Sub
[/vba]

Hope this helps,

Mark

[GTO]

Percy,

Off to bed for this lad, but please note that cross-posting w/o providing a link to the other site's thread is, well... not a way to get oodles of help.

Ken Puls has IMHO the nicest, clearest explanation of why, but in short, it wastes time for anyone answering to either and/or both have to check multi threads or waste time offereing similar suggestions.

I hope that makes sense,

http://www.mrexcel.com/forum/showthr...=399898&page=2

Mark