USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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
"ytayta555" wrote in message
...
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

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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

"Ronald R. Dodge, Jr." wrote:

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
"ytayta555" wrote in message
...
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

--

Dave Peterson

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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 wrote:

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

--

Dave Peterson

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

Many thanks , Ronald R. Dodge, Jr.

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

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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 !

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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

can be replaced with:

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



ytayta555 wrote:

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

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

On 12 Mar, 04:44, Dave Peterson wrote:
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 }

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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 !)

USEING IF FUNCTION TO COPY IN ANOTHER WORKBOOK
 

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