conditional cell copy with multiple conditions

L

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

[TABLE="width: 1494"]
<tbody>[TR]
[TD]MNTH[/TD]
[TD]CODE[/TD]
[TD]VOLUME[/TD]
[TD]PRICE[/TD]
[TD]VALUE[/TD]
[TD]TAXES[/TD]
[TD]DEDUCT[/TD]
[TD]VALUE[/TD]
[TD]DECIMAL[/TD]
[TD]VOLUME[/TD]
[TD]VALUE[/TD]
[TD]TAXES[/TD]
[TD]DEDUCT[/TD]
[TD]CD[/TD]
[TD]VALUE[/TD]
[TD]BTU[/TD]
[/TR]
[TR]
[TD="align: right"]100405[/TD]
[TD="align: right"]1[/TD]
[TD]BAKKE[/TD]
[TD="align: right"]23[/TD]
[TD]#1H[/TD]
[TD="align: right"]330113[/TD]
[TD="align: right"]1[/TD]
[TD]ND[/TD]
[TD]MOUNTRAIL[/TD]
[TD="align: right"]3.3061E+11[/TD]
[TD]OR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jan-16[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.01[/TD]
[TD]IN[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]0.48[/TD]
[TD="align: right"]0.21[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]-0.19[/TD]
[TD="align: right"]-303.12[/TD]
[TD="align: right"]303.41[/TD]
[TD="align: right"]0.0065625[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1.99[/TD]
[TD]TR[/TD]
[TD="align: right"]1.99[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.83[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.17[/TD]
[TD="align: right"]-0.66[/TD]
[TD="align: right"]0.0065625[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Aug-15[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]-0.51[/TD]
[TD="align: right"]-0.14[/TD]
[TD="align: right"]0.07[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]-285.83[/TD]
[TD="align: right"]285.36[/TD]
[TD="align: right"]0.0065625[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1.89[/TD]
[TD]TR[/TD]
[TD="align: right"]1.88[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jul-15[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]673.52[/TD]
[TD="align: right"]2.91[/TD]
[TD="align: right"]1,958.10[/TD]
[TD="align: right"]74.67[/TD]
[TD="align: right"]1,854.48[/TD]
[TD="align: right"]28.95[/TD]
[TD="align: right"]0.0065625[/TD]
[TD="align: right"]4.42[/TD]
[TD="align: right"]12.85[/TD]
[TD="align: right"]0.49[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.22[/TD]
[TD="align: right"]1170[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.3[/TD]
[TD]CM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.02[/TD]
[TD]TR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.1[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.18[/TD]
[TD]SF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.57[/TD]
[TD]MS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.41[/TD]
[TD]ST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]Jul-15[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]4,916.63[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"]766.65[/TD]
[TD="align: right"]23.99[/TD]
[TD="align: right"]774.88[/TD]
[TD="align: right"]-32.22[/TD]
[TD="align: right"]0.0065625[/TD]
[TD="align: right"]32.26[/TD]
[TD="align: right"]5.04[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.37[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1.19[/TD]
[TD]PR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


result of running code;


[TABLE="width: 1088"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]Jan-16[/TD]
[TD="width: 64, align: right"]204[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]-0.01[/TD]
[TD="width: 64"]IN[/TD]
[TD="width: 64, align: right"]0.01[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-15[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]0.48[/TD]
[TD="align: right"]0.21[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]-0.19[/TD]
[TD="align: right"]-303.12[/TD]
[TD="align: right"]303.41[/TD]
[TD="align: right"]0.006563[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1.99[/TD]
[TD]TR[/TD]
[TD="align: right"]1.99[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-15[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]-0.51[/TD]
[TD="align: right"]-0.14[/TD]
[TD="align: right"]0.07[/TD]
[TD="align: right"]0.54[/TD]
[TD="align: right"]-285.83[/TD]
[TD="align: right"]285.36[/TD]
[TD="align: right"]0.006563[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-1.89[/TD]
[TD]TR[/TD]
[TD="align: right"]1.88[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.01[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.3[/TD]
[TD]CM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.02[/TD]
[TD]TR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.1[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.18[/TD]
[TD]SF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.57[/TD]
[TD]MS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.41[/TD]
[TD]ST[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1.19[/TD]
[TD]PR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please, any help appreciated.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

J
  • Question Question
copying adjacent cells based on value of referenced cell
Replies
4
Views
233
arun_eagertolearn
A
C
  • Question Question
VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets
Replies
2
Views
485
chittunoo
C
E
  • Solved
VBA Find match, if found, copy rows and paste in different workbook
Replies
6
Views
461
MARK858
MARK858
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
523
glaccounting
G
E
  • Question Question
VBA - COPY AND PASTE RANGE OF CELLS IN MULTIPLE SHEETS
Replies
1
Views
887
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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