Dynamic selection of columns in Excel

[sisterProg]

Hello, I am new to VBA and need your help.
I want to create a macro to do this:
When the user selects some columns, the content of those columns is copied to a new sheet. Will be copied only the rows marked with "yes" in a column which will not be copied.

How do I use the user's selection in VBA? May be a range of columns which is not continue and it's a dynamic number of columns (user-defined).

Thank you in advance, VBA -Community .

[Bob Phillips]

[VBA]Sub CopyData()
Const ID_COLUMN As String = "B"
Dim ws As Worksheet
Dim source As Range
Dim lastrow As Long
Dim i As Long

Set ws = ActiveSheet
Set source = Selection
Worksheets.Add
With ActiveSheet

lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Row
source.Copy .Range("A1")
For i = 1 To lastrow

If ws.Cells(i, ID_COLUMN).Value <> "yes" Then

.Cells(i, "A").Resize(, source.Columns.Count).ClearContents
End If
Next i
End With
End Sub
[/VBA]

[sisterProg]

Thanks, xld. I am very happy you answered so quickly.

I am using M Office 2010 and perharps that's why I've got some errors.
1) Type mismatch in
[vba] lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Rows.Count [/vba]
I replaced it with
[vba] lastrow = ws.UsedRange.Rows.Count [/vba]
2) The object is missing in the line
[vba]source.Copy.Range ("A1")[/vba]
If I just use it with . (dot) in front, I get also error.

Please help.

thanks a lot!

[Bob Phillips]

I have 2010 as well, and it all worked in my tests, so maybe post a workbook.

[sisterProg]

I have a huge workbook!

[Bob Phillips]

I have just looked again at the code you posted. That is not the code I gave you so I suggest you re-check what you did.

[sisterProg]

Hi,

in this line
[VBA]lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Row [/VBA]
I've got "type mismatch", that"s why I change it a bit.

So, the problem is still opened...

[Bob Phillips]

First time you said you got a type mismatch with

[VBA]lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Rows.Count [/VBA]

which is not what I gave, then you say it is with

[VBA]lastrow = ws.Cells(ws.Rows.Count, ID_COLUMN).End(xlUp).Row[/VBA]

Sorry, but I cannot trust what you have done, so there is no point trying anything unless you post the workbook with what code you have added so we can see what has been done.

[sisterProg]

If you don't trust me, I trust you and this community.

Let's see. I have a more than 10000 lines in my original worksheet. I created a model, and I attach it here. it seems the deletion of cells doesn't work.

I must confess I would prefer to copy in the new sheet only the rows with a certain value in them, not to copy everything and delete after. That's because of the huge amount of input data which may not be all present in the resulting data.

Thank you in advance. You're great!

[sisterProg]

Any help, please?