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

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

Hi
Your details are insufficient. Samples provided should preferably reflect the original data in data type. save the attached file in the same folder as PS11 ... series files. and run the macro
ravi

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

Originally Posted by ravishankar

Hi
Your details are insufficient. Samples provided should preferably reflect the original data in data type. save the attached file in the same folder as PS11 ... series files. and run the macro
ravi

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