Copy range of data within sheet and rename based on cell value paste values and format

Hello everybody. I'm new to the forum and would appreciate some help. I have a code which allows me to copy sheet and rename based on cell value:

Please Login or Register  to view this content.

I'd like for it to only copy a specific range of data from the sheet and paste only values and format (not formulas) into the newly created sheet.

In addition to that, if I'm trying to copy a sheet with the same name twice, I'm getting an error. Is there any possibility to add a number to the newly created sheet name in addition to the value/name (C3:C4 above) if there's already a sheet with the existing name?

Also, for some reason, the text within my ActiveX-buttons increases in size when I click on them. Any idea why that is and how to fix it?

Thanks in advance.

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file. What specific range do you want to copy?

Try:

Please Login or Register  to view this content.

I don't see any formulas in columns F to T. When I click on any cell in these columns, the formula bar displays a zero. As far as the form button problem is concerned, I would delete them and replace them with a shape (Insert...Shapes). You can format the shapes to make them look attractive and assign the macros to them. Place the corresponding macros in a regular module instead of the sheet code module. Also your link in Post #6 doesn't work. See the yellow banner at the top for instructions on how to attach a file.

Originally Posted by Mumps1

I don't see any formulas in columns F to T. When I click on any cell in these columns, the formula bar displays a zero. As far as the form button problem is concerned, I would delete them and replace them with a shape (Insert...Shapes). You can format the shapes to make them look attractive and assign the macros to them. Place the corresponding macros in a regular module instead of the sheet code module. Also your link in Post #6 doesn't work. See the yellow banner at the top for instructions on how to attach a file.

Thanks for the response, appreciate it. As for the formulas, that is strange. I've attached a picture showing that there're formulas in those cells. I've also uploaded the file again, maybe they're visible this time.

As for the buttons, I'll give it a try. I think I managed to do it.

EDIT: I'm noticing that giving the "Save" sheet the name of cells C3:C4 might be to long at times. Is there an easy way to rewrite the code so that it names the saved sheets as C3 only and if there's already an existing sheet with that particular name then just add a number starting with 1 instead? Sorry for all the questions.

Try the attached file. I have changed the buttons to shapes and moved the macros to Module1. Everything seems to be working properly. You may want to look at the "Clear" macro. You have it set to clear the contents of AA3 and AA12. Column AA has no data in it. Perhaps the column in the code needs to be changed.

Originally Posted by Mumps1

Try the attached file. I have changed the buttons to shapes and moved the macros to Module1. Everything seems to be working properly. You may want to look at the "Clear" macro. You have it set to clear the contents of AA3 and AA12. Column AA has no data in it. Perhaps the column in the code needs to be changed.

Thanks, mate. AA should be changed to AB, I'll change it.

But the Copy button still doesn't function properly for some reason. Columns F:T don't work as they should. In the PLANNER sheet, they work properly. When you change something in column C and/or D, the same row for column F:T changes accordingly. But when I make a copy and re-do the same thing, the values in column F:T don't change. Very strange.

Even trying a simpler COPY macro like this doesn't seem to work. So I don't think it's something wrong with the macro. For some reason column F:T don't pick up what they should when the sheet is copied

Please Login or Register  to view this content.

I am not experiencing the same problem. The Copy macro works properly for me, copying the formulas into the new sheets. I'm wondering if the problem is with the version of Excel that you are using. Can you try the file on a computer with an earlier version of Excel?

Is it a simple fix to change the name of the Save sheet as I suggested above (value C3+n if there's already an existing sheet with the same name instead of C3+C4? Thanks again, mate

.
Could you give me a couple of examples?

Originally Posted by Mumps1

.
Could you give me a couple of examples?

This is the current code you wrote for me

Please Login or Register  to view this content.

The red part is what I want re-done. Basically, if the name Gareth Williams is written in C3, and DMC is written in C4, the saved sheet is going to be named Gareth Williams DMC. I would like it to be named Gareth Williams only instead. BUT, if there's a previous sheet named Gareth Williams I'd like it to be named Gareth Williams 1 instead and so on. Maybe just deleting the red part will do?

The previous problem was as I suspected. I moved all the data to the PLANNER sheet and everything seems to work now. Thank you for all your help.

EDIT:
I managed to make it work I think by changing the line

Please Login or Register  to view this content.

Seems OK?

Experiencing another problem with the Save button now that I've moved all the data from the INPUT DATA sheet to the PLANNER sheet. See attached picture. For some reason, the macro doesnt seem to copy the format in the highlighted area when I click the Save button. Also noticed that if I change the height of row 11, the saved sheet doesn't register it. I've attached the file with all the data moved to the PLANNER sheet as well.

Sorry to be bothering you with all this. I highly appreciate your help.

Try the attached file. I have fixed all the issues except the copying of the formatting. You are using conditional formatting for the range F3:T10 in the PLANNER sheet. The formulae you use in the conditional formatting rules refer to column AR in the PLANNER sheet. The SAVE button copies only A1:Z35. Therefore the newly created sheets don't include the data in the AR column which is necessary for the conditional formatting to work.

Originally Posted by Mumps1

Try the attached file. I have fixed all the issues except the copying of the formatting. You are using conditional formatting for the range F3:T10 in the PLANNER sheet. The formulae you use in the conditional formatting rules refer to column AR in the PLANNER sheet. The SAVE button copies only A1:Z35. Therefore the newly created sheets don't include the data in the AR column which is necessary for the conditional formatting to work.

Thanks, I'll check tonight when I have access to a computer. I also thought that the problem might be connected to conditional formatting. But in the very first file you helped me with before I moved all the underlying data to the PLANNER sheet, the data was in the INPUT DATA sheet while the SAVE button was still only copying A:Z from the PLANNER sheet. But it still worked back then eventhough the underlying formulae wasn't within the columns that were copied trough the SAVE button. Any idea why it worked back then but not now? That makes me wonder if that really is the issue here. Have a look yourself in the first file in my first comments.

Edit: is there any paste special conditional formatting code? I see it's discussed here https://stackoverflow.com/questions/28388350/copy-conditional-formatting-from-one-cell-to-another-using-vba

But then again, this wasn't an issue in the initial file.

In your original file you had the conditional formatting formulae referring to data in the INPUT DATA sheet and the formulae included a reference to the INPUT DATA sheet. So when the range was copied, the formulae still referred to the INPUT DATA sheet. Now the formulae refer to data in the PLANNER sheet which is not copied over. If you include the reference to the PLANNER sheet in the formulae, it should work.

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

You are very welcome. You may find the following links useful:
https://www.mrexcel.com/forum/lounge...ml#post5235342
http://www.snb-vba.eu/index_en.html
https://excelmacromastery.com/vba-dictionary/
https://excelmacromastery.com/excel-vba-array/

Good luck with it and enjoy.

Have a look at this link: https://www.mrexcel.com/board/thread...hidden.816006/

Thanks, but I don't think that was what I was looking for. As I SAVE the PLANNER sheet, it creates a new sheet. The PLANNER sheet protection doesn't apply to the newly SAVED sheet. Is there a way for the protection and password to pass on from the PLANNER to the newly SAVED sheet as well is my question I guess.

For example, when I use the COPY button, the protection from the initial PLANNER sheet copies over to the new COPY sheet. But when I use the SAVE button, the protection from the PLANNER sheet doesn't copy over to the new SAVE sheet.

The difference between the two (COPY and SAVE) is that the former copies everything including formulaes, while the latter pastes special (values and format).

EDIT
I think I managed to solve my problem. I added the highlighted row to the code

Please Login or Register  to view this content.

Looks good.