Search a Column for a specific Value and Place in Another sheet

[trevor2524]

Hello, I need help creating the following part of a macro. I have information that will be on sheet 1 in the following format:
Column A,Column B,Column C,Column D,Column E
File Number,Field Name,Changed From,Changed To, Modified By

I will have up to 14 sheets one for each person making a modification.
So Sheet 2 = John Doe
Sheet 3 = Jane Smith and so on....

On sheet 1 I would Like a Macro to start by searching Column E. For John Doe and if it comes along a John Doe it will then copy that row into the first empty row in column A on the sheet that corresponds with that name. So for John Smith it would be Sheet 2 and for Jane Smith it will copy to the first available row in Column A in Sheet 3. I have another step after this but this portion needs to be done before anything else and I'm just trying to get the basic of the macro started. Thanks for your help.

[SamT]

This assumes that the Names in Column E of Sheet1 are identical to the Tab names of the other sheets.

Sub SamT()
Const NumCols As Long = 4 'Number of columns to copy (A,B,C,D)
Const MainSheet As String = "Sheet1" 'Change to suit
Dim NextA As Range 'Next Empty Cell in Column A of given Sheet
Dim LastE As Range 'The last used Cell of a given column (E in this case)
Dim Cel As Range 'Used in loop

  'Cells(Rows.Count, n) is the bottommost cell in column number "n"
  'End(xlUP) is the same as selecting any Cell and pressing Ctrl+Up Arrow
  Set LastE = Sheets(MainSheet).Cells(Rows.Count, 5).End(xlUp)
  For Each Cel In Sheets(MainSheet).Range(Range("E1"), LastCell)
    If Trim(Cel.Text) = "" Then GoTo CelNext 'In case empty row before end
    Set NextA = Sheets(Cel.Text).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Range("A" & Cel.Row).Resize(1, NumCols).Copy 'Resize range An to An:Dn
    Sheets(Cel.Text).Range(NextA).PasteSpecial 'with no params, pastes all
CelNext:
  Next Cel
End Sub

[trevor2524]

Thanks SamT for the response. I keep getting a run-time error '1004'. I have the Column E Cell 2 highlighted when i try and run the macro but unfortunately it won't go into the first step when I try to step-into the macro. Thanks for your help.

[SamT]

Trevor,

Try putting the code in a module.

It should run from the VBa Editor, (VBE,) with F5 or F8. It should also run from the Macro Menu. It should ignore any selecting on any sheet.

Let me know what changes you made to it to conform it to your workbook.

I don't understand which line of code you mean by "First step."



According to Microsoft, the 1004 error can occur when the data is filtered and when pasting an entire Row of more than about 2500 rows.

Other research indicates that it can happen if you are trying to paste links, ie a cell formula is similar to "=A1." however, there are still other situations where a 1004 can occur.

If checking the above doesn't solve the issue, can you upload an example file for us to experiment with? Be sure to replace any personal or proprietary information with dummy info.