USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK

[ytayta555]

Hi

How can I change this macro
If cell.Value <= 32 Then
to activate another workbook
and to copy entire row in this
another workbook ????

Any sugestion is very important
for me

many thanks


Sub Clear_Ranges()

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

 

[Ronald R. Dodge, Jr.]

The second argument of the "Cells" property also needs to be a number, which
would be respective to the Nth column of the worksheet, so for column "BD",
it would be a number of 56, which would be the same as the spreadsheet
formula of:

=COLUMN(BD:BD)

One way to redo the Range object is the following:

Dim wshSource as Worksheet, wshDestination as Worksheet, rng as Range, cell
as Range
Set wshSource = Workbooks("Book1").Worksheets("Sheet1")
Set wshDestination = Workbooks("Book2").Worksheets("Sheet1")
Set rng = wshSource.Range("BD91:BD" &
CStr(wshSource.Range("BD65536").End(xlUp).Row))
For Each cell In rng
If cell.Value <= 32 Then
cell.EntireRow.Copy(wshDestination.Range("A" & CStr(cell.Row))
End If
Next

Make adjustments to this as necessary to fit your needs.
--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Dave Peterson]

cells() will accept either a number or a string as the column argument.

 

[Dave Peterson]

Another version...

Option Explicit
Sub Copy_Ranges()

Dim FromWks As Worksheet
Dim DestWks As Worksheet
Dim NextRow As Long
Dim myCell As Range
Dim myRng As Range

Set FromWks = Workbooks("book1.xls").Worksheets("sheet1")
Set DestWks = Workbooks("Book2.xls").Worksheets("sheet1")

With FromWks
Set myRng = .Range("BD91", .Cells(.Rows.Count, "BD").End(xlUp))
End With

For Each myCell In myRng.Cells
If myCell.Value <= 32 Then
With DestWks
NextRow = .Cells(.Rows.Count, "BD").End(xlUp).Row + 1
myCell.EntireRow.Copy _
Destination:=.Cells(NextRow, "A")
End With
End If
Next myCell

End Sub

 

[ytayta555]

Many thanks , Ronald R. Dodge, Jr.

I try to Make adjustments to your macro to
work for me ;

 

[ytayta555]

WAW ! I'TS INCREDIBLE !!
THANK YOU very much , DAVE PETERSON !
Your macro work perfect for me !
With this macro I 'll make my database
of 231 workbooks 3 size bigger { my problem
was the speed of querry in my database ! }
Thanks a lot again

ONE only last problem I have :
HOW to modify your macro to
copy from ,,,FromWks = Workbooks("book1.xls").Worksheets("sheet1") '''
like VALUES ?
(...there are functions...to copy them like
values !...such as you use ,,Selection.PasteSpecial
Paste:=xlPasteValues! ").
??


God bless kindly and clever boys !

 

[Dave Peterson]

This portion:
myCell.EntireRow.Copy _
Destination:=.Cells(NextRow, "A")

can be replaced with:

myCell.EntireRow.Copy
.cells(nextrow,"A").pastespecial paste:=xlpastevalues

 

[ytayta555]

This portion:
                myCell.EntireRow.Copy _
                    Destination:=.Cells(NextRow, "A")

can be replaced with:

                myCell.EntireRow.Copy
                .cells(nextrow,"A").pastespecial paste:=xlpastevalues

Please , one more question : how can I
do the query in column BD in three steps :
(1)BD91:BD22000 , (2) BD22001:BD44000 ,
(3step)BD44001:BD65536 , such as I
have 3 myRng ? (it's really my last
problem !)

Thank very much for your time

{if you have need I can say you how you can
do 65536 count function -for example- in a
few minutes , with the
references in combinatoric order ..it is very usefull
for who have lotto statistics hobby ...I was look
for resolve this problem many months }

 

[ytayta555]

Please , one more question : how can I

 

[ytayta555]

Please , one more question : how can I

do the query in column BD  in three steps :
(1)BD91:BD22000 ,   (2) BD22001:BD44000 ,
(3step)BD44001:BD65536 , such as I
have 3  myRng   ? (it's really my last
problem !)

I found the resolve for my problem :

With FromWks
Set myRng1 = .Range("BD91:BD22000")
End With ........


.... For Each myCell In myRng1.Cells


THANK YOU very much Dave ; I have no
words to thank you