Loop several sheets - not all worksheets of workbook
 

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

Loop several sheets - not all worksheets of workbook
 

"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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"al" wrote in message
...
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

Loop several sheets - not all worksheets of workbook
 

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

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

--

Dave Peterson

Loop several sheets - not all worksheets of workbook
 

On Jan 25, 3:53 am, Dave Peterson wrote:
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 wrote:

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

--

Dave Peterson

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

Loop several sheets - not all worksheets of workbook
 

Nope.

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



al wrote:
Dave Peterson

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

Loop several sheets - not all worksheets of workbook
 

On Jan 25, 5:07 pm, Dave Peterson wrote:
Nope.

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

al wrote:
Dave Peterson

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

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

Loop several sheets - not all worksheets of workbook
 

I don't understand your question.

al wrote:

<<snipped
Dave Peterson

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

--

Dave Peterson

Loop several sheets - not all worksheets of workbook
 

On Jan 25, 8:23 pm, Dave Peterson wrote:
I don't understand your question.



al wrote:

<<snipped
Dave Peterson

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

--

Dave Peterson

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

Loop several sheets - not all worksheets of workbook
 

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.

al wrote:

On Jan 25, 8:23 pm, Dave Peterson wrote:
I don't understand your question.



al wrote:

<<snipped
Dave Peterson

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

--

Dave Peterson

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