Results 1 to 7 of 7

Thread: Macro to feed info into other sheets

  1. #1

    Macro to feed info into other sheets

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    Attached Files Attached Files

  2. #2
    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.

  3. #3
    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.

  4. #4
    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
    Attached Files Attached Files

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

  6. #6
    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.

  7. #7
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts