Please can some one help I been Trying to Wrap my head around this.

[jpt145]

Hello, let me just say something this is my first time posting to a forum so bear with me and please let me know if i do something wrong or if there is any way to improve.

Okay so the issue is I haven’t worked with Macros before so a lot of research has been put in but I’m at the point I just don’t have any more time. if you could help thanks.


First off i have been informed that Excel is not the best program to do what I want but it’s all I have.

Excel 2016

here is a code that i found on the internet that seems to match what I want and I tweaked it to my needs



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Code goes in the ThisWorkbook specific module
Dim rng As Range
Dim lastrow As Long
Dim DestSh As String
Dim col As Integer

Select Case Sh.Name
Case "Cold Call Input", "Closed Leads"
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
On Error GoTo exitprog
Application.EnableEvents = False
If Sh.Name = "Cold Call Input" Then
DestSh = "Closed Leads"
col = 1
' Set Target Range
lastrow = Sh.Cells(Sh.Rows.Count, "S").End(xlUp).Row

Set rng = Sh.Range("S1:S55" & lastrow) 'this sets the "Range"

' Only look at that range
If Intersect(Target, rng) Is Nothing Then GoTo exitprog
Else
DestSh = "Cold Call Input"
col = 2
End If
If Target.Text = "move" And Sh.Name = "Cold Call Input" Or _
Target.Text = "" And Sh.Name = "Closed Leads" Then
lastrow = Sheets(DestSh).Cells(Sheets(DestSh).Rows.Count, col).End(xlUp).Row + 1
With Target
.EntireRow.Copy Sheets(DestSh).Range("S" & lastrow)
.EntireRow.Delete Shift:=xlUp
End With
End If
End Select
exitprog:
Application.EnableEvents = True
End Sub



if it is red it’s what I changed

what I would like for the Following code to do is "Search a certain cell range in the "Cold Call Input"


Hello, let me just say something this is my first time posting to a forum so bear with me and please let me know if i do something wrong or if there is any way to improve.

Okay so the issue is I haven’t worked with Macros before so a lot of research has been put in but I’m at the point I just don’t have any more time. if you could help thanks.


First off i have been informed that Excel is not the best program to do what I want but it’s all I have.

Excel 2016

here is a code that i found on the internet that seems to match what I want and I tweaked it to my needs



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Code goes in the ThisWorkbook specific module
Dim rng As Range
Dim lastrow As Long
Dim DestSh As String
Dim col As Integer

Select Case Sh.Name
Case "Cold Call Input", "Closed Leads"
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
On Error GoTo exitprog
Application.EnableEvents = False
If Sh.Name = "Cold Call Input" Then
DestSh = "Closed Leads"
col = 1
' Set Target Range
lastrow = Sh.Cells(Sh.Rows.Count, "S").End(xlUp).Row

Set rng = Sh.Range("S1:S55" & lastrow) 'this sets the "Range"

' Only look at that range
If Intersect(Target, rng) Is Nothing Then GoTo exitprog
Else
DestSh = "Cold Call Input"
col = 2
End If
If Target.Text = "move" And Sh.Name = "Cold Call Input" Or _
Target.Text = "" And Sh.Name = "Closed Leads" Then
lastrow = Sheets(DestSh).Cells(Sheets(DestSh).Rows.Count, col).End(xlUp).Row + 1
With Target
.EntireRow.Copy Sheets(DestSh).Range("S" & lastrow)
.EntireRow.Delete Shift:=xlUp
End With
End If
End Select
exitprog:
Application.EnableEvents = True
End Sub



if it is red it’s what I changed

what I would like for the Following code to do is "Search For a certain Values in a cell range in the "Cold Call Input"
worksheet the cell range in this case is "S1:S55" and if a certain value is in a cellSuch as "Red" or "Green". then that entire row is copied and moved to another worksheet which would be "Closed Leads" into a table, but the kicker is i would like them to be copied to the bottom of the table. what I mean by that is like this eample.

	A	B	C	D
1	Name	Fav Color	Phone#	Email
2	JOE	Green	123-4567	1@email
3	JANE	Red	123-4567	2@email
4	BOB	Green	123-4567	3@email
5	Insert Here	at the bottom of list	that is already there
6
7

<tbody>
</tbody>

and once that is pasted on the Closed leads worksheet it is deleted from the Cold Call Input worksheet.
the other code you may notice has it so that if the Fave color is changed then it goes back to the First worksheet at the bottom of that table.


i really need help please let me know any questions.

@Email

<tbody>
</tbody>

 

[jpt145]

Sorry i Noticed after posting that some things have been copied

Hello, let me just say something this is my first time posting to a forum so bear with me and please let me know if i do something wrong or if there is any way to improve.

Okay so the issue is I havenÂ’t worked with Macros before so a lot of research has been put in but IÂ’m at the point I just donÂ’t have any more time. if you could help thanks.


First off i have been informed that Excel is not the best program to do what I want but itÂ’s all I have.

Excel 2016

here is a code that i found on the internet that seems to match what I want and I tweaked it to my needs



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Code goes in the ThisWorkbook specific module
Dim rng As Range
Dim lastrow As Long
Dim DestSh As String
Dim col As Integer

Select Case Sh.Name
Case "Cold Call Input", "Closed Leads"
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
On Error GoTo exitprog
Application.EnableEvents = False
If Sh.Name = "Cold Call Input" Then
DestSh = "Closed Leads"
col = 1
' Set Target Range
lastrow = Sh.Cells(Sh.Rows.Count, "S").End(xlUp).Row

Set rng = Sh.Range("S1:S55" & lastrow) 'this sets the "Range"

' Only look at that range
If Intersect(Target, rng) Is Nothing Then GoTo exitprog
Else
DestSh = "Cold Call Input"
col = 2
End If
If Target.Text = "move" And Sh.Name = "Cold Call Input" Or _
Target.Text = "" And Sh.Name = "Closed Leads" Then
lastrow = Sheets(DestSh).Cells(Sheets(DestSh).Rows.Count, col).End(xlUp).Row + 1
With Target
.EntireRow.Copy Sheets(DestSh).Range("S" & lastrow)
.EntireRow.Delete Shift:=xlUp
End With
End If
End Select
exitprog:
Application.EnableEvents = True
End Sub



if it is red itÂ’s what I changed

what I would like for the Following code to do is "Search For a certain Values in a cell range in the "Cold Call Input"
worksheet the cell range in this case is "S1:S55" and if a certain value is in a cellSuch as "Red" or "Green". then that entire row is copied and moved to another worksheet which would be "Closed Leads" into a table, but the kicker is i would like them to be copied to the bottom of the table. what I mean by that is like this eample.

	A	B	C	D
1	Name	Fav Color	Phone#	Email
2	JOE	Green	123-4567	1@email
3	JANE	Red	123-4567	2@email
4	BOB	Green	123-4567	3@email
5	Insert Here	at the bottom of list	that is already there
6
7

<tbody>
</tbody>

and once that is pasted on the Closed leads worksheet it is deleted from the Cold Call Input worksheet.
the other code you may notice has it so that if the Fave color is changed then it goes back to the First worksheet at the bottom of that table.


i really need help please let me know any questions.