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
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
Bookmarks