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 havent worked with Macros before so a lot of research has been put in but Im at the point I just dont 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 its 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 its 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 havent worked with Macros before so a lot of research has been put in but Im at the point I just dont 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 its 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 its 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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD] Fav Color[/TD]
[TD]Phone#[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JOE[/TD]
[TD]Green[/TD]
[TD]123-4567[/TD]
[TD]1@email[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JANE[/TD]
[TD]Red[/TD]
[TD]123-4567[/TD]
[TD]2@email[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BOB[/TD]
[TD]Green[/TD]
[TD]123-4567[/TD]
[TD]3@email[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Insert Here[/TD]
[TD]at the bottom of list[/TD]
[TD]that is already there[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]@Email[/TD]
[/TR]
</tbody>[/TABLE]
Okay so the issue is I havent worked with Macros before so a lot of research has been put in but Im at the point I just dont 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 its 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 its 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 havent worked with Macros before so a lot of research has been put in but Im at the point I just dont 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 its 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 its 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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD] Fav Color[/TD]
[TD]Phone#[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]JOE[/TD]
[TD]Green[/TD]
[TD]123-4567[/TD]
[TD]1@email[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]JANE[/TD]
[TD]Red[/TD]
[TD]123-4567[/TD]
[TD]2@email[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BOB[/TD]
[TD]Green[/TD]
[TD]123-4567[/TD]
[TD]3@email[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Insert Here[/TD]
[TD]at the bottom of list[/TD]
[TD]that is already there[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]@Email[/TD]
[/TR]
</tbody>[/TABLE]
