Typo:

Sub ABCD()
Dim sh As Worksheet
Dim i As Long
Dim sh1 As Worksheet
Set sh = ActiveSheet
i = 6 ' column F
For Each sh1 In Worksheets
If InStr(1, sh1.Name, "detail", vbTextCompare) Then
sh1.Range("B9:B1000").Copy Destination:= _
sh.Cells(9, i)
i = i + 1
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Assume the copies will be moving to the right starting with Column F as
you
copy them:

Dim sh as Worksheet
Dim i as Long
Dim sh1 as Worksheet
set sh = Activesheet
i = 6 ' column F
for each sh1 in Worksheets
if Instr(1,sh.Name,"detail",vbTextCompare) then
sh1.Range("B9:B1000").Copy Destination:= _
sh.Cells(9,1)
i = i + 1
End if
Next

--
Regards,
Tom Ogilvy

"KimberlyC" wrote in message
...
Hi
I need to run code that will copy data from cells B9:B1000 located on
the
third worksheet Before (to the left) the activeworksheet ...and
then...paste
the data into cells F9:F1000 of the Active worksheet.
In case this helps.......
The name of this third worksheet is "Details"..however, more sets of
these
worksheets can be added (via macro) to this template file and I will
need
to
run this code from them as well.. for ex:.. when the second set of
worksheets are added..the third worksheet before the activeworksheet
that
is
running this code will be named Details (2) ...and so on..as more sets
are
added.

I would use formulas to do this..but I need to be able to determine the
last
entry in column F of the active worksheet..and formulas count even if
the
results = nothing...and there could be data in cells B9:B20 or
B9:B1000..
it
just depends..on that data entered...so the formulas will interfear with
this...

Any help is greatly appreciated...
Thanks in advance,
Kimberly
.