Best way to bring in data from one excel sheet to another?

Can you attach a copy of the base survey, possibly with sample/example data?
That would help us make the answer more complete for you.

Depending on the data layout and the template you could probably use VLOOKUP or INDEX/MATCH. A workbook with some sample data and the template form would help provide a solution

Attached is the template/sheet that will be used on an annual basis going forward
test-sheet.xlsx

I can't see what you want to import or where to

So I copied the sheet into another xlsx file since it's static and not going to be changing from year to year

In the previous year file, I included a line of sample data (all data types are included at the top of the sheet). I could not provide the original, for security reasons. Also note, anything that does not have the 'number' data type at the top is chosen from a drop down menu (not sure if that'll effect anything or not)

I hope this paints a clearer picture. Let me know if you need anything else
current-year.xlsx
previous-year.xlsx

your requirement isn't clear to me - have I got this correct:-


So you want a way to read all the previous years sheets (approx. 300) and show them the data they previously filled? this is not difficult in theory but why not just send them a copy of their previous year sheet as well as the new request?

That's correct. We're just trying to streamline this process so that the amount of work is minimized on both ends. I just wasn't sure if there was a better way to do it than a copy/paste operation or if just providing them the survey from last year was the way to go.

Ideally the less work it appears the customer has to do the better in the end

OK, so working with a  copy of all last years responses in a chosen folder you need a routine that renames the tab to 'previous responses' and puts a new tab for this years response at the front and then saves it - ready for distribution. It should also delete any existing 'previous year' tab (ie: two year old data) if it exists  then you could just copy/execute/re-issue year on year.

The alternative is that they get two files and can look up their old response if they want to open the second one (which doesn't seem too onerous but could lead to them filling the wrong one in and being a tad annoyed).

Is that approach acceptable?

One other thought is that they may just copy and paste last years data - is that a potential issue?

Its near the end of day here so I may not be able to respond for a while

Yeah I really like where you were going with that initial thought in your above reply. Is there any way to automate that process? Doing all of that manually (300 times) would be a huge black hole of time.

The alternative you mention (as well as the copy paste), were methods that I had previously mentioned to my team members.

I don't think any approach is or is not acceptable at this point - we just want all of our options in front of us so we can make the best decision going forward

the code to do that would be relatively straight forward but I didn't want to start something if it doesn't match needs. There would be a couple of limitations to consider:
- is it ok to lock down the workbook structure to prevent modification of the tab names
- what to do if we find a sheet with incorrectly named tabs
- how does it fit into existing process

assuming you can work around these I'll put together an initial attempt

The final workbook will be completely locked down. We will not be allowing customers to modify the workbook structure. My team made this oversight last year, and are implementing that change this year.

There shouldn't be any tabs named incorrectly, we can instruct customers on how to properly submit the data and we can always do a quick double check on our end before running the code.

Can definitely work around all of that :)

here's a first attempt, I'm assuming a certain level of VBA support- ie: someone can open the VBA editor and modify the constants I put at the top; if that's not possible then I can add in some dialogs but it would be good to confirm this is going in the right direction.

I've not done extensive testing but the basic flow is in place.

Copy some of the previous year surveys to a folder and create a new empty folder to receive the changed files.

Put the folder names into the Macro - there are Constants defined in the first few lines- then run the macro

It should run through all the files, add the new survey request, rename the old Tab to 'Previous' and save it in the new folder - the originals should not be updated

However I've had to make assumptions about file and tab names

 - I've tried to show where in the comments so you can adjust as needed but get back to me if you don't have vba support

- there is no check for duplicate files in the target directory (so running it a second time will fail unless you empty it)

- there are links in the control sheet which won't resolve on my pc, i can suppress them if required but assumed they might be relevant to what you are doing and they will update on your machine. For testing purposes you can just update or not as needed if the dialog pops up
nflynn-iteration-1.xlsm

WOW thank you for that! I can somewhat follow along with what you have here. It's been a long time since I've touched VBA code (college...10 or so years)

I will make some edits and see if I can get things working

here's a second iteration  - a bit more robust on the error handling and tested with a few more files.

Setup is the same in terms of previous/current folders and files but you can now quote the folder names, current year and  what the sheets are actually called on the control sheet. - I've highlighted the boxes to fill in so you don't need to edit the vba

So, for example, if your previous surveys all use 'Sheet1', you can put that in the 'Previous sheet name' and choose what you want to call this years tab in 'Current Sheet name' - it doesn't check that the tabnames are valid and the routine will fail if you put a bad one in, I can add that if you want

You will have to give me a steer on how to handle the file names, at the moment its just appending  the current year on the end of the previous file so if you re-use next year it will be survey346 2015 2016 and so on
nflynn-iteration-2.xlsm

Nice touch with just having to update the control to get the updates in the VBA!

Is there any way to have this set up so that when it runs, it isn't dependent on what the previous tab name is or was? I think it's great how it spits out a new file and gives you current and previous year's data. Exactly what i'm looking for.

As far as the file name is concerned, I think it will become cumbersome if we have to keep modifying it like that (I feel like we'd have to manually go in there and remove it, which we don't want). Is there a way to strip the previous years stamp/date, and add the current year's date?

This great, thank you so much

Those are the kind of problems that look simple but will need a bit of discussion:-

As it's written at the moment it can be run year on year and will keep moving current to previous but has the issues you point out, these are a result of trying to convert the current format to a new format whilst being usable for subsequent years (so we have to deal with both formats in the same routine):
- it needs to know which of the two tabs is the 'old' previous and which is the 'last year' entry and to do that it uses the sheet names. If we don't have clarity on those then it can't decide which to remove
- the filename obviously needs work but we have to get over the fact that the current filenames don't match what we are moving to so the routine has to deal with today's format AND the format that will exist next year.

I see two possible ways forward:-
NB: the filename in the sample is obviously an example so I would need confirmed filename formats (what it is and what you would like it to be) and what Tab Name I should change it to


Alternatively we leave the filenames as they are and use dates in the tab names:-

This will reduce the number of full copies and keep the filenames consistent for audit purposes but the drawback is that you will need to open the file to see if its a new or old format (which is why I added the year).

Let me know which way you would like to go

I definitely like the second route the best - I definitely like the idea of not having to edit the file name on a year to year basis but doing it internally to the sheet would seem like the most logical way to handle it

This is incredible. Thanks so much for your help with this! Great Job!

I did some testing and it is working as expected. Simple and to the point. Works like a champ.

Glad it does what you need, I was tidying some stuff up so might as well post the final version :)
nflynn-iteration-5.xlsm