Copy data from one worksheet to another worksheet by macro/vba

[punnu07]

Hi,

I have a problem hope anyone can help me. Actually, I have an excel sheet in which first worksheet contains 113 columns. On a daily basis, we need to fill some data on the second worksheet. So what I want is that data from worksheet first automatically copied to second worksheet basis on a particular criteria. For example, if I enter the person's name on the second worksheet say in cell D1 the macro should look for that name in first worksheet and then copied the data of say A1 to K1 to same cell range of second worksheet. Same will repeat for second row and so on.

 

[alansidman]

crosspost: Copy data from one worksheet to another worksheet by macro/vba

 

[hiker95]

A message to forum cross posters.

Please read this:
Excelguru Help Site - A message to forum cross posters

 

[punnu07]

Bump

 

[Michael M]

Look at using a VLOOKUP formula on Sheet 2 to retrieve the data from sheet 1

 

[pmich]

I read your reply in the following page for cross posting.
Copy data from one worksheet to another worksheet by macro/vba
Try this:

Private Sub cmdFindInOneSheetCopyInAnother_Click()
 'http://www.mrexcel.com/forum/excel-questions/771665-cut-paste-other-sheet.html
 Dim SrchVal As Range
 Dim FoundRow As Long
 Dim SelRange As Range
 Dim LastRecNum As Long
 Dim NowRowNum As Long
 Dim LstRowNmbr As Long
 Dim SrchNam As Range
 Sheets("SrchMaster").Select
 LstRowNmbr = Cells(Rows.Count, "D").End(xlUp).Row  'to go to last data cell inspite of blank cells
 Sheets("SourceNames").Select
 LastRecNum = Cells(Rows.Count, "D").End(xlUp).Row
 Range("D2").Select
 NowRowNum = ActiveCell.Row
 Do While NowRowNum <= LastRecNum
  Set SrchVal = Cells(NowRowNum, 4)    'value in Active Row 2 & Col 4
  Sheets("SrchMaster").Select
  Set SrchNam = Range("D:D").Find(SrchVal, , xlValues, xlWhole)
  If SrchNam Is Nothing Then 'if name is Benn, & no record is matching
  Else 'SrchNam Is Nothing 'if name is Benn, _
    record found may be Benny. So, moving pointer to that record.
   With SrchNam
    .Activate 'Pointer moved to that record for checking.
   End With
   If SrchNam <> SrchVal Then 'if Benny is found and Benn is Not Found
   ElseIf SrchNam = SrchVal Then 'if Benn is found.
    FoundRow = WorksheetFunction.Match(SrchVal, Range("D1" & ":D" & Trim(Str(LstRowNmbr))), False)
    Range("A" & Trim(Str(FoundRow)) & ":K" & Trim(Str(FoundRow))).Select
    Set SelRange = Selection
    SelRange.Copy Worksheets("SourceNames").Range("A" & Trim(Str(NowRowNum)))
   End If 'Val(SrchNam) <> Val(SrchVal)
  End If 'SrchNam Is Nothing
  Sheets("SourceNames").Select
  NowRowNum = NowRowNum + 1
  Range("D" & Trim(Str(NowRowNum))).Select
 Loop 'NowRowNum <= LastRecNum
 MsgBox "All Rows in SourceNames searched!"
End Sub