copy range from one workbook to active workbook

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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top