copy range from one workbook to active workbook

[dnickelson]

I'm trying to copy a range from preexisting workbooks into the current sheet. And this may take some explaining.

I have several sheets that are created twice a week, each sheet contains a list of stores that don't meet certain requirements. there are 7 types of stores, bd, st, fp, tx, lm. When the report is run, the results are saved to a workbook named bdsunday.xls, fpthursday.xls, etc depending on store type and day report is run, if no stores of the specific type are on the report, no file is created. The files are saved in a folder corresponding to the date it was run, 01-02-04, 02-23-04, etc.

This is fine, since people work off these reports, but I would like to pull all of the stores on each workbook into 1 sheet for reference purposes (something the report itself is unable to do).

1 problem is that the reports are not always named correctly, dbsun.xls, dbsunday.xls, etc. I could standardize the name, but would have to deal with human error in that case.

All this being said, I have a project in front of me, but mostly I have it under control, I wrote some code to give me each date to path to, I think I'll be able to figure out how to deal with looking through each file inside each folder using wildcards, but I haven't been able to figure out the syntax for copying the range from the existing workbook to my totals sheet.

the existing workbooks will contain 1 sheet (Sheet1), off this sheet, I need to copy from A8 to d? (could be 1 to 50 rows, but only need over to column d). I think I can figure out the syntax of where to put in on my worksheet using end(xlup), but just can't figure out how to get the range I need into a variable or the clipboard to insert into my sheet.

If anyone has read through all this, thanks for even sticking through it. If anyone has suggestions for the copy or the entire procedure, thanks again.

 

[Cbrine]

I would suggset something like this. You will need to integrate this code in your loop where you get the filename using the dir function. But it should give you a good idea how to do what you want. You can keep using the SB and SS references since you close the file and open a new one.

Dim sb as workbook, ss as worksheet
Dim pb as workbook, ps as worksheet

set pb = Activeworkbook
set ps = Activesheet

filename = Your code to get filename

Do until filename =""(Or whatever your code is)
workbooks.Open(Filename)

set sb = Activeworkbook
set ss = Activesheet

SS.range("A1",range("D65000").end(xlup)).copy ps.Range("A65000").end(xlup).offset(1,0)

sb.close
Filename = NewFile(Your code to get next filename)
Loop

end sub

Hope this helps.