selecting multiple columns, copy to new sheet VBA code ?

[RompStar]

Hi there....

I have a raw report that I get, it's headers are always the same, but the order of the columns might not be the same everytime and the number of rows also could be different.

Anyways, I know what headers of the columns of interest that I want, I would like to copy the multiple columns and then paste them into a new sheet, please keep in mind that the columns are multiple and might not be next to each others, so:

Let's say that I have a sheet with Columns A through W

but I might only want 5 columns, A, C, R, P, W - just as as example:

The header name are always the same, say:

A - First Name
C - Last Name
R - Suffix
P - Home Phone
W - Spend Rank

I noticed that I could select multiple columns by using the Ctrl + and clicking on the columns, but I couldn't copy them or nothing, it wasn't allowed.

Please let me know how I can do this in VBA! that would be a great starting point for me, then I will need to learn how to place the columns in the order that I want and do some other things on them.

Thanks!

 

[sericom]

'I noticed that I could select multiple columns by using the Ctrl + and clicking on the columns, but I couldn't copy them or nothing, it wasn't allowed. '

You should be able to do this, just make sure you're in row 1 when pasting

 

[Peter_SSs]

RompStar

See if the code below is some use. Note, if you put the column headings in the line:
myColumns = Array(...
in the order you want them in the final sheet, you won't have to re-order them at the end.

Also, I named my original sheet with all the data as "Original" and the sheet being copied to as "Final". You will need to amend the code to reflect your sheet names.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MoveColumns()
    <SPAN style="color:#00007F">Dim</SPAN> wsO <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> wsF <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> wsO = Worksheets("Original")
    <SPAN style="color:#00007F">Set</SPAN> wsF = Worksheets("Final")
    myColumns = Array("Spend Rank", "Last Name", "First Name", "Suffix", "Home Phone")
    <SPAN style="color:#00007F">With</SPAN> wsO.Range("A1:W1")
        <SPAN style="color:#00007F">For</SPAN> i = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myColumns)
            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
            .Find(myColumns(i)).EntireColumn.Copy Destination:=wsF.Cells(1, i + 1)
            Err.Clear
        <SPAN style="color:#00007F">Next</SPAN> i
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> wsO = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> wsF = <SPAN style="color:#00007F">Nothing</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

The result for me was turning this...

Excel Workbook
	A	B	C	D	E	F	G
1	First Name	Last Name	Title	Home Phone	Suffix	Age	Spend Rank
2	Fred	Smith	Mr		Jr	32	5
3	Jones	Ann	Mrs	12345678		28	2
Original

... into this...

Excel Workbook
	A	B	C	D	E
1	Spend Rank	Last Name	First Name	Suffix	Home Phone
2	5	Smith	Fred	Jr
3	2	Ann	Jones		12345678
Final

 

[triff]

Peter
That is a really neat way of dealing with Rompstar's problem. I am learning so much about Excel and in particular VBA from participating in this forum.
Thanks
Michael

 

[RompStar]

Yay, I love this Forumn, it's so helpful, I will definately try all the help, thanks for taking the time to think it and in responding.

Rompstar will have a cold one on for you at the Pub after work, Cheers!!!

 

[RompStar]

Got a question:

Set wsO = Worksheets("data")
Set wsF = Worksheets("Sheet1")

So that I don't have to type in the name of the sheet with the data, I changed it a little bit:

Set wsO = ActiveSheet

But, how do I create a new sheet for the data that will be moved into ? so that I don't have to create it and keep track of it's name ?

 

[RompStar]

Acctually I kinda solved this and then not. When I place this code into a sheet, it works, but when I place it into my personals folder in the Modules it don't work, why ? All that I changed was the top part...

Sub MoveColumns()
Dim wsO As Worksheet
Dim wsF As Worksheet
Dim i As Integer
Dim sheetName As String

sheetName = InputBox("Please enter the name of the new Sheet which will contain your Phone List", "Sheet Name")
Sheets.Add.Name = sheetName

Application.ScreenUpdating = False
Set wsO = ActiveSheet
Set wsF = Sheets(sheetName)

The VBE runs fine, but the new sheet in empty, it contains no information at all. When I use this same code in the Sheet of the document, it works just fine and transferes all the information.... Any ideas ?

Thanks for all the help.

 

[triff]

I think your code is unclear about what the ActiveSheet is. Use Peter's approach and specify the Original sheet. I made this one change and got it to work from a Module.
Michael

 

[RompStar]

Thanks - I will play around with it more.

Ray

 

[SandeepGurram]

I am unable to copy only the filtered data for the first columns. Rest of the columns filtered data is being as per the criteria.