Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ; |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 } |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 !) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return while useing CONCATENATE | Excel Discussion (Misc queries) | |||
Useing IF to Paste a Range | Excel Programming | |||
Copy workbook- Validation function | Excel Worksheet Functions | |||
useing arrows to move | Excel Discussion (Misc queries) | |||
Need Help to Prevent Copy Function Use in a Read-Only Workbook | Excel Worksheet Functions |