Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with copying selected row to new sheet
With the code below I have the following problem. Please help If the source sheet contains data on B2:B5, and "X"s on A2:A5. Data on B2:B5 should get copied since they all have an "X" on column A. But after running the macro only the data from B5 get copied. Now, If I also include data on C2:C5, then everything get copied. thanks. This is what i am using: Sub Priority() Application.ScreenUpdating = False Worksheets("Sheet2").Select For Each r In Worksheets("Sheet2").UsedRange.Rows n = r.Row If Worksheets("Sheet2").Cells(n, 1) = "X" Then Worksheets("Sheet2").Range(Cells(n, 2), Cells(n, 7)).Copy _ Destination:=Worksheets("Sheet1").Range("B65536"). End(xlUp).Offset(1, -1) Else End If Next r Worksheets("Sheet2").Columns("A").Replace What:="X", Replacement:="*", _ SearchOrder:=xlByColumns, MatchCase:=True Application.CutCopyMode = True Application.ScreenUpdating = True End Sub -- ATOMICLUIS ------------------------------------------------------------------------ ATOMICLUIS's Profile: http://www.excelforum.com/member.php...o&userid=28773 View this thread: http://www.excelforum.com/showthread...hreadid=484721 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with copying selected row to new sheet
Point 1:
It's not that the other values arn't getting copied over, it's that they get pasted over top of each other. The expression: Worksheets("Sheet1").Range("B65536").End(xlUp) doesn't increment if nothing gets pasted to column B (Sheet1) which is the case if there's nothing in column C (Sheet2) since the code pastes values from column C (Sheet2) to Column B (Sheet1). Point 2: I prefer not to copy and paste if only values are being transfered. I rewrote the code excluding the copy and paste routine. Minimal testing and done in a hurry:- Sub Priority() Dim rng As Range, c As Range Dim i As Long Dim ws As Worksheet, ws2 As Worksheet Set ws = Sheets("Sheet1") Set ws2 = Sheets("Sheet2") With Application Set rng = .Intersect(ws2.UsedRange, ws2.Columns(1)) i = 2 .ScreenUpdating = False For Each c In rng.Cells If c.Value = "X" Then c.Value = "*" ws.Cells(i, 1).Resize(1, 6).Value = c(1, 2).Resize(1, 6).Value i = i + 1 End If Next .ScreenUpdating = True End With End Sub Regards, Greg "ATOMICLUIS" wrote: With the code below I have the following problem. Please help If the source sheet contains data on B2:B5, and "X"s on A2:A5. Data on B2:B5 should get copied since they all have an "X" on column A. But after running the macro only the data from B5 get copied. Now, If I also include data on C2:C5, then everything get copied. thanks. This is what i am using: Sub Priority() Application.ScreenUpdating = False Worksheets("Sheet2").Select For Each r In Worksheets("Sheet2").UsedRange.Rows n = r.Row If Worksheets("Sheet2").Cells(n, 1) = "X" Then Worksheets("Sheet2").Range(Cells(n, 2), Cells(n, 7)).Copy _ Destination:=Worksheets("Sheet1").Range("B65536"). End(xlUp).Offset(1, -1) Else End If Next r Worksheets("Sheet2").Columns("A").Replace What:="X", Replacement:="*", _ SearchOrder:=xlByColumns, MatchCase:=True Application.CutCopyMode = True Application.ScreenUpdating = True End Sub -- ATOMICLUIS ------------------------------------------------------------------------ ATOMICLUIS's Profile: http://www.excelforum.com/member.php...o&userid=28773 View this thread: http://www.excelforum.com/showthread...hreadid=484721 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copying only selected data from Sheet 1 to Sheet 2 | Excel Worksheet Functions | |||
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. | Excel Worksheet Functions | |||
Copying selected rows to another sheet | Excel Worksheet Functions | |||
copying selected cells to another sheet | Excel Programming | |||
Copying a selected used range | Excel Programming |