How to Copy User defined columns from one workbook to another

**Mah_OS** · 10-18-2012, 05:44 PM

Hello, please help me out on my macro!
I just started using VBA programming for work and I have a very large macro to write.
The concept is, I have a main master workbook where all the data is collected and then manipulated. I will be collecting the data from two other source workbooks, both in different formats. These source workbooks are going to updated weekly.

PROBLEM: I want to be able to copy columns from one of the source workbooks and paste into the master workbook. However, because this is rather a big and meticulous project, I need to be able to have the user specify the columns (this could be satisfied with searching for the column names, as these would be known) and then to have the user specify which sheet of the master workbook that the selected columns will be pasted.

The code I have is huge and messy and I haven't attempted to run it yet. Its a mixture of me recording a macro and adding in my own code to clean it up. It does not include code to get the user input on what columns to copy (I have no idea how to do that one) but I do have code on how to get a user specified worksheet. I have posted it if people want to take a look, but I will guarantee that it is hard to interpret, mostly because there is a lot of code in there that smart people would have simplified.

More clarification: There will always be multiple columns selected and these will be different each time depending on which sheet is selected to paste them into. The source sheets have hourly&daily electricity meter readings for 50 different buildings. I am making groups of buildings (ex. group1 will have 4 buildings) and giving each group their own sheet in the master workbook (group1 = sheet1, group2 = sheet2, etc.). All the sheets are pre-named.

I hope all of this makes sense. Please help me out, I have already been working on this bad boy for three days straight.

Please Login or Register  to view this content.

**Norie** · 10-18-2012, 05:52 PM

What specificaly do you need help with?

It seems to be user input but you mention in your post that you could search for the columns.

I think the latter would probably be easier especially if the column names are fixed and they are always in a specific row.

**Mah_OS** · 10-18-2012, 05:58 PM

Sorry. What I would like is for the macro to ask the user which columns they would like to be copied (this can be done as a loop if that's easier) and then for those columns to be pasted into the master (and the active cell will always be D1 for each sheet). Source workbook is already open.
I just re-worded what I had previously posted. So if this doesn't clarify let me know. I also bold-ed the original wording for my problem in the original post.

**Norie** · 10-18-2012, 06:04 PM

So how will the user select the columns?

By name?

**Mah_OS** · 10-18-2012, 06:13 PM

Honestly, I have no idea what is the best way for this, because I don't know the possibilities.
I didn't know if it is possible for the user to search by names, or to do column numbers, or to just click on the column in the source workbook. Option 1 or 3 of these would be the best, but it doesn't matter really.

**Norie** · 10-18-2012, 06:14 PM

Do you know the names of the columns required and the row they will be in?

If you do then the user doesn't need to search for them the code can.

**Mah_OS** · 10-18-2012, 06:18 PM

One thing I did find, if it helps, I found this code posted in another forum:
http://www.daniweb.com/software-deve...-copy-columns#

but it is for worksheets within the same workbook, and it is a bit too complicated for me to manipulated to work for separate workbooks. It also doesn't really do what I want, as far as the end game goes, but they did the User prompting kind of what I was thinking.

**Mah_OS** · 10-18-2012, 06:29 PM

I will know the columns, but the rows doesn't matter because I just need the entire column. The data stretches from row 1 to (possibly) row 5000.

This following explanation is a bit long winded... so bear with me.
I need different columns pasted into different worksheets in the master workbook; So it will be different columns of data per worksheet. However, it will always be the same buildings going into the same work sheet.
If there is a way to get the macro to do all of this without the user inputs that would be amazing; However, there is going to be 12 worksheets of data, so I am assuming that the macro will be long and slow.
I was thinking that if I ran the macro 12 times, for each worksheet, then I (the user) would have to specify which columns of data I need for each worksheet, every time I ran the macro. Sorry if this doesn't make sense.
Again, I don't know the best way to go about doing this, because I don't know the possibilities.

**Norie** · 10-18-2012, 06:35 PM

Do you know which row the column names are in?

That's what I was asking, not the no of rows.

**Mah_OS** · 10-18-2012, 06:52 PM

The first row. Heading the columns.

**Mah_OS** · 10-22-2012, 01:15 PM

No help? I really just need to know how to search for columns to be copied by their column header.

**Norie** · 10-22-2012, 01:32 PM

If you have a list of the column headers you want to copy in a worksheet you can use the Match worksheet function to find each header in another sheet and then copy that column to yet another worksheet.

So that would be a worksheet with a list (lists?) of column names and a 'data' worksheet from which the data is actually copied from.

Perhaps if you posted an example workbook I could demonstrate how to do that.