By user1548751


2012-07-24 12:58:01 8 Comments

I have checked a bunch of different posts and can't seem to find the exact code I am looking for. Also I have never used VBA before so I'm trying to take codes from other posts and input my info for it to work. No luck yet. At work we have a payroll system in Excel. I am trying to search for my name "Clarke, Matthew" and then copy that row and paste it to the workbook I have saved on my desktop "Total hours".

3 comments

@Excel Hero 2015-08-21 02:09:17

I know this is old, but for anyone else searching for how to do this, it can be done in a much more direct fashion:

Public Sub ExportRow()
    Dim v
    Const KEY = "Clarke, Matthew"
    Const WS = "Sheet1"
    Const OUTPUT = "c:\totalhours.xlsx"
    Const OUTPUT_WS = "Sheet1"

    v = ThisWorkbook.Sheets(WS).Evaluate("index(a:xfd,match(""" & KEY & """,a:a,),)")
    With Workbooks.Open(OUTPUT).Sheets(OUTPUT_WS)
        .[1:1].Offset(.[counta(a:a)]) = v
        .Parent.Save: .Parent.Close
    End With
End Sub

@Siddharth Rout 2012-07-24 14:35:52

TRIED AND TESTED

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long '<~~ Not Integer. Might give you error in higher versions of excel
    Dim strSearch As String

    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("yourSheetName")

    strSearch = "Clarke, Matthew"

    With ws1

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> I am assuming that the names are in Col A
        '~~> if not then change A below to whatever column letter
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    '~~> Destination File
    Set wb2 = Application.Workbooks.Open("C:\Sample.xlsx")
    Set ws2 = wb2.Worksheets("Sheet1")

    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        copyFrom.Copy .Rows(lRow)
    End With

    wb2.Save
    wb2.Close
End Sub

SNAPSHOT

enter image description here

@Jon Kelly 2012-07-24 15:19:33

+1 theres a lot of edge cases here I didn't go into

@user1548751 2012-07-27 13:36:09

TY for all the feed back. I am tryng to use that second macro but am now recieving an error message on this line .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*" .... Tells me 'Run time error 1004: Autofilter method of range failed'. Any suggestions?

@Siddharth Rout 2012-07-27 13:37:20

What have you set here? With .Range("A1:A" & lRow) ?

@user1548751 2012-07-27 14:00:45

I had left that the same, than i tried A1:A50 assuming it meant the range from row 1 - 50 in column A. Still no luck.. I just realized that this sheet is protected and now when i try to run the macro on that sheet it says i cant. Is there another way around this? im not sure that these sheets will allow for the use of macros

@Siddharth Rout 2012-07-27 14:44:38

Can I see your file?

@user1548751 2012-07-27 14:51:59

How would i go about sending it or posting it? doubt i can from these computers. Also i ran the macro using a unprotected macro-enabled file and it did work. when i try to use the same macro on the other files that are protected i get an error that i need to unprotect it to do it.

@Siddharth Rout 2012-07-27 14:59:37

Do you not have the password for that sheet?

@user1548751 2012-07-27 15:22:01

No i dont its a payroll sheet that we input our information onto and i just wanted to keep my own record on my own sheet by using a macro. i can always just copy and paste instead. TY for all the help though.

@Amatya 2013-11-29 21:30:51

@SiddharthRout How does one also copy the header row?

@Amatya 2013-11-29 21:33:49

@SiddharthRout ok remove the Offset. got it! thx

@DaveU 2013-12-20 04:24:06

Very instructive. However, I'm finding that if there's existing data in the destination sheet, the last row is overwritten with the new data. I'm wondering if lRow = .Cells.Find(What:="*", _ ... should be lRow = .Cells.Find(What:="*", _...) + 1. Thanks.

@Jon Kelly 2012-07-24 13:39:20

Expanding on what timrau said in his comment, you can use the AutoFilter function to find the row with your name in it. (Note that I'm assuming you have the source workbook open)

Dim curBook As Workbook
Dim targetBook As Workbook
Dim curSheet As Worksheet
Dim targetSheet As Worksheet
Dim lastRow As Integer

Set curBook = ActiveWorkbook
Set curSheet = curBook.Worksheets("yourSheetName")

'change the Field number to the correct column
curSheet.Cells.AutoFilter Field:=1, Criteria1:="Clarke, Matthew" 

'The Offset is to remove the header row from the copy
curSheet.AutoFilter.Range.Offset(1).Copy  
curSheet.ShowAllData 

Set targetBook = Application.Workbooks.Open "PathTo Total Hours"
Set targetSheet = targetBook.WorkSheet("DestinationSheet")

lastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

targetSheet.Cells(lastRow + 1, 1).PasteSpecial

targetBook.Save
targetBook.Close 

As you can see I put placeholders in for the specific setup of your workbook.

@Siddharth Rout 2012-07-24 14:17:43

ActiveSheet.AutoFilter.Range.Offset(1).Copy This is an incorrect way to do it :) Please see the two links that I posted in the comment.

@Jon Kelly 2012-07-24 14:27:12

@Siddharth I have found that the AutoFilter.Range works fine. SpecialCells(xlCellTypeVisible) should work too, but I've had issues with it returning blank cells as well.

Related Questions

Sponsored Content

7 Answered Questions

[SOLVED] How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 981645 View
  • 1698 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

1 Answered Questions

2 Answered Questions

[SOLVED] Master sheet copy-paste error 1004

2 Answered Questions

[SOLVED] excel vba macro: copy and paste the specific column to another workbook

  • 2017-12-06 16:37:44
  • user3472143
  • 1408 View
  • 1 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] Paste data values - copied a sheet to another sheet

2 Answered Questions

[SOLVED] Excel vba macro to copy cells and paste in another

  • 2016-05-20 20:49:29
  • Josh James
  • 503 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

0 Answered Questions

how to copy specific cells from other excel to another with macro

  • 2016-02-21 12:41:08
  • Iver Renvire
  • 588 View
  • 0 Score
  • 0 Answer
  • Tags:   excel-vba vba excel

2 Answered Questions

[SOLVED] How to copy then paste formulas from one workbook to another with VBA

  • 2015-05-12 15:08:41
  • user2962699
  • 6856 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] Copy row to another sheet in excel using VBA

  • 2012-07-09 17:51:37
  • Justin
  • 22201 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

Sponsored Content