Useing IF to Paste a Range
 

HI ALL

I get here {in this community} a very usefull help for me with a
Macro;
I needed a macro to delete a ,,specific range,, in every Row,
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 looonger ... time ,even this macro find in the same range {BD91 to
BD65536} the Value =33 , and then Copy this EntireRow in another Wo
rkbook ; I tried so :


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 other wb
if the value in this Rows in Column BD is =33 }

Respectfully to ALL

Useing IF to Paste a Range
 

[this topic is not the same with ,,Useing IF to Delete a Range "],
here must be done a little change in this macro , but ...how ?..

Useing IF to Paste a Range
 

try this:

Sub Clear_Ranges()

dim calc_mode as long
calc_mode=application.calculation
application.calculation=xlcalculationmanual
Application.ScreenUpdating = False

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").ClearContents
Next cell

application.calculation=calc_mode

End Sub


turning off screenupdating and calculation should make it run dramatically
faster. please don't hijack my thread anymore.

"ytayta555" wrote:

HI ALL

I get here {in this community} a very usefull help for me with a
Macro;
I needed a macro to delete a ,,specific range,, in every Row,
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 looonger ... time ,even this macro find in the same range {BD91 to
BD65536} the Value =33 , and then Copy this EntireRow in another Wo
rkbook ; I tried so :


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 other wb
if the value in this Rows in Column BD is =33 }

Respectfully to ALL

Useing IF to Paste a Range
 

HI ryan

my apologise again ,
thank very much for help ;

the problem is to copy entire row in wbook R1 ,
if you'll have time toread again you'll understand ;
the problem with clear contents is resolved ;
the problem is that Macro must activate wbook
R1 and copy entire row in this wb , then come back
in wbook YTA1 and continue the search ,loop again
the value =33 in BD91:BD65536 ,when find it again
activate wbook R1 and so on ; the problem is to copy and
paste entire row , not to clear contents .

Respectfully