IF Statement based on first two characters of cell
 

Good morning,

I am currently using the following basic code to copy the contents of a
worksheet to another worksheet:
Sheets("DT Master").Select
Cells.Select
Selection.Copy
Sheets("DT CFD Open Positions").Select
Range("A1").Select
ActiveSheet.Paste

However, I have now discovered, that there are several rows of data within
the worksheet that should not be moved to the other worksheet, but instead be
placed on their own worksheet in the workbook.

The Column that determines which worksheet they need to be copied to is
Column D. If the cell begins with "C0", then it needs to be copied to a new
worksheet named "DT Retail"; if it begins with any other two characters, it
needs to be copied to the "DT CFD Open Positions" worksheet.

I think the best way to achieve this would be to use an IF ELSE statement.
However, as a result of further code run modifications of the data in "DT CFD
Open Positions", I would like the IF statement to first deal with the "DT
Retail" data, and then the "DT CFD Open Positions" data, ie:
IF column D starts with C0,
Copy rows to "DT Retail"
ELSE column D does not start with C0,
Copy rows to "DT CFD Open Positions"

Can someone please help me with the correct syntax and code? I would really
appreciate the help

Regards

IF Statement based on first two characters of cell
 

Try something like this:

NoOfRows = 10 'replace 10 by real No of rows
lastrow_DTRetail = 1 'replace 1 by real No of 1st empty row
lastrow_DTCFD = 1 'replace 1 by real No of 1st empty row

For i = 1 To NoOfRows
If Left(Range("D" & i), 2) = "C0" Then
destsheet = "DT Retail"
lastrow_DTRetail = lastrow_DTRetail + 1
j = lastrow_DTRetail
Else
destsheet = "DT CFD Open Positions"
lastrow_DTCFD = lastrow_DTCFD + 1
j = lastrow_DTCFD
End If
Range("D" & i).EntireRow.Copy
Destination:=Worksheets(destsheet).Range("A" & j)
Next i

Regards,
Stefi

€žPVANS€� ezt Ã*rta:

Good morning,

I am currently using the following basic code to copy the contents of a
worksheet to another worksheet:
Sheets("DT Master").Select
Cells.Select
Selection.Copy
Sheets("DT CFD Open Positions").Select
Range("A1").Select
ActiveSheet.Paste

However, I have now discovered, that there are several rows of data within
the worksheet that should not be moved to the other worksheet, but instead be
placed on their own worksheet in the workbook.

The Column that determines which worksheet they need to be copied to is
Column D. If the cell begins with "C0", then it needs to be copied to a new
worksheet named "DT Retail"; if it begins with any other two characters, it
needs to be copied to the "DT CFD Open Positions" worksheet.

I think the best way to achieve this would be to use an IF ELSE statement.
However, as a result of further code run modifications of the data in "DT CFD
Open Positions", I would like the IF statement to first deal with the "DT
Retail" data, and then the "DT CFD Open Positions" data, ie:
IF column D starts with C0,
Copy rows to "DT Retail"
ELSE column D does not start with C0,
Copy rows to "DT CFD Open Positions"

Can someone please help me with the correct syntax and code? I would really
appreciate the help

Regards

IF Statement based on first two characters of cell
 

Hi,

Try this. I wasn't sure if you wanted to add the shhets with code. This
doesn't, I have assumed they already exist

Sub stance()
Dim MyRange
Dim CopyrangeRetail As Range
Dim CopyrangeOpen As Range
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = Range("D1:D" & lastrow)
For Each c In MyRange
If UCase(Left(c.Value, 2)) = "CO" Then
If CopyrangeRetail Is Nothing Then
Set CopyrangeRetail = c.EntireRow
Else
Set CopyrangeRetail = Union(CopyrangeRetail, c.EntireRow)
End If
End If
If UCase(Left(c.Value, 2)) < "CO" Then
If CopyrangeOpen Is Nothing Then
Set CopyrangeOpen = c.EntireRow
Else
Set CopyrangeOpen = Union(CopyrangeOpen, c.EntireRow)
End If
End If
Next
If Not CopyrangeRetail Is Nothing Then
CopyrangeRetail.Copy Destination:=Sheets("DT Retail").Range("A1")
End If
If Not CopyrangeOpen Is Nothing Then
CopyrangeOpen.Copy Destination:=Sheets("DT CFD Open
Positions").Range("A1")
End If
End Sub


Mike

"PVANS" wrote:

Good morning,

I am currently using the following basic code to copy the contents of a
worksheet to another worksheet:
Sheets("DT Master").Select
Cells.Select
Selection.Copy
Sheets("DT CFD Open Positions").Select
Range("A1").Select
ActiveSheet.Paste

However, I have now discovered, that there are several rows of data within
the worksheet that should not be moved to the other worksheet, but instead be
placed on their own worksheet in the workbook.

The Column that determines which worksheet they need to be copied to is
Column D. If the cell begins with "C0", then it needs to be copied to a new
worksheet named "DT Retail"; if it begins with any other two characters, it
needs to be copied to the "DT CFD Open Positions" worksheet.

I think the best way to achieve this would be to use an IF ELSE statement.
However, as a result of further code run modifications of the data in "DT CFD
Open Positions", I would like the IF statement to first deal with the "DT
Retail" data, and then the "DT CFD Open Positions" data, ie:
IF column D starts with C0,
Copy rows to "DT Retail"
ELSE column D does not start with C0,
Copy rows to "DT CFD Open Positions"

Can someone please help me with the correct syntax and code? I would really
appreciate the help

Regards

IF Statement based on first two characters of cell
 

Hi Mike and Steffi

Thanks for the advice. Mike, your one worked like a charm, thanks so much.
Really appreciate the help.

Regards

"Mike H" wrote:

Hi,

Try this. I wasn't sure if you wanted to add the shhets with code. This
doesn't, I have assumed they already exist

Sub stance()
Dim MyRange
Dim CopyrangeRetail As Range
Dim CopyrangeOpen As Range
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = Range("D1:D" & lastrow)
For Each c In MyRange
If UCase(Left(c.Value, 2)) = "CO" Then
If CopyrangeRetail Is Nothing Then
Set CopyrangeRetail = c.EntireRow
Else
Set CopyrangeRetail = Union(CopyrangeRetail, c.EntireRow)
End If
End If
If UCase(Left(c.Value, 2)) < "CO" Then
If CopyrangeOpen Is Nothing Then
Set CopyrangeOpen = c.EntireRow
Else
Set CopyrangeOpen = Union(CopyrangeOpen, c.EntireRow)
End If
End If
Next
If Not CopyrangeRetail Is Nothing Then
CopyrangeRetail.Copy Destination:=Sheets("DT Retail").Range("A1")
End If
If Not CopyrangeOpen Is Nothing Then
CopyrangeOpen.Copy Destination:=Sheets("DT CFD Open
Positions").Range("A1")
End If
End Sub


Mike

"PVANS" wrote:

Good morning,

I am currently using the following basic code to copy the contents of a
worksheet to another worksheet:
Sheets("DT Master").Select
Cells.Select
Selection.Copy
Sheets("DT CFD Open Positions").Select
Range("A1").Select
ActiveSheet.Paste

However, I have now discovered, that there are several rows of data within
the worksheet that should not be moved to the other worksheet, but instead be
placed on their own worksheet in the workbook.

The Column that determines which worksheet they need to be copied to is
Column D. If the cell begins with "C0", then it needs to be copied to a new
worksheet named "DT Retail"; if it begins with any other two characters, it
needs to be copied to the "DT CFD Open Positions" worksheet.

I think the best way to achieve this would be to use an IF ELSE statement.
However, as a result of further code run modifications of the data in "DT CFD
Open Positions", I would like the IF statement to first deal with the "DT
Retail" data, and then the "DT CFD Open Positions" data, ie:
IF column D starts with C0,
Copy rows to "DT Retail"
ELSE column D does not start with C0,
Copy rows to "DT CFD Open Positions"

Can someone please help me with the correct syntax and code? I would really
appreciate the help

Regards

IF Statement based on first two characters of cell
 

Glad I could help and thanks for the feedback


"PVANS" wrote:

Hi Mike and Steffi

Thanks for the advice. Mike, your one worked like a charm, thanks so much.
Really appreciate the help.

Regards

"Mike H" wrote:

Hi,

Try this. I wasn't sure if you wanted to add the shhets with code. This
doesn't, I have assumed they already exist

Sub stance()
Dim MyRange
Dim CopyrangeRetail As Range
Dim CopyrangeOpen As Range
lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set MyRange = Range("D1:D" & lastrow)
For Each c In MyRange
If UCase(Left(c.Value, 2)) = "CO" Then
If CopyrangeRetail Is Nothing Then
Set CopyrangeRetail = c.EntireRow
Else
Set CopyrangeRetail = Union(CopyrangeRetail, c.EntireRow)
End If
End If
If UCase(Left(c.Value, 2)) < "CO" Then
If CopyrangeOpen Is Nothing Then
Set CopyrangeOpen = c.EntireRow
Else
Set CopyrangeOpen = Union(CopyrangeOpen, c.EntireRow)
End If
End If
Next
If Not CopyrangeRetail Is Nothing Then
CopyrangeRetail.Copy Destination:=Sheets("DT Retail").Range("A1")
End If
If Not CopyrangeOpen Is Nothing Then
CopyrangeOpen.Copy Destination:=Sheets("DT CFD Open
Positions").Range("A1")
End If
End Sub


Mike

"PVANS" wrote:

Good morning,

I am currently using the following basic code to copy the contents of a
worksheet to another worksheet:
Sheets("DT Master").Select
Cells.Select
Selection.Copy
Sheets("DT CFD Open Positions").Select
Range("A1").Select
ActiveSheet.Paste

However, I have now discovered, that there are several rows of data within
the worksheet that should not be moved to the other worksheet, but instead be
placed on their own worksheet in the workbook.

The Column that determines which worksheet they need to be copied to is
Column D. If the cell begins with "C0", then it needs to be copied to a new
worksheet named "DT Retail"; if it begins with any other two characters, it
needs to be copied to the "DT CFD Open Positions" worksheet.

I think the best way to achieve this would be to use an IF ELSE statement.
However, as a result of further code run modifications of the data in "DT CFD
Open Positions", I would like the IF statement to first deal with the "DT
Retail" data, and then the "DT CFD Open Positions" data, ie:
IF column D starts with C0,
Copy rows to "DT Retail"
ELSE column D does not start with C0,
Copy rows to "DT CFD Open Positions"

Can someone please help me with the correct syntax and code? I would really
appreciate the help

Regards