Brain Teaser

[spcalan]

Here is my question.

I have a worksheet with 12 columns ( L ) with data.

I am wanting to find each cell in column L that starts with "Q".
It will always start with "Q" and be either "Q001" or "Q002" or "Q003" or "Q004".

I want to be able to run a VBA code that will capture it along with the Column A/Column B/Column L and copy/paste into another sheet.

Any ideas?

 

[spcalan]

It seems to me that the code should go like this:

Find cell in Col L that starts with "Q".
Copy ColA/ColB/ColC/ColL
Paste into Sheet 2.

 

[schielrn]

Something like this should work:

sub copyabl()
dim LR2 as long
For i = 1 to cells(rows.count,"L").end(xlup).row
if left(cells(i,"L").value,1)="Q" then
LR2 = Sheets("Sheet2").cells(rows.count,1).end(xlup).row +1
Sheets("Sheet2").range("A" & lr2 & ":B" & lr2).value = range("A" & i & ":B" & i).value
Sheets("Sheet2").range("C" & lr2).value = range("L" & i).value
end if
next i
end sub

This is untested. Hope that helps.

 

[erik.van.geit]

Hi,

try this manually: should not take more than 1 minute
autofilter column L
custom criterion: "starts with"
then write Q

hide the columns C to K
copy and paste

If this works for you (for me it does) and you need this often, then you can record a macro while doing this. Using autofilter will make your code much quicker than row-by-row approach.

kind regards,
Erik