This what I have so far.
I need a data sheet to be exported to a template sheet according to the columns given. However I need column "F" to not import duplicates in to the template sheet. Also the template sheet has a formula in row A68 and the code I have makes me put the data after it even though I need it before the formula as well as add new rows if there is more than 64 rows of data taken. I say 64 because the blank rows start at 4. Please help cause I feel like I'm almost done with this project yet I'm hitting some road blocks.
I need a data sheet to be exported to a template sheet according to the columns given. However I need column "F" to not import duplicates in to the template sheet. Also the template sheet has a formula in row A68 and the code I have makes me put the data after it even though I need it before the formula as well as add new rows if there is more than 64 rows of data taken. I say 64 because the blank rows start at 4. Please help cause I feel like I'm almost done with this project yet I'm hitting some road blocks.
Code:
Sub Redemption()
Dim wsDatatable As Worksheet
Dim wsTempelate As Worksheet
Dim rangeFound As Range
Dim rangeNames As Range
Dim NameCells As Range
Dim stringFirst As String
Dim stringNames As String
Dim stringUniqueNames As String
Set wsDatatable = Sheets("DATA INPUT TABLE")
Set wsTempelate = Sheets("CLASS GROUPING ID")
Set rangeNames = wsDatatable.Range("A2", wsDatatable.Cells(Rows.Count, "A").End(xlUp))
For Each NameCells In rangeNames.Cells
If InStr(1, "|" & stringUniqueNames & "|", "|" & NameCells.Text & "|", vbTextCompare) = 0 Then
stringUniqueNames = stringUniqueNames & "|" & NameCells.Text
Set rangeFound = rangeNames.Find(NameCells.Text, rangeNames.Cells(rangeNames.Cells.Count), xlValues, xlWhole)
If Not rangeFound Is Nothing Then
stringFirst = rangeFound.Address
stringNames = NameCells.Text
stringNames = Trim(Left(WorksheetFunction.Trim(stringNames), 31))
If Evaluate("IsRef('" & stringNames & "'!A1)") = False Then
wsTempelate.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = stringNames
End If
With Sheets(stringNames)
Do
If LCase(wsDatatable.Cells(rangeFound.Row, "I").Text) = "full liquidation" Or LCase(wsDatatable.Cells(rangeFound.Row, "I").Text) = "redemption" Then
.Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "E").Value
.Cells(Rows.Count, "B").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "F").Value
.Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "B").Value
.Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "O").Value
.Cells(Rows.Count, "E").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "L").Value
End If
Set rangeFound = rangeNames.Find(NameCells.Text, rangeFound, xlValues, xlWhole)
Loop While rangeFound.Address <> stringFirst
End With
End If
End If
Next NameCells
Set wsDatatable = Nothing
Set wsTempelate = Nothing
Set rangeFound = Nothing
Set rangeNames = Nothing
Set NameCells = Nothing
End Sub