Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |