dnickelson
Board Regular
- Joined
- Oct 30, 2003
- Messages
- 118
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.
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.