Generate a master worksheet from individual worksheets

[Bond007]

Hi all,

I'm having trouble making a master worksheet that imports data from a set of worksheets that are in the same file directory.

Basically,
I have about 200 or so rate sheets and I need to get them all into one master worksheet so that way I can run different data and graphs without manually typing each one into the master worksheet.

I've not really new to excel; however I am new to VBA and I'm assuming that's what I need to use??

I've now uploaded all the correct files that I'm using.

Attached:
RC1272.xls
Master RCF Report.xls

I need to find a way to import the info RC1272 and others alike into the master file. In reality, I'll have about 150-200 "RC" to get moved over.

What stuff that I need to move from the individual RC files are highlighted in YELLOW in the RATESHEET tab

Here's a list of the items that I need moved over from RateSheet (from top to bottom and left to right) to the master file

Provider Name: Column B
Provider Number: Column D
OSS/IPC Resident days: Column F
Total Resident Days: Column G
Total Provider Beds: Column E
Total Allowance Days: Column H
Income: Column k
Admin: Column L
Resident Care: Column M
Dietary: Column N
Laundry: Column O
Utilities: Column P
Taxes: Column W
Cost of Capital: Column R

Again, I don't know if there's a trick to get excel to transfer those cells over or a coding solution.

I think that's all the info. Please let me know if I can provide anymore info.

Much appreciated!

[davesexcel]

For some reason, when I open the worksheets and the master sheet, the master sheet does not have anything to do with the other sheets.

[Bond007]

Oops, my fault. Looks like I accidentally had it overridden when i was trying a few things to get it to work.

I'll upload the correct one now.

[Bond007]

Correct files uploaded. Keep in mind I made this up till I get my real files tomorrow.

[davesexcel]

Correct files uploaded. Keep in mind I made this up till I get my real files tomorrow.

Perhaps we should wait until we get the real examples, that way time will not be wasted.

[Bond007]

Perhaps we should wait until we get the real examples, that way time will not be wasted.

Sure thing. I added additional info and uploaded my correct files

[Bond007]

I've been googling a few Macro's but no luck yet. I was wondering, I would run it in the master worksheet, correct?

[davesexcel]

This works for me, both workbooks need to be opened, click the button on the report workbook, it will send the info to the Master workbook.

[Bond007]

Wow thank you!
I have a few questions though. Where do I see the code so I can understand how it's working

Also, do I have to open RC1272 first or even at all?

Now as I said, I have like 200 of these files. How do I get them all to get imported into the master?

When I press "click me" in the 1272 file, I get the following message:
"Cannot run the macro 'RC1272.xls!SendtoMaster'. The macro may not be available in this workbook or all macros may be disabled. "

Thanks again

[davesexcel]

I double checked the workbook, the workbook RC1272, it has the macro in it, I would then assume you have not enabled macros.
Hit Alt & F11 to go to the VBA editor to see the code,

[Bond007]

I have them enabled. But something is screwy with excel on this computer. It keeps on opening the personal.xlsb file every time I open excel.

I'll try them tomorrow when I get to my other computer.

So I just copy the same macro in all the worksheets?

[Bond007]

Ok they were disabled and I enabled.

However, I now get this message:
Run-time error '9':
subscript out of range

and when I debug it, it highlights the 3rd line in the code:
Set WB = Workbooks("Master RCF Report1")

[Bond007]

Nevermind, I fixed it. Was missing the excel extension, .xls

Works like a charm and I'm running it in Office 2007. Should be fine to use on xlsx files? I really appreciate it!

As far as my question above about applying this to the other worksheets, I'll just copy and paste it and edit the file name to correspond with each one?

[davesexcel]

Excellent, maybe you could mark this thread as solved then.

[Bond007]

I ran into a quick question,

I did some research on the personal macro worksheet and I was wanting to know if I can use it to my advantage or not

Right now the macro above is set file :RC1272 only
Since I have so many files that will use this Macro, is there a way to make the macro avaiable to any worksheet I want by not hardcoding the file name.

For example, is there a way to tell the macro, get the name of the file name and set it to be equal to whatever this file is saved/named as?
I have RC1090, RC1091, RC1092 etc etc

Or must I go in each individual worksheet and copy and paste the macro and change the file name in all the ranges? --> This would pretty much defeat the purpose of the macro though

Thanks again.

edit: I took off the solved prefix this question can be viewed

[Bond007]

^^ Anyone have insights on my question in the post right above?
Thanks again

[Bond007]

Anyone before I start copying and pasting? Cheers