| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy all Rows If Column Value X within a Range
Hi,
Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy all Rows If Column Value X within a Range
Baby steps....
You need to increment the rows - you are always copying to the same place. Instead of Sheets("Sheet2").Select Range("A9").Select you could use Sheets("Sheet2").Select Cells(Rows,Count,1).End(xlUp)(2).Select and that won't overwrite existing data. HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hi, Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy all Rows If Column Value X within a Range
Hello Bernie,
thanks but not working. I replaced Sheets("Sheet2").Select Range("A9").Select with Sheets("Sheet2").Select Cells(Rows, Count, 1).End(xlUp)(2).Select But Get error" Wrong Number of arguments or invalid property assignment Do you have any suggestions? thanks Juan "Bernie Deitrick" wrote: Baby steps.... You need to increment the rows - you are always copying to the same place. Instead of Sheets("Sheet2").Select Range("A9").Select you could use Sheets("Sheet2").Select Cells(Rows,Count,1).End(xlUp)(2).Select and that won't overwrite existing data. HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hi, Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy all Rows If Column Value X within a Range
Sorry, Juan! Typo on my part:
Cells(Rows, Count, 1).End(xlUp)(2).Select should be Cells(Rows.Count, 1).End(xlUp)(2).Select HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hello Bernie, thanks but not working. I replaced Sheets("Sheet2").Select Range("A9").Select with Sheets("Sheet2").Select Cells(Rows, Count, 1).End(xlUp)(2).Select But Get error" Wrong Number of arguments or invalid property assignment Do you have any suggestions? thanks Juan "Bernie Deitrick" wrote: Baby steps.... You need to increment the rows - you are always copying to the same place. Instead of Sheets("Sheet2").Select Range("A9").Select you could use Sheets("Sheet2").Select Cells(Rows,Count,1).End(xlUp)(2).Select and that won't overwrite existing data. HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hi, Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy all Rows If Column Value X within a Range
Hello Bernie
well this is not coming out right. It's copying just one row but many times. I want to copy to specific range, since the sheet where I want to copy to has sections. I tried using the following: Sheets("Won").Select Rows(Application.InputBox("Enter Row")).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False but get a runtime error PasteSpecial method of Range class failed. I tesed by using With Selection.Interior .colorindex and that work, so not sure why the Paste doesn't. I will continue and see if I can get this to work. I really appreciate your help. So if you have any other suggestion please advise. again thanks, Juan "Bernie Deitrick" wrote: Sorry, Juan! Typo on my part: Cells(Rows, Count, 1).End(xlUp)(2).Select should be Cells(Rows.Count, 1).End(xlUp)(2).Select HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hello Bernie, thanks but not working. I replaced Sheets("Sheet2").Select Range("A9").Select with Sheets("Sheet2").Select Cells(Rows, Count, 1).End(xlUp)(2).Select But Get error" Wrong Number of arguments or invalid property assignment Do you have any suggestions? thanks Juan "Bernie Deitrick" wrote: Baby steps.... You need to increment the rows - you are always copying to the same place. Instead of Sheets("Sheet2").Select Range("A9").Select you could use Sheets("Sheet2").Select Cells(Rows,Count,1).End(xlUp)(2).Select and that won't overwrite existing data. HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hi, Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy all Rows If Column Value X within a Range
Juan,
We were forgetting to re-select the sheet with the data after the pastespecial. Try it this way: Sub Test() Dim i As Long Dim mySht As Worksheet Set mySht = ActiveSheet For i = 1 To ActiveSheet.UsedRange.Rows.Count If mySht.Cells(i, "U") Like "*X*" Then mySht.Cells(i, "U").EntireRow.Copy Sheets("Sheet2").Select Cells(Rows.Count, 1).End(xlUp)(2).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End If Next i End Sub HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hello Bernie well this is not coming out right. It's copying just one row but many times. I want to copy to specific range, since the sheet where I want to copy to has sections. I tried using the following: Sheets("Won").Select Rows(Application.InputBox("Enter Row")).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False but get a runtime error PasteSpecial method of Range class failed. I tesed by using With Selection.Interior .colorindex and that work, so not sure why the Paste doesn't. I will continue and see if I can get this to work. I really appreciate your help. So if you have any other suggestion please advise. again thanks, Juan "Bernie Deitrick" wrote: Sorry, Juan! Typo on my part: Cells(Rows, Count, 1).End(xlUp)(2).Select should be Cells(Rows.Count, 1).End(xlUp)(2).Select HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hello Bernie, thanks but not working. I replaced Sheets("Sheet2").Select Range("A9").Select with Sheets("Sheet2").Select Cells(Rows, Count, 1).End(xlUp)(2).Select But Get error" Wrong Number of arguments or invalid property assignment Do you have any suggestions? thanks Juan "Bernie Deitrick" wrote: Baby steps.... You need to increment the rows - you are always copying to the same place. Instead of Sheets("Sheet2").Select Range("A9").Select you could use Sheets("Sheet2").Select Cells(Rows,Count,1).End(xlUp)(2).Select and that won't overwrite existing data. HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hi, Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy all Rows If Column Value X within a Range
Hello Bernie,
thanks alot this works. Just one question, let's say if my sheet where I'm copying has sections with data example America uses range (A8:Z19), Europe (A24:Z34) etc, example A8 W2009 a9:a18(Is there a way to copy those rows here)? A19 Total the Cells(Rows.Count, 1).End(xlUp)(2).Select puts rows at end.. Please advise if you have an idea. Really appreciate the time you have put in helping me out. Thanks, Juan "Bernie Deitrick" wrote: Juan, We were forgetting to re-select the sheet with the data after the pastespecial. Try it this way: Sub Test() Dim i As Long Dim mySht As Worksheet Set mySht = ActiveSheet For i = 1 To ActiveSheet.UsedRange.Rows.Count If mySht.Cells(i, "U") Like "*X*" Then mySht.Cells(i, "U").EntireRow.Copy Sheets("Sheet2").Select Cells(Rows.Count, 1).End(xlUp)(2).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End If Next i End Sub HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hello Bernie well this is not coming out right. It's copying just one row but many times. I want to copy to specific range, since the sheet where I want to copy to has sections. I tried using the following: Sheets("Won").Select Rows(Application.InputBox("Enter Row")).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False but get a runtime error PasteSpecial method of Range class failed. I tesed by using With Selection.Interior .colorindex and that work, so not sure why the Paste doesn't. I will continue and see if I can get this to work. I really appreciate your help. So if you have any other suggestion please advise. again thanks, Juan "Bernie Deitrick" wrote: Sorry, Juan! Typo on my part: Cells(Rows, Count, 1).End(xlUp)(2).Select should be Cells(Rows.Count, 1).End(xlUp)(2).Select HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hello Bernie, thanks but not working. I replaced Sheets("Sheet2").Select Range("A9").Select with Sheets("Sheet2").Select Cells(Rows, Count, 1).End(xlUp)(2).Select But Get error" Wrong Number of arguments or invalid property assignment Do you have any suggestions? thanks Juan "Bernie Deitrick" wrote: Baby steps.... You need to increment the rows - you are always copying to the same place. Instead of Sheets("Sheet2").Select Range("A9").Select you could use Sheets("Sheet2").Select Cells(Rows,Count,1).End(xlUp)(2).Select and that won't overwrite existing data. HTH, Bernie MS Excel MVP "Juan" wrote in message ... Hi, Would like to do the following within a range ex A9:Z18: If column U has an X , copy all rows which contain X and paste to sheet 2, else if Column V has X, copy all rows and paste on sheet 3 range A7 Else if Column W has X copy/paste to sheet 4 range A9. I tried something like below: Dim i As Long Range("A9:Z18").Select For i = 1 To ActiveSheet.UsedRange.Rows.Count If Cells(i, "U") Like "*X*" Then Cells(i, "U").EntireRow.Select Selection.Copy Sheets("Sheet2").Select Range("A9").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ElseIf Cells(i, "V") Like "*X*" Then Cells(i, "V").EntireRow.Select Selection.Copy Sheets("Sheet3").Select Range("A7").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False etc. But seems to only copy one row from Column U. Would appreciate any help. Thank you, JUAN |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A macro to copy & paste many rows (a range) to the next column .. | New Users to Excel | |||
| copy a range with known start column to variable end column | Excel Programming | |||
| Copy rows with a specific value in column A | Excel Programming | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| Copy Column headings to Rows | New Users to Excel | |||
Linear Mode
