Is this possible? Macro to pull data from unspecified # of sheets?

[liquidmettle]

Hi Everyone,

I have a rather ambitious idea that may not even be possible in excel. It's already obvious it will need to be done by macro, but even then I'm not sure.

I currently have a dashboard system set up with a master sheet that links to program sheets. All the program sheets are contained in their own folder (nothing else in that folder).

Is it possible to write a macro that will look through the program sheet folder and automatically pull specified data from all sheets?

I'm trying to pull specific data from multiple sheets into a reporting sheet (that will be formatted how I want). All program sheets will have distinct names that will vary by end user, therefore its not possible to base it on name.

Ultimately I want to make a button that will look through the folder, copy all the data from Range X in every sheet (it will be the same range as the sheets are identical except for the data entered).

The reason I am pursuing this is because many of my end users will not have the expertise to link the data into the reporting sheet on their own. And since they may add new programs over time, it would be best to automate the process with a button.

It will not matter if data from each sheet is posted in successive ranges (for instance Sheet B's data is directly below Sheet A's data, etc) because I'll have it import to an unformatted data sheet within the reporting workbook.

So is this possible?

Any ideas?

Thank you to everyone in advance!

-LM

[alansidman]

Here is some code that will need to be modified to your specific needs, but it should put you on the correct starting trail

Please Login or Register  to view this content.

[MarvinP]

Hi LM,

This sounds like a job for PowerPivots, but it is in 2010 or 2013 Excel and not 2007 .

http://www.powerpivotpro.com/what-is-powerpivot/

[shg]

Cross-posted at http://www.mrexcel.com/forum/excel-q...-possible.html

[liquidmettle]

Thank you for your quick reply alansidman!

I have not had the chance to test this yet, but I will let you know the results when I do. (And will update the thread appropriately).

[alansidman]

The section below needs to be replaced with your code for what ever action you want to do with each workbook. This was from a previous thread where the OP was searching for some keywords

Please Login or Register  to view this content.

[liquidmettle]

Hi alansidman,

Thank you again for the very quick response you gave me!

I've finally found time to work with the macro you wrote. Here are some of my initial thoughts:

1. The Folder Picker code is something I'd like to change to a specific folder. Basically, I don't want the user to be able to choose. The code should pull from the same, specific folder every time.

Here is the file structure:

All files & sub-folders are contained in a folder named PM Dashboard.

There are four sub-folders: Logo, Mini User-Guides, Program Dashboards, Reports.

The excel file that will run the macro code is in the Reports folder.

The folder I want it to pull data from is the Program Dashboards folder. This will never change. Is it possible to define just this file path without worrying whether the PM Dashboard folder is on the C: drive or a shared drive?

2. I ran the code just to see where it broke down (knowing it would without any edits).

This section is the first stopgap:

Please Login or Register  to view this content.

Does this refer to the sheet name in the excel file I am running the code from? Or from one of the excel files data is being pulled from?

If it's the latter, then my sheet name should be Reporting. I just wanted to double check before I made that change.

3. I'm afraid I'm not sure how I should change the Dubai section you mentioned.

4. Can I remove the "Save and Close" code without breaking any other part of the process? Or does it refer to the workbooks data is being pulled from?

I suppose those are my only questions for now.

Thank you so much again for providing the base code, I appreciate your assistance!

-LM

[alansidman]

1. What is the full path to the folder you wish to retrieve data from? Once this is known, it can be hard coded into the VBA. You have indicated that it may be on the C:\ drive or a network drive. If you are not sure where this is going to be, then you may be better with the Folder Picker or some modification. In either case, supply both full paths to the proper folder.

2. This is sheet1 in each file you are wishing to extract data from.

3. You will have to describe in simple language what you wish to do with each file you open. Only you can determine this.

I'm trying to pull specific data from multiple sheets into a reporting sheet (that will be formatted how I want). All program sheets will have distinct names that will vary by end user, therefore its not possible to base it on name.

4. That step closes and saves each file after it has been processed and then loops to next file to open and process.

[liquidmettle]

Hi Alansidman,

Thank you for the amazingly fast reply!

You've made a good case for the folder picker : ). Is there a way to say "go one folder level up" from where the workbook running the macro is? If it could open up directly into the PM Dashboard folder the user could then just select the Program Dashboards folder and all would be perfect.

For #3, does the file name for each workbook file need to be explicitly stated?

This is generally what I wanted to avoid with the code, because there will be anywhere from 6 to 20 user-named program workbooks (possibly more, but that's a pretty good estimate) tied into the dashboard system.

In simple language, I am hoping to have the macro pull data from a specific range in the sheet named Reporting from every workbook in the specified folder. This data being pulled into a single sheet in the workbook running the macro.

I changed #2 from above to "Reporting" to match the sheet name.

The debugger then gets hung up on:

Please Login or Register  to view this content.

Right now that sheet has a lot of various data and formatting that can all be removed if this macro will pull data as I have envisioned. In total there will be 30 rows of data from each workbook.


Thank you so much for your help!!

[alansidman]

You've made a good case for the folder picker : ). Is there a way to say "go one folder level up" from where the workbook running the macro is? If it could open up directly into the PM Dashboard folder the user could then just select the Program Dashboards folder and all would be perfect.

Not that I am aware. Others may be able to help on this. My suggestion: If you want to run this from different workbooks on different folders, then specifically hard code for each scenario in the file you are running from.

For #3, does the file name for each workbook file need to be explicitly stated?

No, the defining of wb as a workbook and then the code

Please Login or Register  to view this content.

does this for you.

If you know that the last row for each file is 30, you do not need to define and identify the lr through this code. Simply put, change that line to

Please Login or Register  to view this content.

I hope this helpful.

Alan

[liquidmettle]

Hi Alansidman,

Alright. So here's where the testing stands:

I changed that code to:

Please Login or Register  to view this content.

The full range of data runs from A4 to U34.

I ran the macro on a set of test files that only had data in that range.

The macro ran through without issue!

However, no data was copied to the sheet running the macro.

So right now we stand with no debugging errors, but no data copied.

Any thoughts?

Thanks!

[liquidmettle]

One other consideration:

Will this macro only work by "opening" each of the excel files? This isn't a major problem, more of an inconvenience. I anticipate my end users will have one or two of the program workbooks open and I noticed the macro asks if you want to "reopen" those workbooks (thereby erasing any unsaved changes).

Just wanted to clarify if this is necessary (Ill have to instruct them to close their workbooks if it is).

Thanks.

[alansidman]

Yes. This will open each workbook. Perform what is necessary and then save and close. If it were to work on a workbook that was already open, then you would probably have a conflict on the data saved or it may only open the second file in read-only format. Either way, there would be an issue.

[alansidman]

Please Login or Register  to view this content.

What does your code that you use to extract the data and paste to the "Master" file look like. Hard to evaluate what can't be seen.

[shg]

Is there a way to say "go one folder level up" from where the workbook running the macro is?

Please Login or Register  to view this content.

[liquidmettle]

Okay so I think I've narrowed down the possible culprit area in the code.

Please Login or Register  to view this content.

I think my issue is coming from this code section (not the least of which because I realized I haven't changed "Dubai" yet).

The workbook running the macro is named "PM Reports". While there is a "Sheet1", I'd like to change that to "Data" (so that all the copied data gets put here). What references in the above code section do I need to change to affect this?

I tried changing:

Please Login or Register  to view this content.

But that produced errors. Also, I'm not sure what should replace "Dubai". Did I change the correct references, or do I need to alter something else?

[alansidman]

Well, for beginners, if you were running the code with "Dubai" in it and not your own, there would be nothing copied and pasted, unless you had "Dubai" in the specified cell.

Without knowing specifically, what you are looking to copy and from what locations and to where, I am of little help in that area.

[liquidmettle]

Alright, this question could solve the issue: is/was Dubai a named range in the previous sheet you helped with? If so, I can name the range easily. It wasn't a named range in my sheet which may have been why I was confused on what to replace it with.

If you can confirm that is, or is not, the issue, that may solve the problem. I won't be able to test it until I get back into work tomorrow.

[alansidman]

It was not a named range. It was a string that the OP was looking to capture if it occurred in a particular cell.

[liquidmettle]

Hi Alansidman,

Okay so I think that's where we had the confusion.

For my purposes, I don't need to base the data pull on whether there is specific text in a cell. Rather, I want it to pull everything in a specific range of cells from each program workbook.

I tried adding a Dubai string (which to my knowledge should be either just the text dubai or dubai preceeded by a ' ) to cell A2, but other than running through the code with no errors, there was still no data copied.

Is it possible to change this entire section to say "Copy and paste this named range" from every sheet named Reporting in each workbook of the folder?

Unless I also need to change "Master.Sheets" and "Master.Range" to some reference specific to me.

I would dabble but I've not quite gotten the hang of what seem like random references to me (like "A" or "C" in the ranges).

Any thoughts on switching the code from a string to copying a named range from every sheet?

Heck, is it possible that all the current code would be necessary if it was just looking for a specific named range?

Thanks,

-LM

[alansidman]

In my mind the issue is that you have not told us specifically what you want range of data you wish to find and transfer to your main or master workbook. Until you describe this, then there is really nothing anyone can do to help you. I had suspected that you had this code already established and would substitute it and replace this section of code below with your own.

Please Login or Register  to view this content.

What this code does is to look in the opened workbook, loop through column C to the last row and if it finds the word Dubai in column C, then it copies the values in columns H & I for that row to a Master sheet, placing it in the next available row in column A.

If this is not what you want, then please describe in similar fashion and we will attempt to give you some code that works for you. If you look at Post #6, this is what I meant in that post where I told you this section needed to be replaced.

[liquidmettle]

Hi Alan & SHG,

Here is the code as I currently have it:

Please Login or Register  to view this content.

After noticing SHG's suggestion for a directory change, I first got the directory to do what I wanted with his code, then searched and found a different code that avoids the file-picker altogether. I know that this found the proper file-path because it asked if I wanted to re-open the only workbook in the test folder. So that part is settled (and much thanks to both of your contributions).

As far as the Dubai section of the code, my familiarity with VBA coding is growing, but I'm still leagues away from being able to write my own from scratch. I can understand the logic behind a lot of the code, but interpretation is ways away from execution.

What this code does is to look in the opened workbook, loop through column C to the last row and if it finds the word Dubai in column C, then it copies the values in columns H & I for that row to a Master sheet, placing it in the next available row in column A.

If this is not what you want, then please describe in similar fashion and we will attempt to give you some code that works for you. If you look at Post #6, this is what I meant in that post where I told you this section needed to be replaced.

Here's my shot at explanation:

In every workbook I have a block of formulas that pull data from other sheets. This block is 21 columns by 31 rows (the first row is just a title). What I would like this macro to do is to look at all the Excel workbooks in a specified folder (which that part is solved) and copy the block of data from the "Reporting" sheet into the workbook running the macro. Since there will be multiple source workbooks, each block needs to be copied below the previous.

I tried replacing the Dubai section with this code:

Please Login or Register  to view this content.

But other than the macro running with no issues, nothing else happened. I don't require the macro to display the sheet its pasting data into, but I don't know what code to use to copy that block of cells and paste it into the designated workbook. Additionally, I don't know if it will be a problem that there are formulas in that block of cells (really I just need the results of those formulas to paste, but however it can be achieved is fine).


Please let me know if my description is still confusing and I will try a different tack at explaining it.

Thank you for your patience (it is truly appreciated!).

[alansidman]

Try replacing the "Dubai" Section with the following.

Please Login or Register  to view this content.

[liquidmettle]

Hi Alan,

Using that code I received the following error:

Run Time error '438':

Object doesn't support this property or method

The debugger indicated this line of code as the culprit:

Please Login or Register  to view this content.

Any thoughts?

[alansidman]

Yup. I think I forgot to put the sheet name in. My bad.

Try this:

Please Login or Register  to view this content.

this happens when you don't have a worksheet to test the code on. My apologies.

[liquidmettle]

Ah Brilliant!!!

It appears that the code is working perfectly after that minor adjustment!

Thank you so much Alan for your patience and persistence!

One final thought (no worries if its too much hassle), but is there a way to make the code pastes just the values and not the formulas? As I said no worries if this is a major problem. I can work around either solution.

[alansidman]

Try this:

Please Login or Register  to view this content.

[liquidmettle]

Hi Alan,

Works like a charm! I tried something to that effect before I asked you but came up with an error.

Appreciate everything!

Consequently I did solve one final issue on my own where the clipboard was asking if I wanted to save all the information for use in the clipboard.

Overall cheers!

[alansidman]

Glad to be of help. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.