Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows
Hello
I have a spreadsheet that tracks the progress of projects. is there a way to copy only the rows that contains my name (Column G), to a new sheetthat I would have open? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows
The following macro will work efficiently so long as their are not more
than, say, 300 to 400 rows with your name in Column G (if you could have more, I should be able to modify the code to handle it); change the values in the five Const statements to reflect your actual setup... Sub CopyMe() Dim R As Range, M As Range Const MyName As String = "Rick" Const NameColumn As String = "G" Const SourceSheet As String = "Sheet2" Const DestinationCell As String = "A1" Const DestinationSheet As String = "Sheet4" With Worksheets(SourceSheet) For Each R In Intersect(.UsedRange, .Columns(NameColumn)) If R.Value = MyName Then If M Is Nothing Then Set M = R.EntireRow Else Set M = Union(M, R.EntireRow) End If End If Next End With M.Copy Worksheets(DestinationSheet).Range(DestinationCell ) End Sub -- Rick (MVP - Excel) "Novice Lee" wrote in message ... Hello I have a spreadsheet that tracks the progress of projects. is there a way to copy only the rows that contains my name (Column G), to a new sheetthat I would have open? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows
Sub COUNTme()
Dim row1 As Integer row1 = 1 For count1 = 1 To 100 Cells(row1, 6).Select If Selection = "MYNAME" Then Call transfer row1 = row1 + 1 Next End Sub Sub transfer() Selection.EntireRow.Copy Workbooks.otherworkbook.active Sheets("MYNAME").Select Range("A1").Select Insert.Row Paste originalworkbook.Select End Sub "Novice Lee" wrote: Hello I have a spreadsheet that tracks the progress of projects. is there a way to copy only the rows that contains my name (Column G), to a new sheetthat I would have open? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows
Try code like the following. Change the lines marked with <<< to the
appropriate values. SourceWS is the worksheet with the data you want to copy. DestWS is the worksheet to which the records will be copied. DestCell is the first cell where the records are to be copied. StartRow is the row on SoureWS where the data starts. YourName is your name, to be tested in column G. Sub AAA() Dim SourceWS As Worksheet Dim DestWS As Worksheet Dim DestCell As Range Dim StartRow As Long Dim EndRow As Long Dim RowNdx As Long Dim YourName As String Set SourceWS = Worksheets("Sheet1") '<<< CHANGE Set DestWS = Worksheets("Sheet2") '<<< CHANGE Set DestCell = DestWS.Range("A1") '<<< CHANGE StartRow = 1 '<<< CHANGE YourName = "John Smith" With SourceWS EndRow = .Cells(.Rows.Count, "G").End(xlUp).Row For RowNdx = StartRow To EndRow If StrComp(.Cells(RowNdx, "G").Value, _ YourName, vbTextCompare) = 0 Then .Cells(RowNdx, "A").EntireRow.Copy DestCell Set DestCell = DestCell(2, 1) End If Next RowNdx End With End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 7 Apr 2009 06:49:01 -0700, Novice Lee wrote: Hello I have a spreadsheet that tracks the progress of projects. is there a way to copy only the rows that contains my name (Column G), to a new sheetthat I would have open? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows
Since we are copying entire rows, perhaps it would be better to specify a
DestinationRow rather than a DestinationCell. Use this modified macro instead of the one I posted earlier... Sub CopyMe() Dim R As Range, M As Range Const MyName As String = "Rick" Const NameColumn As String = "G" Const SourceSheet As String = "Sheet2" Const DestinationRow As Long = 1 Const DestinationSheet As String = "Sheet4" With Worksheets(SourceSheet) For Each R In Intersect(.UsedRange, .Columns(NameColumn)) If R.Value = MyName Then If M Is Nothing Then Set M = R.EntireRow Else Set M = Union(M, R.EntireRow) End If End If Next End With M.Copy Worksheets(DestinationSheet).Rows(DestinationRow) End Sub -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... The following macro will work efficiently so long as their are not more than, say, 300 to 400 rows with your name in Column G (if you could have more, I should be able to modify the code to handle it); change the values in the five Const statements to reflect your actual setup... Sub CopyMe() Dim R As Range, M As Range Const MyName As String = "Rick" Const NameColumn As String = "G" Const SourceSheet As String = "Sheet2" Const DestinationCell As String = "A1" Const DestinationSheet As String = "Sheet4" With Worksheets(SourceSheet) For Each R In Intersect(.UsedRange, .Columns(NameColumn)) If R.Value = MyName Then If M Is Nothing Then Set M = R.EntireRow Else Set M = Union(M, R.EntireRow) End If End If Next End With M.Copy Worksheets(DestinationSheet).Range(DestinationCell ) End Sub -- Rick (MVP - Excel) "Novice Lee" wrote in message ... Hello I have a spreadsheet that tracks the progress of projects. is there a way to copy only the rows that contains my name (Column G), to a new sheetthat I would have open? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy rows
thanks to all
I will try all when I get home "Novice Lee" wrote: Hello I have a spreadsheet that tracks the progress of projects. is there a way to copy only the rows that contains my name (Column G), to a new sheetthat I would have open? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Find all rows of a color and copy those rows to a new worksheet | Excel Programming | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions | |||
Copy Rows and insert these rows before a page break | Excel Programming |