Macro to feed info into other sheets

[damaniam]

Attached I've included a purged version of a spreadsheet I am trying to make. The tabs are named and the following requested are humbly made to give instructions of the desired function of the macro. For the sheet per Agent Page for all rows where K on sheet1=B2 on sheet per agent arrange info into columns a-e on sheet per agent in the following order n a c f m with information from sheet1. Then do this for all rows that where K sheet 1= b2. I know what I want it to do, but I'm not sure of function that could do it or how to utilize the macro recorder to achieve this goal. There will need to be a macro for the last 3 tabs as well. All the macros will involve pulling info from sheet 1 and inserting it into a certain order on the other tabs based upon criteria. If anyone is able to help with my first issue or any of the additional I'd greatly appreciate it.

Thanks,
-D

[SamT]

How are you going to name each "Sheet Per Agent?" While it is possible to write code to search every Sheet's Range("B2") and get the sheet name when there is a match, but it is longer and harder to maintain than matching Sheet1.Range("K) to a sheet's Tab name.

[damaniam]

I will have a sheet per agent so if the sheet name itself help in the searching for information and coding. For example there is 16 agents, each will have a worksheet for them. So for worksheet titled "Smith" I need to have all information relating to Agent Smith listed on "Sheet1" to be pulled in. Thanks for the help.

[SamT]

Try this. In the attachment, I moved the Sales Total formula so it would not interfere with the code. The Code is in Module1. I deleted all other sheets to save bandwidth.

Here is the Code

Option Explicit

'For assistance with these Enumerations see:
' Link deleted in this post
Enum InfoColumns
  infoAddress = 1
  infoLeaseSale = 3
  infoAmount = 6
  infoVolume = 13
  infoClosed = 14
End Enum

Enum AgntColumns
  agntClosed = 1
  agntAddress = 2
  agntLeaseSale = 3
  agntAmount = 4
  infoVolume = 5
End Enum

Sub SamT()
'For assistance with this sub see:
' Link deleted, still in attachment code

Dim infoSheet As Worksheet
Dim infoAgentsList As Range
Dim agntNextRow As Long
Dim listRow As Long
Dim infoRow As Long

  Set infoSheet = Sheets("sheet1")
  With infoSheet
    Set infoAgentsList = .Range("K4:K" & Cells(Rows.Count, 11).End(xlUp).Row)
  End With
  
  With infoAgentsList
    For listRow = 1 To .Rows.Count
      infoRow = i + 3
      'Insert test for Agent Sheet exists here
      With Sheets(infoAgentsList.Cells(i))
        agntNextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        With .Rows(agntNextRow)
          .Cells(agntClosed) = infoSheet.Cells(infoRow, infoClosed)
          .Cells(agntAddress) = infoSheet.Cells(infoRow, infoAddress)
          .Cells(agntLeaseSale) = infoSheet.Cells(infoRow, infoLeaseSale)
          .Cells(agntAmount) = infoSheet.Cells(infoRow, infoAmount)
          .Cells(agntVolume) = infoSheet.Cells(infoRow, infoVolume)
        End With
      End With
    Next listRow
  End With

End Sub

[damaniam]

When I download the attachment and run the macro, I get an error where i is undefined

[SamT]

That means that someone put "Option Explicit" at the top of the page (catches some errors.)

And it means that someone (Who? Me? ) forgot to Dim i As Long.

[damaniam]

I seem to get errors regardless of which sheet I have open when I run the macro. Also could you give me a bit of insight into what each section of the code is doing. Kind of a learning experience here and I am very grateful.
Thanks,