wanna copy and paste rows

January 1, 2012 at 15:15:29
Specs: Windows 7
Hi,

I wanna copy rows from sheet 1 to sheet 2 for particular name.. e.g. want to copy all rows from sheet 1 having name of Jack and past those rows into sheet 2


See More: wanna copy and paste rows

Report •

#1
January 3, 2012 at 08:17:45
Use the sorting tool.. I'm not sure if your using Excel or Access, but in each of these programs in an icon that has a A/Z and an up and down arrow, this is your sorting. It will sort based on numerical and alphabetical order, starting with 0 and ending with Z. Use this function and you will see all the rows that pertain to your particular name.

Report •

#2
January 14, 2012 at 16:32:34
thanx for the reply, i'm using ms excel....but i need to macros programming for this...i'm doin manually from past 2 months...i wanna apply macros now..

Report •

#3
January 14, 2012 at 19:17:38
We would need to know what column(s) contain the name Jack before we could offer any coding suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 15, 2012 at 04:14:44
Paste the following code in Sheet1 Module. I assume that Column A contains the name Jack. Press Alt+F8 then Enter

Sub CopyJack()
Application.ScreenUpdating = False
Dim lastRow, nxtRow As Integer
Dim sht2_Row As Integer
'Find length of Column A
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Loop through Column A
   For nxtRow = 1 To lastRow
'Check for Jack, Copy-PasteSpecial: Values
   If Cells(nxtRow, "A") = "Jack" Then
    sht2_Row = sht2_Row + 1
    Cells(nxtRow, "A").EntireRow.Copy
    Sheets("Sheet2").Cells(sht2_Row, 1).PasteSpecial Paste:=xlPasteValues
   End If
   Next
End Sub


Report •

#5
January 15, 2012 at 08:14:23
Another option is to use the .Find method instead of checking every cell for the value "Jack".

In addition, using sht2_Row = sht2_Row + 1 could be problematic since sht2_Row will be 0 each time the code is run, which could result in data being overwritten. This could be an issue if there are column headings.

While both methods certainly work, the best method would depend on the OP's needs.

Here is another Macro option, again assuming that "Jack" resides in Column A:

Option Explicit
Sub CopyJack_v2()
Application.ScreenUpdating = False
Dim sht1_lastRow, nxt_sht2_Row As Integer
Dim j As Range, firstAddress As String
'Find length of Column A
   sht1_lastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
'Check for Jack in Column A
      With Sheets(1).Columns(1)
         Set j = .Find("Jack", lookat:=xlWhole)
'Copy row to Sheet2 if Jack is found
           If Not j Is Nothing Then
             firstAddress = j.Address
              Do
                nxt_sht2_Row = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
                  j.EntireRow.Copy Destination:=Sheets(2).Cells(nxt_sht2_Row, "A")
'Try to Find Jack again
                    Set j = .FindNext(j)
'Loop if found
                      Loop While Not j Is Nothing And j.Address <> firstAddress
          End If
      End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question