Hello, new to the forum and I'm new to VB so please be patient with me.
My questions is how to code so as to copy a group of cells to another sheet in different cell location based on conditional cell data.
To be more specific, say in cell B11 is a qualifier "MT" or "AT" or 15 other such and I want to copy B11 and A11 to new sheet putting A11 in A11 and B11 in B11.
Next row may have AT located in N12 so desire to copy M12 to A12 and N12 to B12 in new sheet.
and so on for entire sheet, which may have 7000 or 8000 entry's. These Qualifiers could appear anywhere in the spreadsheet so have to search for them and copy that cell and the previous cell into the new sheet in Columns A & B
Please, any help would be appreciated.
I have tried the following code but it copies the whole row.
Sub Acopy()
SrcLR = Application.CountA(Sheets("Past-here").Range("A:N"))
DestLR = Application.CountA(Sheets("Selec-Data").Range("A:B"), -1)
For i = 1 To SrcLR
P = UCase(Sheets("Past-here").Cells(i, "N"))
If P = "CM" Or P = "DH" Or P = "FF" Or P = "FL" Or P = "GA" Or P = "IN" Or P = "MS" Or P = "PR" Or P = "QA" Or P = "RC" Or P = "RR" Or P = "SF" Or P = "ST" Or P = "TR" Or P = "TX" Or P = "WH" Then
DestLR = DestLR + 1
Sheets("Past-here").Cells(i, 1).EntireRow.Copy Sheets("Selec-Data").Cells(DestLR, 1)
End If
P = UCase(Sheets("Past-here").Cells(i, "B"))
If P = "CM" Or P = "DH" Or P = "FF" Or P = "FL" Or P = "GA" Or P = "IN" Or P = "MS" Or P = "PR" Or P = "QA" Or P = "RC" Or P = "RR" Or P = "SF" Or P = "ST" Or P = "TR" Or P = "TX" Or P = "WH" Then
DestLR = DestLR + 1
Sheets("Past-here").Cells(i, 1).EntireRow.Copy Sheets("Selec-Data").Cells(DestLR, 1)
End If
Next i
End Sub
copy of part of raw sheet and after is copy of result
<tbody>
</tbody>
result of running code;
<tbody>
</tbody>
Please, any help appreciated.
My questions is how to code so as to copy a group of cells to another sheet in different cell location based on conditional cell data.
To be more specific, say in cell B11 is a qualifier "MT" or "AT" or 15 other such and I want to copy B11 and A11 to new sheet putting A11 in A11 and B11 in B11.
Next row may have AT located in N12 so desire to copy M12 to A12 and N12 to B12 in new sheet.
and so on for entire sheet, which may have 7000 or 8000 entry's. These Qualifiers could appear anywhere in the spreadsheet so have to search for them and copy that cell and the previous cell into the new sheet in Columns A & B
Please, any help would be appreciated.
I have tried the following code but it copies the whole row.
Sub Acopy()
SrcLR = Application.CountA(Sheets("Past-here").Range("A:N"))
DestLR = Application.CountA(Sheets("Selec-Data").Range("A:B"), -1)
For i = 1 To SrcLR
P = UCase(Sheets("Past-here").Cells(i, "N"))
If P = "CM" Or P = "DH" Or P = "FF" Or P = "FL" Or P = "GA" Or P = "IN" Or P = "MS" Or P = "PR" Or P = "QA" Or P = "RC" Or P = "RR" Or P = "SF" Or P = "ST" Or P = "TR" Or P = "TX" Or P = "WH" Then
DestLR = DestLR + 1
Sheets("Past-here").Cells(i, 1).EntireRow.Copy Sheets("Selec-Data").Cells(DestLR, 1)
End If
P = UCase(Sheets("Past-here").Cells(i, "B"))
If P = "CM" Or P = "DH" Or P = "FF" Or P = "FL" Or P = "GA" Or P = "IN" Or P = "MS" Or P = "PR" Or P = "QA" Or P = "RC" Or P = "RR" Or P = "SF" Or P = "ST" Or P = "TR" Or P = "TX" Or P = "WH" Then
DestLR = DestLR + 1
Sheets("Past-here").Cells(i, 1).EntireRow.Copy Sheets("Selec-Data").Cells(DestLR, 1)
End If
Next i
End Sub
copy of part of raw sheet and after is copy of result
MNTH | CODE | VOLUME | PRICE | VALUE | TAXES | DEDUCT | VALUE | DECIMAL | VOLUME | VALUE | TAXES | DEDUCT | CD | VALUE | BTU |
100405 | 1 | BAKKE | 23 | #1H | 330113 | 1 | ND | MOUNTRAIL | 3.3061E+11 | OR | 1 | 0 | |||
Jan-16 | 204 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -0.01 | IN | 0.01 | 0 |
Sep-15 | 204 | 0.48 | 0.21 | 0.1 | -0.19 | -303.12 | 303.41 | 0.0065625 | 0 | 0 | 0 | -1.99 | TR | 1.99 | 0 |
Sep-15 | 400 | 0 | 0 | -0.83 | 0 | -0.17 | -0.66 | 0.0065625 | 0 | -0.01 | 0 | 0 | -0.01 | 0 | |
Aug-15 | 204 | -0.51 | -0.14 | 0.07 | 0.54 | -285.83 | 285.36 | 0.0065625 | 0 | 0 | 0 | -1.89 | TR | 1.88 | 0 |
0.01 | FL | ||||||||||||||
Jul-15 | 204 | 673.52 | 2.91 | 1,958.10 | 74.67 | 1,854.48 | 28.95 | 0.0065625 | 4.42 | 12.85 | 0.49 | 0 | -0.22 | 1170 | |
0.3 | CM | ||||||||||||||
6.02 | TR | ||||||||||||||
0.1 | FL | ||||||||||||||
3.18 | SF | ||||||||||||||
2.57 | MS | ||||||||||||||
0.41 | ST | ||||||||||||||
Jul-15 | 400 | 4,916.63 | 0.16 | 766.65 | 23.99 | 774.88 | -32.22 | 0.0065625 | 32.26 | 5.04 | 0.15 | 0 | -0.37 | 0 | |
1.19 | PR |
<tbody>
</tbody>
result of running code;
Jan-16 | 204 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -0.01 | IN | 0.01 | 0 | |
Sep-15 | 204 | 0.48 | 0.21 | 0.1 | -0.19 | -303.12 | 303.41 | 0.006563 | 0 | 0 | 0 | -1.99 | TR | 1.99 | 0 | |
Aug-15 | 204 | -0.51 | -0.14 | 0.07 | 0.54 | -285.83 | 285.36 | 0.006563 | 0 | 0 | 0 | -1.89 | TR | 1.88 | 0 | |
0.01 | FL | |||||||||||||||
0.3 | CM | |||||||||||||||
6.02 | TR | |||||||||||||||
0.1 | FL | |||||||||||||||
3.18 | SF | |||||||||||||||
2.57 | MS | |||||||||||||||
0.41 | ST | |||||||||||||||
1.19 | PR |
<tbody>
</tbody>
Please, any help appreciated.