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