View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ytayta555 ytayta555 is offline
external usenet poster
 
Posts: 247
Default Some changes in a Macro ..

Thanks to mr. Dave Peterson I have this macro :
(simplified )

__________________________
Sub NEWWAY1()
'

Dim FromWks1 As Worksheet
Dim DestWks As Worksheet
Dim NextRow As Long
Dim myCell As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myRng3 As Range

Workbooks.Open ("D:\WAVE\YTA1.xls")

Set FromWks1 = Workbooks("YTA1.xls").Worksheets("1")
Set DestWks = Workbooks("R1.xls").Worksheets("1")


With FromWks1
Set myRng1 = .Range("BD91:BD65536")
End With

Sheets("1").Select
Range("V91:V7000").Select
Selection.AutoFill Destination:=Range("V91:BB7000"),
Type:=xlFillDefault
Range("V7001:V14000").Select
Selection.AutoFill Destination:=Range("V7001:BB14000"),
Type:=xlFillDefault
Range("V14001:V22000").Select
Selection.AutoFill Destination:=Range("V14001:BB22000"),
Type:=xlFillDefault
Range("V22001:V29000").Select
Selection.AutoFill Destination:=Range("V22001:BB29000"),
Type:=xlFillDefault
Range("V29001:V36000").Select
Selection.AutoFill Destination:=Range("V29001:BB36000"),
Type:=xlFillDefault
Range("V36001:V44000").Select
Selection.AutoFill Destination:=Range("V36001:BB44000"),
Type:=xlFillDefault
Range("V44001:V51000").Select
Selection.AutoFill Destination:=Range("V44001:BB51000"),
Type:=xlFillDefault
Range("V51001:V58000").Select
Selection.AutoFill Destination:=Range("V51001:BB58000"),
Type:=xlFillDefault
Range("V58001:V65536").Select
Selection.AutoFill Destination:=Range("V58001:BB65536"),
Type:=xlFillDefault

For Each myCell In myRng1.Cells
If myCell.Value = 33 Then
With DestWks
NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1
myCell.EntireRow.Copy
.Cells(NextRow, "A").PasteSpecial
Paste:=xlPasteValues
End With
End If
Next myCell
Application.CutCopyMode = False

Workbooks("YTA1.xls").Close SaveChanges:=False

End Sub

This macro work perfect for me , to find a value in column BD , and if
value is =33
to copy entire row and paste it in another workbook .

Now , I have another two (or three) little ,,needs ,, in this
macro :
__________________________________________________ _____
1). First need is the next :
IF value - For Each myCell In myRng1.Cells
If myCell.Value = 33 Then -
then , to select the cell of the *SAME* row ,*BUT* in Column (BB ) ,
and to do an autofill from BB
(in the same row) to the begin of sheet , it means Column (A) ;
{I know that here must be use an resize , a line of code like this :
[myCell or ActiveCell.Select.Selection(Resize(x;y) .Selection.Autofill
Destination .....Column A..],
but I don't know very well to do this to work } .
__________________________________________________ ____
2). My second need is :
For Each myCell In myRng1.Cells
If myCell.Value = 33 Then -
then , to show in the same row , in Column BF the name of the
workbook ;
{I know too , the line of code must to look something like this
[ myCell or ActiveCell.ActiveWorkbook or ThisWorkbook.Name ], but I
don't know very well how to make it to work]} .
__________________________________________________ ____
3). Only and only if it is possible ,my 3-th need is :
For Each myCell In myRng1.Cells
If myCell.Value = 33 Then -
then , in the *SAME* row , in Column BE , to input the function
=ROW() , which return the number of current row .
__________________________________________________ ____

The IF steps , must be in this order :need 1, need 2 and then need
3 ;
And , only after this 3 steps , to copy entire row in this other
workbook with Dave Peterson
line of code , which work perfect :
For Each myCell In myRng1.Cells
If myCell.Value = 33 Then
With DestWks
NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1
myCell.EntireRow.Copy
.Cells(NextRow, "A").PasteSpecial
Paste:=xlPasteValues
End With
End If
Next myCell
__________________________________________________ ____
This 3 IF' s I must them and with my IF must to be in a good arrange
in macro, to action in the steps' order I explained .

Thank you very very much for your time .