Copying several columns

Richard70

New Member
Joined
Sep 29, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
You should be able to get it all into one copy command in a macro:

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

Richard70

New Member
Joined
Sep 29, 2009
Messages
6
You should be able to get it all into one copy command in a macro:

Rich (BB code):
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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

New Member
Joined
Sep 29, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.
Rich (BB code):
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
 
Last edited:

Richard70

New Member
Joined
Sep 29, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.

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

Richard70

New Member
Joined
Sep 29, 2009
Messages
6
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

Well-known Member
Joined
May 8, 2002
Messages
6,012
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
 

Forum statistics

Threads
1,082,045
Messages
5,362,862
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top