Hi,

Can anyone suggest how I can loop between two sheets within a workbook
please? I do not know how many sheets there will be in the workbook, but I
do know the names of two of them. It is between these two sheets ("FRONT"
and "END") that I wish to loop and paste data into.

Any advice greatly appreciated.

Thanks,

Ewan.

Hi Ewan7279,

Can anyone suggest how I can loop between two sheets within a workbook
please? I do not know how many sheets there will be in the workbook, but I
do know the names of two of them. It is between these two sheets ("FRONT"
and "END") that I wish to loop and paste data into.


Dim oSh as worksheet
Dim bFirstPassed as Boolean
For Each oSh in Thisworkbook.Worksheets
If bFirstPassed Then
'Do your thing!
End If
If oSh.Name="FRONT" Then bFirstPassed=True
If oSh.Name="END" Then Exit For
Next

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Hi, if I understand you, each sheet between "Front" and "End" you wish
to insert some data.

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "Front" OR WS.Name < "End"
'Code to insert data
End If
Next

Let me know if this is not what you were aiming to do. HTH--Lonnie M.

P.S. I left out the 'Then' at the end of the 'IF' statement. Sorry,
about that.

Hi Lonnie,

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "Front" OR WS.Name < "End"
'Code to insert data
End If
Next


This will add the info to any sheet, because

1. the If clause always yields true (needs to be AND, not OR)
2. Sheets before and after Front and end are also accepted.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Hi Jan and Lonnie,

Thanks for the input!! I will go with Jan's approach, as there are a number
of sheets at the front of the workbook that I do not wish to paste data into,
and as he saya in this posting, the IF statement will add info into those
sheets. I'll let you know how I get on.

Thanks again,

Ewan.

"Jan Karel Pieterse" wrote:

Hi Lonnie,

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "Front" OR WS.Name < "End"
'Code to insert data
End If
Next


This will add the info to any sheet, because

1. the If clause always yields true (needs to be AND, not OR)
2. Sheets before and after Front and end are also accepted.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

In the end I've used a different solution:

As the workbook is user defined in size and format, I have used the user
input sheet to determine which sheets, and which column of the sheets to
update thus:

Dim CELL As Range, RNG as Range
With Worksheets ("User Input Setup Sheet")
Set RNG = .Range(.Range("C3:C52"), .Range("C3:C52").End(xlDown))
End With

Select Case Sheets ("User Choose Column Sheet").Range("B3").Value
Case "1":
Sheets ("Data to copy").Range("D7:D761").Copy
For Each CELL In RNG
If CELL < "" Then
Sheets (CELL.Value).Activate
Range ("D7").Activate
Activesheet.Paste
End If
Next
Case "2":
etc etc etc

Thanks again for your input.

Ewan.

"ewan7279" wrote:

Hi Jan and Lonnie,

Thanks for the input!! I will go with Jan's approach, as there are a number
of sheets at the front of the workbook that I do not wish to paste data into,
and as he saya in this posting, the IF statement will add info into those
sheets. I'll let you know how I get on.

Thanks again,

Ewan.

"Jan Karel Pieterse" wrote:

Hi Lonnie,

Dim WS As Worksheet
For Each WS In Worksheets
If WS.Name < "Front" OR WS.Name < "End"
'Code to insert data
End If
Next


This will add the info to any sheet, because

1. the If clause always yields true (needs to be AND, not OR)
2. Sheets before and after Front and end are also accepted.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Try something like

Dim Ndx As Long
For Ndx = Worksheets("Front").Index To Worksheets("End").Index
' do something with Worksheets(Ndx)
Next Ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"ewan7279" wrote in message
...
Hi,

Can anyone suggest how I can loop between two sheets within a
workbook
please? I do not know how many sheets there will be in the
workbook, but I
do know the names of two of them. It is between these two
sheets ("FRONT"
and "END") that I wish to loop and paste data into.

Any advice greatly appreciated.

Thanks,

Ewan.