create an info input tab to transfer new information into other sheets?

[Keegan1116]

Hi there,

I'm not looking for specific coding or anything like that, but rather, I want to know how I should go about creating this. I've created a project management workbook to track all of my projects, each of which include a checklist of things to do.

It has become quite cumbersome to copy & paste new templates in and input all of the information every time, so I'm wondering if there is a way that I can create a sheet with just one existing template, where I type all the information into and it gets automatically transferred into my project management tab and then the template information subsequently erased so that I can repeat this process again and again?

Basically I want this tab to:
1. Copy & Paste new template 2 rows beneath current projects (for visability)
2. Automatically transfer all data from the input tab
3. Erase entered data from input tab so that it can be done again and again

Thanks for any and all help in advance.

[Mumps1]

This certainly is possible by inserting a new sheet that will act as the input template. After the template is populated, the data can be copied to the project management tab and then cleared to be ready for more input. If you attach a copy of your file (de-sensitized if necessary) we could help with some coding.

[Keegan1116]

That would be great to help me get going! I have a macro included in this spreadsheet that copies the template from "Template to copy" and pastes it into "Projects" below my current work. I did not write this macro but rather got it from someone else on this forum, with their help and a couple of tried it finally works. But I've no idea where to start with the whole Input Template that copies information over into my Project sheet.

[Mumps1]

I still don't quite follow. The macro you currently have copies "Template to copy" to "Projects". I'm not sure what the purpose of the Input Template is. Is "Template to copy" your actual template where you will be inputting the data? My understanding was that you would have 2 sheet only, the template and the Projects. You would fill in the template, copy it to the Projects and then clear the template. If that is the case, your current macro does that. Please clarify.

[Keegan1116]

I wanted to input the information into the "Input Template" sheet, then have that information automatically copy & paste into the "Projects" sheet, then reset the "Input Template" sheet so I can do it again. The macro I have already written is really only doing 1/3rd of that, but it was a start. I certainly don't need that macro - The end goal is and always has been just about the "Input Template" and "Project" sheets. Hope that clears things up!

[Mumps1]

Try:

Please Login or Register  to view this content.

[Keegan1116]

PERFECT! This worked first try!!! Thanks so much for writing that. There's one little thing I Want to add, and that is the checklist at the bottom of the template and to also have it copy over to the "Projects" tab when I run the macro. So what I'm wondering is: What part of this code can I change to instead, copy the range A2:W73, and then proceed to copy that over to the "Projects" tab?

[Mumps1]

This part of the code already copies A2:W73 to the "Projects" tab. Is it not doing that?

Please Login or Register  to view this content.

[Keegan1116]

Yes it does, sorry. I just got the chance to test it out again - I see it it erasing all of the data from the Input Template sheet when I run the macro, when in fact I'm just trying to erase the header; The checklist remains the same throughout all projects. I thought I would just be able to easily add an additional copy & paste of this checklist beneath every project header (after running the macro), but it seems its not so simple after all

[Mumps1]

What are the ranges in the Input Template sheet that you want to erase?

[Keegan1116]

Oh boy... I know I'm asking a lot here... But:

I basically want to copy the entire thing to "Projects", and only erase or 'reset', if you will, cells C2:L2 as well as O2, and P4:P29. Lol!

[Mumps1]

Try:

Please Login or Register  to view this content.

You have some merged ranges in your sheet. You should avoid merged cells at all cost because they almost always create problems for macros. Hopefully, this macro will work without any errors.

[Keegan1116]

I'll give this a shot tomorrow at work, thanks! I can't remove the merged cells because that would involve starting from scratch basically and re-inputting all formulas and conditional formatting - I'll let you know how it goes!!

[Keegan1116]

Hey there,

So I tried the code out, and you're right. I'm getting a:

Rum-Time error '1004': Cannot change part of a merged cell.

What exactly does this mean? Why would having some merged cells create a problem for this?

[Mumps1]

This link describes very well why you should not use merged cells: https://www.perfectxl.com/about-spre...t-merge-cells/

Do a little research into "CenterAcrossSelection". This gives you the same visual effect as merging cells without actual merging them. This only works with horizontal ranges not vertical so you should not merge cells vertically.

[Keegan1116]

Hmm that is very interesting. I can certainly fix a lot of my merged cells with center across selection, however, if I'm to do that for all the merged cells in my cheer, it is going to mess up my conditional formatting. All of my 'Action Required' cells are formatted to highlight their cells to certain colors if actions are needed, and with this, only the first cell is highlighted. If I can find a way to highlight all 4 cells in the row, then this could work!

[Mumps1]

Unfortunately, unmerging is the only way to fix the problem.