Loop several sheets - not all worksheets of workbook

[al]

Trying to make a macro run on several sheets between 2 sheets "UK" &
"Others" but not working
i.e macro need to loop on sheets France,Germany,Italy,Spain . Pls help

Sub Loopshhets()

Dim WS As Worksheet
Dim roomnight
Dim bednight
For Each WS In Worksheets
If WS.Name <> "UK" And WS.Name <> "OTHERS" Then

Let roomnight = (Range("AL27").Value)
ActiveWorkbook.PrecisionAsDisplayed = False
Range("AL28:AL30").Copy
Range("D28").Select
ActiveCell.Offset(0, roomnight).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With


Let bednight = (Range("AL36").Value)
ActiveWorkbook.PrecisionAsDisplayed = False
Range("AL37:AL39").Copy
Range("D37").Select
ActiveCell.Offset(0, bednight).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End If
Next
End Sub

 

[Don Guillett]

"not working" doesn't tell us much

You need to start at the top to tell us where you want the data to go.
???ActiveCell.Offset(0, roomnight).Select

 

[Dave Peterson]

If you know the names of the worksheets that you want to use, you could use:

for each ws in worksheets(array("france","germany","Italy","Spain"))

If all you know is that you want to work on the worksheets between UK and
Others, then maybe:

(Untested, but it did compile)

Option Explicit
Sub Loopshhets()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RoomNight As Variant 'long or string???
Dim BedNight As Variant 'long or string???
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False

MinIndex = Worksheets("UK").Index
MaxIndex = Worksheets("Others").Index

If MinIndex > MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("uk").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index > MinIndex _
And .Index < MaxIndex Then
'do the work
RoomNight = .Range("al27").Value
Set RngToCopy = .Range("AL28:AL30")
RngToCopy.Copy
With .Range("D28").Offset(0, RoomNight)
.PasteSpecial Paste:=xlPasteValues
With .Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End With

BedNight = .Range("AL36").Value
Set RngToCopy = .Range("AL37:AL39")
RngToCopy.Copy
With .Range("D37").Offset(0, BedNight)
.PasteSpecial Paste:=xlPasteValues
With .Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End With
End If
End With
Next WS
End Sub

The dots in front of those range objects mean that they refer to the objects in
the previous With statement.

So
BedNight = .Range("AL36").Value
means that this variable is picked up from AL36 of each sheet.

Is that what you meant?

 

[al]

If you know the names of the worksheets that you want to use, you could use:

for each ws in worksheets(array("france","germany","Italy","Spain"))

If all you know is that you want to work on the worksheets between UK and
Others, then maybe:

(Untested, but it did compile)

Option Explicit
Sub Loopshhets()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RoomNight As Variant 'long or string???
Dim BedNight As Variant 'long or string???
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False

MinIndex = Worksheets("UK").Index
MaxIndex = Worksheets("Others").Index

If MinIndex > MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("uk").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index > MinIndex _
And .Index < MaxIndex Then
'do the work
RoomNight = .Range("al27").Value
Set RngToCopy = .Range("AL28:AL30")
RngToCopy.Copy
With .Range("D28").Offset(0, RoomNight)
.PasteSpecial Paste:=xlPasteValues
With .Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End With

BedNight = .Range("AL36").Value
Set RngToCopy = .Range("AL37:AL39")
RngToCopy.Copy
With .Range("D37").Offset(0, BedNight)
.PasteSpecial Paste:=xlPasteValues
With .Resize(RngToCopy.Rows.Count, _
RngToCopy.Columns.Count).Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
End With
End If
End With
Next WS
End Sub

The dots in front of those range objects mean that they refer to the objects in
the previous With statement.

So
BedNight = .Range("AL36").Value
means that this variable is picked up from AL36 of each sheet.

Is that what you meant?

Dave thxs the macro works thxs - 1 last question to you - can you
instead of
for each ws in worksheets(array("france","germany","Italy","Spain"))
using something else starting from "france" to "spain" i.e gemany &
italy in between included
e.g for each ws in worksheets(array("france" to "Spain"))
pls advise

 

[Dave Peterson]

Nope.

You could loop through the indices, though. That's what the second suggestion
did.

 

[al]

Nope.

You could loop through the indices, though. That's what the second suggestion
did.

dave thxs i think that the 2nd suggestion is better for a wide
selection - did you manage to combine my 3 macros ?

 

[Dave Peterson]

I don't understand your question.

dave thxs i think that the 2nd suggestion is better for a wide
selection - did you manage to combine my 3 macros ?

 

[al]

I don't understand your question.



al wrote:

it's ok dave - i was only referring to a personal reply i made to you
- managed to get all my answers piecemeal - thxs for your help

 

[Dave Peterson]

I never got any email from you, but glad you got things worked out.

I think it's better to keep the discussions in the newsgroups. You'll have many
more people reading and potentially responding to your posts.