Excel macro loop: copy and pasting rows into seperate file

[mp90]

Hi,
**Disclaimer: I know NOTHING about programming in VBA**

I have a large set of data that I need to take rows of data and copy and paste it into one large master excel worksheet. The easiest way to understand this is by an example (I think):

1. Path to the files that I need to copy from:
There are 91 subject files within my data set (so within Stroop data files label Sub1-91) there are 6 different files within each Sub1-91 folder (ex. folder for ECG, Respiration, etc). Within each ECG, Resp folder (contained within the subject folder--there are 10 excel workbooks that I must copy data from. **all the files are labeled exactly the same except for subject number which increase by 1.

2. These 10 workbooks are in the same order in the ECG, Resp, folder for EVERY subject. In the final worksheet (the one I am trying to copy and paste the data to), the column headers are also in this same order.

3. I need the loop to run through each one of the 10 workbooks, in the same order everytime, for each subject. The cell that I need to copy from does not differ in row position (it will always be from row 29). However, for certain files I need to copy only cell B29 where others I need to copy from B29-F29. For example:
File 1: will always need to copy B29
File 2: will always need to copy B29-F29
and so on through 10 files.

4. The destination for these copied cells will always be in the same column in the master file. The rows are the different subject numbers (1-91) and after the entire set of 10 files is run through, I can manually open the next folder (that contains the 10 files), but I do not want the macro to overwrite the subject files it had just copied. I would need it to advance to the row directly below it (ex. Sub01 copied into row 1, need Sub02 copied in row 2).

I would appreciate any assistance greatly!

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mp90]

I have not tried yet; I do not know much about VBA. I do have this code:

Sub test2()
Dim wb As Workbook, wbFR As Workbook

Dim wsFR As Worksheet, wsTO As Worksheet

Set wbFR = ActiveWorkbook
Set wsFR = wbFR.ActiveSheet
MsgBox wbFR.Name & wsFR.Name


For counter = 2 To 115 'rows between top an bottom row

Workbooks.Add

Set wb = ActiveWorkbook
Set wsTO = wb.Worksheets("sheet1")
wbFR.Sheets(wsFR.Name).Range("C1:bF1").Copy Destination:=wsTO.Cells(1, "c")
wbFR.Sheets(wsFR.Name).Range("C" & counter, "BF" & counter).Copy Destination:=wsTO.Cells(2, "c")
wbFR.Sheets(wsFR.Name).Range("C116:bF116").Copy Destination:=wsTO.Cells(3, "c")
wb.SaveAs counter & ".xls" 'for filename
wb.Close
Next counter
End Sub


However, I do not know how to edit it in order to do what I have described above.

 

[SkipVought]

If your data is tabular, it would be much simpler, probably with no VBA code at all, to use MS Query faq68-5829.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mp90]

Our data is not tabular unfortunately, so I don't think MS Query will work.

Is there anyway to edit the code above to run through the 10 files (if I have the folder open) and copy it along the rows of the master file?