conditional cell copy with multiple conditions

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
25
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

MNTHCODEVOLUMEPRICEVALUETAXESDEDUCTVALUEDECIMALVOLUMEVALUETAXESDEDUCTCDVALUEBTU
1004051BAKKE23#1H3301131NDMOUNTRAIL3.3061E+11OR10
Jan-162040000000000-0.01IN0.010
Sep-152040.480.210.1-0.19-303.12303.410.0065625000-1.99TR1.990
Sep-1540000-0.830-0.17-0.660.00656250-0.0100-0.010
Aug-15204-0.51-0.140.070.54-285.83285.360.0065625000-1.89TR1.880
0.01FL
Jul-15204673.522.911,958.1074.671,854.4828.950.00656254.4212.850.490-0.221170
0.3CM
6.02TR
0.1FL
3.18SF
2.57MS
0.41ST
Jul-154004,916.630.16766.6523.99774.88-32.220.006562532.265.040.150-0.370
1.19PR

<tbody>
</tbody>


result of running code;


Jan-162040000000000-0.01IN0.010
Sep-152040.480.210.1-0.19-303.12303.410.006563000-1.99TR1.990
Aug-15204-0.51-0.140.070.54-285.83285.360.006563000-1.89TR1.880
0.01FL
0.3CM
6.02TR
0.1FL
3.18SF
2.57MS
0.41ST
1.19PR

<tbody>
</tbody>


Please, any help appreciated.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
This is the correct code, works perfectly;

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"))
Q = 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, 13).Cells.Copy Sheets("Selec-Data").Cells(DestLR, 1)
Sheets("Past-here").Cells(i, 14).Cells.Copy Sheets("Selec-Data").Cells(DestLR, 2)
ElseIf Q = "CM" Or Q = "DH" Or Q = "FF" Or Q = "FL" Or Q = "GA" Or Q = "IN" Or Q = "MS" Or Q = "PR" _
Or Q = "QA" Or Q = "RC" Or Q = "RR" Or Q = "SF" Or Q = "ST" Or Q = "TR" Or Q = "TX" _
Or Q = "WH" Then
DestLR = DestLR + 1
Sheets("Past-here").Cells(i, 1).Cells.Copy Sheets("Selec-Data").Cells(DestLR, 1)
Sheets("Past-here").Cells(i, 2).Cells.Copy Sheets("Selec-Data").Cells(DestLR, 2)
End If
Next i
End Sub
 
Upvote 0

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top