Copy data from different files into 1 workbook under relevant column heading.

Hi buddy, thanks for your help I was able to work it out. Thanks!

Originally Posted by dustycrockett

Having a hard time figuring out whether you just need a program to copy and paste data, or whether you need a program that figures out which file to open, opens it then figures out where the data is (what sheet and column)......

this will copy data from A to Z in one open workbook and paste it into another:

Sub test()

    'For convenience, create a variable that refers to the consolidated workbook 
     'and one for the source workbook
Set cwb = Workbooks("Consolidate.xls").Sheets("Sheet1")  
Set pwb = Workbooks("1PS10.xls").Sheets(1)   

r = 3                  'To track which row you are putting the data on

' This will iterate 26 times, incrementing the variable 'j' by one each time 
For j = 1 to 26     
    cwb.Cells(r, 2) = pwb.Cells(j, 2)          '"(r,2)" and "(j,2)" refers to (row,column) -- column B in this case
    r = r + 1          ' Increment the row number
Next
End Sub