VBA code to insert a row under a certain cell value in one worksheet based on a value of a cell in a different worksheet

[Mgizzle04]

The part of the code I need to modify is as follows:


With FLWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
oCol = 2
For Each myCell In myRng.Cells
FLWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With


I need to modify the portion of the code that I have below. Essentially, I have an input worksheet and then state-specific worksheets. I am working on a macros that upon clicking the submit button on the input worksheet, the data contained in Column C of the input worksheet is then moved to the appropriate worksheet based on the data entered in "C3" of the input sheet. Essentially, if C3=Florida then the remaining data in column C is transferred to the Florida worksheet as a new row. The code below works up to that point and inserts the data into the next row it can find. What I am struggling with is...within the actual state-specific worksheets, I have certain topical categories that I want the input data to go in that are provided for in column A every 5 or so rows. If C3 in the input worksheet says "apportionment", I want the macros to search Column A in the state-specific worksheet for the same term and and insert the row of data (beginning in Column B) in the next row after the row containing "apportionment". I have copied only the Florida worksheet portion of the code below and once I have have VBA language that does what I have listed above, I will just copy and paste (adjusting the defined terms as necessary) into the the code of the other states.

If it is helpful at all, I have attached the entire code so that you know how I have defined things. Go easy on me.. I have never written macros before in my life and may have unnecessary things in it and may have done it the long way...but it's the only way that made sense to me based on what I found in other people's threads and get the macros to do what I wanted.


Private Sub CommandButton1_Click()
Dim inputWks As Worksheet
Dim NYWks As Worksheet
Dim NYCWks As Worksheet
Dim NJWks As Worksheet
Dim FLWks As Worksheet
Dim TXWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
Dim myInput As String
'cells to copy from Input sheet - some may contain formulas
With inputWks
If Range("C5").Text = "Other" Then
myCopy = "C7,C11,C13,C15,C17,C19,C21,C23,C25"
Else
myCopy = "C9,C11,C13,C15,C17,C19,C21,C23,C25"
End If
End With
Set inputWks = Worksheets("Input")
Set NYWks = Worksheets("NY")
Set NYCWks = Worksheets("NYC")
Set NJWks = Worksheets("NJ")
Set FLWks = Worksheets("FL")
Set TXWks = Worksheets("TX")

With inputWks
Set myRng = .Range(myCopy)
End With

With inputWks
If Range("C3").Text = "New York" Then
With NYWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
oCol = 1
For Each myCell In myRng.Cells
NYWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
ElseIf Range("C3").Text = "New York City" Then
With NYCWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
oCol = 1
For Each myCell In myRng.Cells
NYCWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
ElseIf Range("C3").Text = "New Jersey" Then
With NJWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
oCol = 1
For Each myCell In myRng.Cells
NJWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
ElseIf Range("C3").Text = "Florida" Then
With FLWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
oCol = 2
For Each myCell In myRng.Cells
FLWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
ElseIf Range("C3").Text = "Texas" Then
With TXWks
nextRow = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
oCol = 1
For Each myCell In myRng.Cells
TXWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
End If
End With

'clear input cells that contain constants
With inputWks
myInput = "C3,C5,C7,C9,C11,C13,C15,C17,C19,C21,C23,C25"
On Error Resume Next
With .Range(myInput).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ',Scroll:=True
End With
On Error GoTo 0
End With

End Sub