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.

Sub Data_analysis()
'
' This macro will open a user specified workbook
' Then data will be collected from the source workbook and pasted into ThisWorkBook
' The data will then be manipulated for statistical purposes.
' ThisworkBook should be updated weekly with new data from a source workbook.
' The new weekly data should APPEND to the existing data in thisworkbook. I haven't figured that one yet either
'
    Dim wbsource1 As Workbook
    'Dim wbsource2 As Workbook
    Dim ws1 As Worksheet
    'Dim lastrow As Long, lastcol As Long
    Dim Ret1, Ret2
    Dim i As Integer
    
    Set ThisWorkbook = ActiveWorkbook
        
    'lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    'lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xlsx*), *.xlsx*", _
    , "Please select first file")
    If Ret1 = False Then Exit Sub
    
    '~~> Get the 2nd File
    'Ret2 = Application.GetOpenFilename("Excel Files (*.xlsx*), *.xlsx*", _
    , "Please select Second file")
    'If Ret2 = False Then Exit Sub
    
    Set wbsource1 = Workbooks.Open(Ret1)
    'Set ws1 = wbsource1.Sheets(1)
    'wbsource1.Activate
    Columns("A:A").Select
    Selection.Copy
    
    ThisWorkbook.Activate
    'Get the user specified sheet to paste timestamps in
    myShts = ThisWorkbook.Sheets.Count
    For i = 1 To myShts
    myList = myList & i & " - " & ThisWorkbook.Sheets(i).Name & " " & vbCr
    Next i
    Dim mySht As Single
    mySht = InputBox("Select sheet to go to." & vbCr & vbCr & myList)
    Sheets(mySht).Select
        
    'Paste time stamps in the first column. This code may change depending on how the data is received.
    ' If the data is just given per week, than I have to change it so that the data appends to the end
    ' of the first column.
    Range("A1").Select
    ActiveSheet.Paste
    ThisWorkbook.Save
    
    'Copy energy data from source to be pasted in thisworkbook
    'This is where I want the use to specify which columns to copy
    wbsource1.Activate
    Columns("C:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ' Paste data into column D.
    ' If the data is just given per week, than I have to change it so that the data appends to the end
    ' of the first column.
    ThisWorkbook.Activate
    Range("D1").Select
    ActiveSheet.Paste
    
    'Manipulating timestamp data to display date only, both columns B and C
    Range("B2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1], ""dd-mm-yy"")"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B4135"), Type:=xlFillDefault
    Range("B2:B4135").Select
    ActiveWindow.ScrollRow = 2
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1], 5)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C4135"), Type:=xlFillDefault
    Range("C2:C4135").Select
    ActiveWindow.ScrollRow = 2
    
    'Add in the single date column to sum up the time intervals
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "23-10"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "24-10"
    Range("I4").Select
    ActiveCell.FormulaR1C1 = "25-10"
    
    Range("I5:I10").Select
    Selection.NumberFormat = "General"
    Range("I5").Select
    ActiveCell.FormulaR1C1 = "26-10"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = "27-10"
    Range("I7").Select
    ActiveCell.FormulaR1C1 = "28-10"
    Range("I8").Select
    ActiveCell.FormulaR1C1 = "29-10"
    Range("I9").Select
    ActiveCell.FormulaR1C1 = "30-10"
    Range("I10").Select
    ActiveCell.FormulaR1C1 = "31-10"

    Range("I11:I39").Select
    Selection.NumberFormat = "@"
    Range("I11").Select
    ActiveCell.FormulaR1C1 = "01-11"
    Range("I12").Select
    ActiveCell.FormulaR1C1 = "02-11"
    Range("I13").Select
    ActiveCell.FormulaR1C1 = "03-11"
    Range("I14").Select
    ActiveCell.FormulaR1C1 = "04-11"
    Range("I15").Select
    ActiveCell.FormulaR1C1 = "05-11"
    Range("I16").Select
    ActiveCell.FormulaR1C1 = "06-11"
    Range("I17").Select
    ActiveCell.FormulaR1C1 = "07-11"
    Range("I18").Select
    ActiveCell.FormulaR1C1 = "08-11"
    Range("I19").Select
    ActiveCell.FormulaR1C1 = "09-11"
    
    Range("I19").Select
    Selection.AutoFill Destination:=Range("I19:I22"), Type:=xlFillFormats
    Range("I19:I22").Select
    Range("I20").Select
    ActiveCell.FormulaR1C1 = "10-11"
    Range("I21").Select
    ActiveCell.FormulaR1C1 = "11-11"
    Range("I22").Select
    ActiveCell.FormulaR1C1 = "12-11"
    Range("I23").Select
    ActiveCell.FormulaR1C1 = "13-11"
 
    Range("I24").Select
    ActiveCell.FormulaR1C1 = "14-11"
    Range("I25").Select
    ActiveCell.FormulaR1C1 = "15-11"
    Range("I26").Select
    ActiveCell.FormulaR1C1 = "16-11"
    Range("I27").Select
    ActiveCell.FormulaR1C1 = "17-11"
    Range("I28").Select
    ActiveCell.FormulaR1C1 = "18-11"
    Range("I29").Select
    ActiveCell.FormulaR1C1 = "19-11"
    Range("I30").Select
    ActiveCell.FormulaR1C1 = "20-11"
    Range("I31").Select
    ActiveCell.FormulaR1C1 = "21-11"
    Range("I32").Select
    ActiveCell.FormulaR1C1 = "22-11"
    Range("I33").Select
    ActiveCell.FormulaR1C1 = "23-11"
    Range("I34").Select
    ActiveCell.FormulaR1C1 = "24-11"
    Range("I35").Select
    ActiveCell.FormulaR1C1 = "25-11"
    Range("I36").Select
    ActiveCell.FormulaR1C1 = "26-11"
    Range("I37").Select
    ActiveCell.FormulaR1C1 = "27-11"
    Range("I38").Select
    ActiveCell.FormulaR1C1 = "28-11"
    Range("I39").Select
    ActiveCell.FormulaR1C1 = "29-11"
    Range("I40").Select
    ActiveCell.FormulaR1C1 = "30-11"
    
    Range("I41:I45").Select
    Selection.NumberFormat = "@"
    Range("I41").Activate
    ActiveCell.FormulaR1C1 = "01-12"
    Range("I42").Activate
    ActiveCell.FormulaR1C1 = "02-12"
    Range("I43").Select
    ActiveCell.FormulaR1C1 = "03-12"
    Range("I44").Select
    ActiveCell.FormulaR1C1 = "04-12"
    
    'Add in the data manipulation for the total daily energy value
    ActiveWindow.SmallScroll Down:=-48
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=SUMIF(C3, RC9,C[-6] )"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:L43"), Type:=xlFillDefault
    Range("J2:L43").Select
    
    ActiveWindow.SmallScroll Down:=-27
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=IF(C[-10]=RC[-4], R[94]C[-6]-RC[-6])"
    Range("M2").Select
End Sub

**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.

**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.

**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: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.