This Works just fine as written it will copy to the current sheet
just fine only I need it to Find the Last Sheet IN Workbook ("Master1")
and copy to Range("J1:J140") of that sheet every time i run this This
Macro The Sheet Name will Be different Thats why I need to have this
routine find the Name.
Im running windows xp sp2 with excel 2003
I will certanly be grateful to anyone that will bail me out as im
firmly stuck Thanks Boyd

Public Sub FindTheYeLLeR()
Dim r As Range
Dim i As Variant
i = i
i = i + 1
For Each i In Range("E244:E1000")
If i.Value >= 1 Then
i.Offset(o, -2).Copy Destination:=i.Offset(0, 7)
'Exit For
End If
i = i + 1
Next i
End Sub

Maybe...

Option explicit
Public Sub FindTheYeLLeR()
Dim myCell As Range
dim wks as worksheet

with activeworkbook
set wks = .worksheets(.worksheets.count)
end with

For Each mycell In wks.Range("E244:E1000").cells
If mycell.Value >= 1 Then
mycell.Offset(0, -2).Copy _
Destination:=mycell.Offset(0, 7)
End If
Next mycell
End Sub

Oke Doke wrote:
>
> This Works just fine as written it will copy to the current sheet
> just fine only I need it to Find the Last Sheet IN Workbook ("Master1")
> and copy to Range("J1:J140") of that sheet every time i run this This
> Macro The Sheet Name will Be different Thats why I need to have this
> routine find the Name.
> Im running windows xp sp2 with excel 2003
> I will certanly be grateful to anyone that will bail me out as im
> firmly stuck Thanks Boyd
>
> Public Sub FindTheYeLLeR()
> Dim r As Range
> Dim i As Variant
> i = i
> i = i + 1
> For Each i In Range("E244:E1000")
> If i.Value >= 1 Then
> i.Offset(o, -2).Copy Destination:=i.Offset(0, 7)
> 'Exit For
> End If
> i = i + 1
> Next i
> End Sub

--

Dave Peterson

Hi Boyd

I think you have a typo in your macro, you are using i as counter and
range in 'For Each....' statement and further. Also you have a typo in
the first offset statement. You do not need the name of last sheet
just the index number. I think this is what you need:

Public Sub FindTheYeLLeR()
Dim cell As Range
Dim i As Long
Dim wb As Workbook
Dim DestSh As Worksheet

Set wb = Workbooks("Master1.xls")'<===Notice file type
Set DestSh = wb.Worksheets(Sheets.Count)
For Each cell In Range("E244:E1000")
i = i + 1
If cell.Value >= 1 Then
r.Offset(0, -2).Copy Destination:=DestSh.cell.Offset(0, 7)
'Exit For
End If
Next cell
End Sub


Regards,
Per

On 18 Nov., 02:31, Oke Doke <Beechn...@hotmail.com> wrote:
> This Works just fine as written *it will copy to the current sheet
> just fine only I need it to Find the Last Sheet IN Workbook ("Master1")
> and copy to *Range("J1:J140") of that sheet every time i run this This
> Macro The Sheet Name will Be different Thats why I need to have this
> routine find the Name.
> * Im running windows xp sp2 with excel 2003
> I will certanly be grateful to anyone that will bail me out as im
> firmly stuck *Thanks Boyd
>
> Public Sub FindTheYeLLeR()
> Dim r As Range
> Dim i As Variant
> i = i
> i = i + 1
> For Each i In Range("E244:E1000")
> * *If i.Value >= 1 Then
> * * * i.Offset(o, -2).Copy Destination:=i.Offset(0, 7)
> * * * 'Exit For
> * * * *End If
> i = i + 1
> Next i
> End Sub

Just a correction, use this code:

Public Sub FindTheYeLLeR()
Dim cell As Range
Dim i As Long
Dim wb As Workbook
Dim DestSh As Worksheet

Set wb = Workbooks("Master1.xls")
Set DestSh = wb.Worksheets(Sheets.Count)
For Each cell In Range("E244:E1000").Cells
If cell.Value >= 1 Then
i = i + 1
cell.Offset(0, -2).Copy Destination:=DestSh.Range("J" & i)
'Exit For
End If
Next
End Sub

Regards,
Per


On 18 Nov., 03:25, Per Jessen <perjesse...@hotmail.com> wrote:
> Hi Boyd
>
> I think you have a typo in your macro, you are using i as counter and
> range in 'For Each....' statement and further. Also you have a typo in
> the first offset statement. You do not need the name of last sheet
> just the index number. I think this is what you need:
>
> Public Sub FindTheYeLLeR()
> Dim cell As Range
> Dim i As Long
> Dim wb As Workbook
> Dim DestSh As Worksheet
>
> Set wb = Workbooks("Master1.xls")'<===Notice file type
> Set DestSh = wb.Worksheets(Sheets.Count)
> For Each cell In Range("E244:E1000")
> * * i = i + 1
> * * If cell.Value >= 1 Then
> * * * * r.Offset(0, -2).Copy Destination:=DestSh.cell.Offset(0,7)
> * * * * 'Exit For
> * * End If
> Next cell
> End Sub
>
> Regards,
> Per
>
> On 18 Nov., 02:31, Oke Doke <Beechn...@hotmail.com> wrote:
>
>
>
> > This Works just fine as written *it will copy to the current sheet
> > just fine only I need it to Find the Last Sheet IN Workbook ("Master1")
> > and copy to *Range("J1:J140") of that sheet every time i run this This
> > Macro The Sheet Name will Be different Thats why I need to have this
> > routine find the Name.
> > * Im running windows xp sp2 with excel 2003
> > I will certanly be grateful to anyone that will bail me out as im
> > firmly stuck *Thanks Boyd
>
> > Public Sub FindTheYeLLeR()
> > Dim r As Range
> > Dim i As Variant
> > i = i
> > i = i + 1
> > For Each i In Range("E244:E1000")
> > * *If i.Value >= 1 Then
> > * * * i.Offset(o, -2).Copy Destination:=i.Offset(0, 7)
> > * * * 'Exit For
> > * * * *End If
> > i = i + 1
> > Next i
> > End Sub- Skjul tekst i anførselstegn -
>
> - Vis tekst i anførselstegn -

use something like this:

With ActiveWorkbook
With .Worksheets(.Worksheets.Count)
For Each r In .Range("E244:E1000").Cells
If r.Value >= 1 Then
'<put the rest of your code here>
End If
Next
End With
End With


Don't know why you are using i as a variant and not using r. r is the
correct one to use because your For loop is referencing a range (a single
cell)
"Oke Doke" <(E-Mail Removed)> wrote in message
news:037994fb-7597-4242-8617-(E-Mail Removed)...
> This Works just fine as written it will copy to the current sheet
> just fine only I need it to Find the Last Sheet IN Workbook ("Master1")
> and copy to Range("J1:J140") of that sheet every time i run this This
> Macro The Sheet Name will Be different Thats why I need to have this
> routine find the Name.
> Im running windows xp sp2 with excel 2003
> I will certanly be grateful to anyone that will bail me out as im
> firmly stuck Thanks Boyd
>
> Public Sub FindTheYeLLeR()
> Dim r As Range
> Dim i As Variant
> i = i
> i = i + 1
> For Each i In Range("E244:E1000")
> If i.Value >= 1 Then
> i.Offset(o, -2).Copy Destination:=i.Offset(0, 7)
> 'Exit For
> End If
> i = i + 1
> Next i
> End Sub