Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COPY...........(need a little change in a macro)
HI ALL
Here is a macro which delete a ,,specific range,, in every Row in the same workbook, if the value from BD91 to BD 65536 <=32 ; here is the macro : Sub Clear_Ranges() ' Macro recorded 3/5/2008 by Tim Dim cell As Range, rng As Range Set rng = Range(Cells(91, "BD"), Cells(Rows.Count, "BD").End(xlUp)) For Each cell In rng If cell.Value <= 32 Then Cells(cell.Row, "C").Select Selection.Resize(1, 52).Select Selection.ClearContents End If Next End Sub It works perfect , but it take {in my database of 231 workbooks} a looong ... time !! IF this macro find in the same range {BD91 to BD65536} the Value =33 , and then Copy the EntireRow in another Wo rkbook ,the time shall become dramatically shorter !! I TRIED THIS : Sub Clear_Ranges() ' Macro recorded 3/5/2008 by Tim Dim cell As Range, rng As Range Set rng = Range(Cells(91, "BD"), Cells(Rows.Count, "BD").End(xlUp)) For Each cell In rng If cell.Value = 33 Then '...... {here maybe must be EntireRow .Select???} Cells(cell.Row, "C").Select Selection.Resize(1, 52).Select Selection.Copy Windows("R1.xls").Activate ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Windows("YTA1.xls").Activate End If Next End Sub .......but it didn't work ;{particular informations : both workbooks named YTA1 and R1 are open ;R1 is the basic workbook which get the results I need from the others workbooks;in this case I want to select and copy in wb R1 the EntireRow !!!!!!! of the YTA1 wb if the value in this Rows in Column BD is =33 } ; FOR CONCLUSIONS : ,,IF VALUE IN WB,,YTA1,,=33 THEN COPY EN- TIRE ROW IN ANOTHER WB ,,R1,, " Please help, Respectfully |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COPY...........(need a little change in a macro)
Perhaps the Selection is slowing you down. replace:
Cells(cell.Row, "C").Select Selection.Resize(1, 52).Select Selection.ClearContents with: Cells(cell.Row, "C").Resize(1, 52).ClearContents This may improve the speed. -- Gary''s Student - gsnu2007e "ytayta555" wrote: HI ALL Here is a macro which delete a ,,specific range,, in every Row in the same workbook, if the value from BD91 to BD 65536 <=32 ; here is the macro : Sub Clear_Ranges() ' Macro recorded 3/5/2008 by Tim Dim cell As Range, rng As Range Set rng = Range(Cells(91, "BD"), Cells(Rows.Count, "BD").End(xlUp)) For Each cell In rng If cell.Value <= 32 Then Cells(cell.Row, "C").Select Selection.Resize(1, 52).Select Selection.ClearContents End If Next End Sub It works perfect , but it take {in my database of 231 workbooks} a looong ... time !! IF this macro find in the same range {BD91 to BD65536} the Value =33 , and then Copy the EntireRow in another Wo rkbook ,the time shall become dramatically shorter !! I TRIED THIS : Sub Clear_Ranges() ' Macro recorded 3/5/2008 by Tim Dim cell As Range, rng As Range Set rng = Range(Cells(91, "BD"), Cells(Rows.Count, "BD").End(xlUp)) For Each cell In rng If cell.Value = 33 Then '...... {here maybe must be EntireRow .Select???} Cells(cell.Row, "C").Select Selection.Resize(1, 52).Select Selection.Copy Windows("R1.xls").Activate ActiveCell.Offset(1, 0).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Windows("YTA1.xls").Activate End If Next End Sub .......but it didn't work ;{particular informations : both workbooks named YTA1 and R1 are open ;R1 is the basic workbook which get the results I need from the others workbooks;in this case I want to select and copy in wb R1 the EntireRow !!!!!!! of the YTA1 wb if the value in this Rows in Column BD is =33 } ; FOR CONCLUSIONS : ,,IF VALUE IN WB,,YTA1,,=33 THEN COPY EN- TIRE ROW IN ANOTHER WB ,,R1,, " Please help, Respectfully |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COPY...........(need a little change in a macro)
thank very much Gary , you help me
very much with topic ,,Perfect Macro Combinations ,, . I think I do not make meself clear what I need now ! I want to find the value =33 and copy entire row in the other work-book ; the macro work very well for loop and search the value BD=33 in ,,YTA1.xls,, !! BUT I don,t know how to modify the macro to activate the ,,R1.xls,, and then copy there entire row(x) of ,,YTA1.xls,, 's column BD(x) =33 Respectfully to you |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COPY...........(need a little change in a macro)
have somebody any sugestion here ?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro that will change the font of a cell if i change a value | Excel Discussion (Misc queries) | |||
VBA, change 'copy line' into 'copy range' | Excel Programming | |||
Macro to find, copy, and paste until value change | Excel Worksheet Functions | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming |