can anyone help me finish this

  • Thread starter Thread starter Oke Doke
  • Start date Start date
O

Oke Doke

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
 
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
 
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
 
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)
 
Back
Top