Macro Wanted for looping thru worksheets in one workbook and pasting to another workbook!!

In the context of my original post the NIC AL data masterfile is the "datafile". It seems mislabeled because it says masterfile in the name. It is indeed where I collect all the data of a certain variety though, so this is confusing and poorly worded by me. The idea is that the blank document will pull data from several sources and it will become the "Masterfile". I realize it doesn't appear like much of a masterfile at the moment but once is retrieves all its requested data (hopefully, through vba) it will be one.

The NIC vs Ventas Comparison is the Masterfile. And to make put things into a little bit better context, it is a masterfile in which a user creates a customized analysis of data. (Given what I have available to them)

So what I'd like to be able to accomplish is the following. In the NIC vs Ventas Comparison file (Masterfile) the user defines what type of data they want to choose from (AL, IL, SNF; these are all different data categories). I've only included one type of category because I think I can figure out how to do it for the rest of them once I've figured out how to do it for the first. (so the only thing you can type into the dialogue box for "Care Mix" right now is "AL") Then the user chooses how far back he or she wants to run the analysis for. So the user responds with a number and that's how many quarters of data will be pulled from the datafile into the now blank masterfile.

Once the data is populated into the masterfile, then I will run more code to manipulate the data to my specifications. The big problem I'm having though is getting the data from the datafile to be pulled into the masterfile based on the user's specifications.

So for instance, if you run the macro and you say that you want to analyze AL data (which is all that can be done given what I've provided) and you want to analyze it going back 4 quarters. Then the macro will select the AL datafile, then it will run a loop that pulls the last 4 quarters into the masterfile which will in turn be manipulated further later on. I'm just having difficulty with the data pulling.

If you have any help on that, it would be great. Thanks for the response by the way!

Oh yes, and one more thing. There are a few macros in those two documents. The one that I'm referring to in all of this is the one labeled "Orange." Thanks again.

So for instance, if you run the macro and you say that you want to analyze AL data (which is all that can be done given what I've provided) and you want to analyze it going back 4 quarters. Then the macro will select the AL datafile, then it will run a loop that pulls the last 4 quarters into the masterfile which will in turn be manipulated further later on.

Okay. So do you want the whole (all the data in) quarter-worksheet added to the master file?

Where in the master file should it be added?

Do you want to copy the whole quarter-worksheets into quarter-worksheets in the master file?

Or does all the data get copied into one worksheet?

All good questions. Sorry for being a bit unclear as to my intentions.

I don't want the whole quarter-worksheet added into the masterfile, but I want the ability to do so. Say for instance I have 10 quarters of data and the user specifies that they want to analyze 10 quarters. Then it would pull each quarter into the master file. If, however, the user specifies 1, 4 or 8, etc... quarters then it should only pull in the specified amount.

So let's say that the user decided they wanted to analyze the last four quarters, then the macro would pull the worksheet with the most recent quarter's data into the masterfile to the end of the workbook. Then it would pull the next most recent quarter's data into a worksheet AFTER the most recent. And so on, until it has pulled all the requested data. I envisioned this to work much like when you right click on a tab name in a Workbook, and then select "Move or Copy..." You can have Excel copy that worksheet to the end of all the worksheets in another workbook.

So each quarter of data will have it's own worksheet in the new workbook. And moving from left to right across those data pulls, it would be the most recent quarters on the left and the oldest quarters on the right.

I realized that I didn't have an updated datafile to reflect the most recent quarter. The "Orange" macro really only works if you've updated the data files so I'm working on updating that and re-posting the attached docs so you can see more clearly what I'm talking about. I will hopefully be done with that shortly.

Okay, so I did a quick update of the attached docs. Please play around with the Orange Macro. The closest I've gotten to it doing what I want is to repeatedly paste the most recent quarter data into the Masterfile for the number of times that the user specifies. If we can somehow get it to copy the most recent quarter, paste into the masterfile, then move to the next quarter and do the same, and repeat for as many times as necessary then that would be outstanding.

Also, you can see my amateurish attempt in the code to do so. For some reason it won't even do the repeated paste right now, but I'm not sure why. But either way, you can kinda see the direction I was trying to get to in the code.

See: eduardito_NIC AL data Masterfile.xlsm
Run macro: CopySheets
Click: Open File (open any workbook where you want to copy sheets into it.)
Click on one or more sheets in the list.
Click: Copy Sheets

See if this works for you.

I had problems understanding you code, so I took a totally different approach.

If you like it, I can help you modify the code until it works for your needs.

If you don't like it, I can go back and look at your code again, but a number of things were not clear to me.

Let me know what you think.

It looks very promising. I'm not sure if it works quite right just yet and I'll have to be playing around with it at work. It looks like it will pull one or two sheets but, I can't get it to pull all the requested ones.

One thing I've noticed that I will have to change is that the workbook that everything originates is a datafile. And so that exact file won't necessarily be running depending on what the user selects to run an analysis from in the future.

My hope is that the entire macro is within the file "Copy of NIC vs Ventas Comparison." The macro in this file should request information from the data sources, such as the NIC AL data Masterfile.

Can you try to run the macro as it is, from the data file to be copied onto "Copy of NIC vs Ventas Comparison" and see if you're having any of the issues that I"m seeing. Some of the worksheets copy as blank to the left of the "Comp Macro" tab and other ones copy to the right of the "Comp Macro" tab, as they should.

Also, thanks so much for taking the time to help me out. I think you are right on the right track.

I found my error.

The line towards the bottom of cbCopySheets_Click where I add a new worksheet needs to be changed to this:

Please Login or Register  to view this content.

One thing I've noticed that I will have to change is that the workbook that everything originates is a datafile. And so that exact file won't necessarily be running depending on what the user selects to run an analysis from in the future.

I wondered about that problem myself. Actually, this userform only does three things:
(a) it opens a workbook;
(b) it lists all the worksheets (in this case, all but one, the MicroTab is not listed) in a workbook; and
(c) it copies worksheets from one workbook to another.

It would be an easy matter to re-write the code to list all the worksheets of the file opened so that it could copy worksheets from that workbook to the workbook where the userform resides.

Is it possible to send an edit, where the macro originates in the file NIC vs Ventas Comparison? And from that file the macro pulls from the NIC AL data masterfile to the comparison file?

I'm attaching two more excel docs.

One is called "Copy of Macro Test." This of this as the destination file where the data would be pulled to.
One is called Copy of NIC AL data Masterfile. If you look in it, please check out module #2. It has a list of copy/paste subs which I'm trying to get to pull a certain number of tabs in the Copy of Macro Test file. I can get the loop to repeat for the specified number of times (which later I'll assign a variable to) but for the life of me I can't get the loop to select a sheet, copy that sheet, paste that sheet into the destination file, and then select the next most recent sheet. It has stumped me.

Here is some example code. Hopefully it is more illustrative of what I'm attempt to accomplish. But you can see more of it in the attachments (The code doesn't reference the name "Copy of XYZ file" so you may have to toss that in there because in writing the macros quickly I haven't assigned the workbooks to variables). Any help would be greatly appreciated. I will be looking more closely at your code which you have provided tonight as well. Thank for all the help thus far.

Please Login or Register  to view this content.

Here is another piece of code that isn't quite there

Please Login or Register  to view this content.

And one more

Please Login or Register  to view this content.

In all of these pieces of code, all I'm really trying to accomplish is that the loop will copy one worksheet (which is the newest market data) paste it to the end of the designated workbook, and then select the 2nd newest market data worksheet and repeat until it has done so for however many sheets as the user has requested. If we can get anything that does that, I will be ecstatic. Thanks again for hanging in there with me.

Well I've been playing around with your code, and it definitely works. Wow. Very impressive. If you have any extra time sometime it would be great if you could walk me through a little bit of the mechanics of the code so that I can try to replicate this at a future date for different situation/scenarios I come across.

I have one last request StevenM. I'm hoping you don't mind helping me out, you have been very helpful thus far. Thank you so much.

So I've been playing around with your code. It's bit hard understand as it is quite advanced. Is there anyway that you could add some comments into the code itself. Especially the code from the Userform. I'm interested in your thought process for deciding how to go about the solution.

Also, I have put your code into the "comparison" file (Moving from "NIC AL data Masterfile" to "NIC vs Ventas Comparison" file. Originally, it was in the masterdata file. When I run the code, it only lists the tabs that are in the "comparison" file: "Comp Macro", Sheet1, And Sheet2. If you could somehow show me how to amend the code so that it will run in the "comparison file" and then you can choose to open a masterdata file (and select the tabs within) that would really help me out.

Right now I've only included one master data file in this original posting, but there are several others. For instance, in addition to the "NIC AL master data file" there is a NIC IL master data file" and so on. If you could help me one last time so that the code will run in the comparison file and pull the tabs from these files it would be perfect. Thanks again.

See: Copy_Sheets.xlsm

This macro is set to copy worksheets from another file to this workbook.

I've added notes to the code, but if you have any questions, just ask.

Most of the code is underneath the userform. (Double click on the userform.)

I haven't really tested it, but I assume that you can open more than one file one after the other (but it will only copy sheets from the last file it opens).

Yes, this works exactly as needed! I'm going to be studying the differences between the two code setups to figure out how you changed from one version of the code to the next. Thank you so much for all the input. You are by far the most helpful person I've ever encountered on one of these forums.

On a side note...

If it is at all possible, is there any way I could message you on this forum if I have other things that come along and stump me as I build this macro? This is the biggest hurdle I think I'll run into, and I think I'm going to be able to run with this for some distance, but I'm sure that I will have pieces of code here and there that just flat out stump me again.

I would understand if you felt that you've expended too much time on me already. It would just be really great to be able to learn more from you if I need to. Thanks again.

You can send me a private message via this forum. Or you can e-mail me.