Copying several columns

[Richard70]

Hello. I download several spreadsheets monthly that have a large number of columns (A to EZ). In every excel spreadsheet I need to copy only 18 different columns of data from the sheet which are the same columns each month, and put them into a new spreadsheet. It is very time consuming to highlight and copy a few columns at a time throughout the whole sheet.

I am not very experienced in excel, but is there a simple way to copy all of these columns at once out of the spreadsheet and paste them at once into a new one? I would think there must be some way to enter the column letters I need to pull them out.

Thank you so much for any assistance. Greatly appreciated!
-Richard

 

[jbeaucaire]

You should be able to get it all into one copy command in a macro:

Sub CopyColumns()
    Sheets("Sheet1").Range("A:B,D:D,G:G,H:J").Copy Sheets("Sheet2").Range("A1")
End Sub

 

[Richard70]

You should be able to get it all into one copy command in a macro:

Sub CopyColumns()
    Sheets("Sheet1").Range("A:B,D:D,G:G,H:J").Copy Sheets("Sheet2").Range("A1")
End Sub

Thanks so much. Sounds good. I just need to figure out what a macro is and where I put that code you recommend.

 

[jbeaucaire]

How to use the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use.

Press Alt-F8 and select it from the macro list.

 

[Richard70]

Thanks so much I will try this right now.

One other question regarding where in the code you have: ("A:B,D:D,G:G,H:J")

I need the following columns copied:

D,F,G,I,K,L,P,S,V,W,Y,AB,AC,AI,AS,EU,EV,EW

So do I just enter those like that in place of the letters you show in example? I don't understand the ":" between the letters you have.

Thanks again for clarifying!

 

[jbeaucaire]

Each : separates one range of adjacent columns. A:A means column A only. A:C means columns A, B and C. Is that clear?

So we look at your columns and make ranges out of the individual or adjacent columns.

Sub CopyColumns()
    Sheets("Sheet1").Range("D:D,F:G,I:I,K:L,P:P,S:S,V:W,Y:Y,AB:AC,AI:AI,AS:AS,EU:EW").Copy Sheets("Sheet2").Range("A1")
End Sub

 

[Richard70]

Ok, got it yes. That does make sense now. Thank you.

I just tried the macro, however when I click ALT-F8 and select it for the sheet, I get a pop up window titled: Microsoft Visual Basic
And the text:
Run-time error '9'
Subscript out of range

Do you know what that might mean?

 

[jbeaucaire]

I'd guess you don't have sheets named "Sheet1" and / or "Sheet2". Be sure to edit the code to the exact correct names of your sheets.

Sheets("SourceSheet").Range("A:B,D:D,G:G,H:J").Copy Sheets("DestinationSheet").Range("A1")

 

[Richard70]

I tried changing the sheet name I'm copying from and the sheet name pasting too, but still have same error problem.

I don't know if this is going to work too well with the macro because I download about 175 spreadsheets a month that I need to copy these columns from and the names of each sheet is different, also the name of where I need to paste the columns to. So I would have to keep changing the macro sheet name every time I need to copy. Seems like just as hard as using CTL C & V to copy and paste a few columns at a time.

Are these macro codes something that can be adjusted to work easier for my case and needs?

 

[jbeaucaire]

If all the files are stored in the same directory
If there is some logic to how/where you paste the columns each time you open a new data sheet

...then yes, this could be made into a dynamic macro that goes something like:

1) Open first file in designated folder
2) Copy designated columns from designated sheet into Master workbook in first position
3) Close data file
4) Open next file in designated folder
5) Copy designated columns from designated sheet into Master workbook in NEXT logically determined position (next sheet or next column on same sheet?)
6) Continue until all files in designated folder have been processed