Macro to mirror(copy) present sheet to new

[Larbec]

I want to copy this spread sheet to a new sheet by clicking on a button or new tab using a Macro. Is this possible or is it asking for a lot or impossible to do?

Thanks

[Kaper]

How about simple macro assigned to a button (I assumed there is no other buttons as in a sample):

Please Login or Register  to view this content.

See attachment

[Larbec]

How about simple macro assigned to a button (I assumed there is no other buttons as in a sample):

Please Login or Register  to view this content.

See attachment

OMG that is exactly what I was looking to do Kaper. THANK YOU!!!!!!

As a suggesting from a board member he suggested I break down all I needed. I've posted before and received no replies probably because I made it seem more complicated than it was plus I do not know all the terminology like you all do but I am TRYING to learn.

This was part of it. Now, here is the next most important part ..... how can I get this Macro to count "down" a row in columns A,C:F L,N:Q and W, Y:AB

For example: Next copy A3:A32 goes from 2978 to 2977 the other columns listed above do the same (the yellow/gray cells and ones in blue)

Can the Button be copied and active too? I will be adding a new Game sheet twice a week. I tried by deleted the ".Delete" in the Macro and it does work but gives me an error

[Kaper]

2) delete whole line: ActiveSheet....Delete
1) probably yes. Try to show expected changes. I do not see 2978 in a3:a32

[Larbec]

2) delete whole line: ActiveSheet....Delete
1) probably yes. Try to show expected changes. I do not see 2978 in a3:a32

Thanks for the above statement .

Here is a copy of two sheets if you look at the columns as a whole and then the row numbers you will see a difference between both sheets what needs to change.
It is a lot but then again its not much in the grand scheme of things, I will explain also. THANK YOU for helping ,
this will save me 4 hours of time each time I have to make a new sheet plus mistakes that will not be made using a Macro

Ive written details on the spreadsheet and also in this snip again THANK YOU!!!!

[Larbec]

2) delete whole line: ActiveSheet....Delete
1) probably yes. Try to show expected changes. I do not see 2978 in a3:a32

Kaper,

I am trying to use the Maxro in another worksheet and can not get it to run without error even after changing the Game Number in the Macro. Can you look at my tabs and tell me what Needs to change?

Thanks

Tab layout.png

[Kaper]

As for sheet names - do you write them in code exactly as written in the "tab"?

Anyway we can refer to cativesheet (the one with a button) as ActiveSheet, not just by name.

As for changing formulas two ideas come to my mind.
Either sequence:
- create a new sheet (not copy old one, just insert new)
- copy formulas from old sheet into new one 1 row below original position
- copy all cells A2:AG34 to regular positions (A1:AG33) - formulas will adjust their rows by -1
- copy from old sheet columns widths to new one, and finally
- copy formatting
Or the below one
- create copy
- went through cells with formulas
- separate address from lst few characters and change it into 1 row above original)

it is achieved with the following macro

Please Login or Register  to view this content.

If that works as expected (I could not easily and fully test, because formulas refer to external sheet), you can delete comment mark (apostrophe) from button deletion line and try to make 10 consequtive copies of games just by calling

Please Login or Register  to view this content.

Anticipating next question: yes, numbers in cells A32, L1 etc could be changed similar way to row numbers in formulas.

[Larbec]

WOW!!! You are AMAZING!!! It will take me a little while to check this out as when all books are open its around 8 GIGS plus it it trying to reference a sheet that I took the Game sheet from which is a 100 Game book. This new book is 3000 Games in a book so I need to re direct what its trying to do. I can see the Macro trying to find the linked books (-:

I think my sheet reference error came from me not putting a space after the word Game after looking closely

Again, THANK YOU SO MUCH and I will report back once it does its thing

[Larbec]

I'm receiving a error message "created sheet name" when I first run the Macro
Is it due to having a different tab name than my example?


Created sheet name.png


Row Miscount.png

[Larbec]

This keeps popping up asking me to update values. Do I assume it wants them from the Input Sheet (the 1 the Macro is running on)? To copy one sheet it popped up 9 times. After it runs I will try to do 10 sheets. If there is any way to get this to "not" pop up that would be great so I can let this run and go about my business. If not, no biggie, Ill do some reading lol. I am so blessed to have someone like you to do this for me


Update Values.png

[Larbec]

I have the linking figured out. I will keep you updated again THANK YOU!!!!

[Larbec]

Ran 1 sheet and THANK YOU THANK YOU THANK YOU!!!! It all ran PERFECT!!!!

Going for 10 sheets

[Larbec]

As for sheet names - do you write them in code exactly as written in the "tab"?

Anyway we can refer to cativesheet (the one with a button) as ActiveSheet, not just by name.

As for changing formulas two ideas come to my mind.
Either sequence:
- create a new sheet (not copy old one, just insert new)
- copy formulas from old sheet into new one 1 row below original position
- copy all cells A2:AG34 to regular positions (A1:AG33) - formulas will adjust their rows by -1
- copy from old sheet columns widths to new one, and finally
- copy formatting
Or the below one
- create copy
- went through cells with formulas
- separate address from lst few characters and change it into 1 row above original)

it is achieved with the following macro

Please Login or Register  to view this content.

If that works as expected (I could not easily and fully test, because formulas refer to external sheet), you can delete comment mark (apostrophe) from button deletion line and try to make 10 consequtive copies of games just by calling

Please Login or Register  to view this content.

Anticipating next question: yes, numbers in cells A32, L1 etc could be changed similar way to row numbers in formulas.

How did you know lol Thank you

[Larbec]

Kaper,

I ran the 10 sheets and it ran them The only issue I saw was the box that kept popping up looking to update. All I would have to do is click on Input Sheet then ok Is there a way to get it to pop up?

Findings after Running single sheet and 10 sheets

* box pops up "please check created sheet name" (can it be set to start on any game number or sheet? I will be putting this Macro into different books that will start with a different Game #)

* Update Values box pops up too often (I'll recheck to ensure auto calculating is on but not sure if that's what is causing this)

* add rows 32 & 35 ( do I just change (for i = 3 to 35) from (for i = 3 to 31)?

Now this is a nice to have if it can be added to Macro but you've spent a lot of time with this so I do not want to over step your generosity if this is a big deal to do

* can the Macro when adding/copying a new sheet automatically put the next Game Number in its tab and color code Green for Even Game Numbers and Red for Odd?

*cell B25 controls my Game numbers in M1, M9, M17, M27, M33, X1, X9, X17, X27, X30, X33. When B25 changes ALL the others cells with CF change automatically. So the Macro would only have to deal with cell B25 to have it advance to the "next" game number up These cells are crucial in my calculations as they are used as references Can this be added?

That completes my project (-: I can't thank you enough You are an AMAZING programmer and I appreciate you taking your much valuable time and wealth of knowledge to help me with this

[Kaper]

So, what is current stage?
As for window with sheetname: if the sample is not representative (hash instead of space) the code does not know about it and reacts on wrong data.
As for referring to external data may be adding

Please Login or Register  to view this content.

will help.
Rows A29:W35 ???
Again different than sample.
As for private messages - see point 4 of the forum rules:
Don't Private Message or email Excel questions to moderators or other members. (or Word, Access, etc.) The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
Nobody else would benefit from solution provided by private message (appropriate approach with private consultations, but not public forum).

edit: as we were writing parallely some parts were already answered. other probably could be done, but I think it would be better to work on current version ofn the file.

[Larbec]

Current stage is what I put in post#14

In post #7 you mention Anticipating next question: yes, numbers in cells A32, L1 etc could be changed similar way to row numbers in formulas. There is also A35, L

I believe the only difference between the current version and one I am putting in my large books are the game numbers and where I am presently. My small books have from Game#40 to Game#50, The large books start at Game#3000 to Game#1. Everything else is the same. I am truly sorry for any and all confusion on my part.

Rule point #4 will never happen again, my apologies

Are you telling me to add this at the end of the Macro I presently have?

Please Login or Register  to view this content.

Thanks!

[Kaper]

As for this small piece of code:
No, replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Post #14 - but there is only text, no file .

[Larbec]

Ignore this, I just saw your update



I added the additional code and get an error

Please Login or Register  to view this content.

Else and If statement.png

[Larbec]

As for this small piece of code:
No, replace:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Post #14 - but there is only text, no file .

Sorry forgot, here is present / actual Game#

[Larbec]

After updating all the code and running it I get a box that pops up and says please check formulas? Dies this mean I have bad formulas in my spreadsheet as far as my conditional formatting or inside the actual cells?

Please check formulas.png

I can see in these cells after it ran that it has errors in G11:H22. Is that what this box is referring to?

pick of errors.png

[Larbec]

Update on running 10 sheets
It's been running now for 14 hours and about every 45 minutes the box pops up "please check formulas probably there are 18 of them". Could be 12, 16 etc... Hopefully there is a way around this box popping up. If it can be faster great if not I don't mind letting it run to do 10 sheets as long as I'm not babysitting it. These 14 hours may only be 10 due to I'm not watching my pc I'm running a solid state drive with only excel on it , 7 core 64 bit, 16 GIGS I'm very grateful for your help

[Kaper]

1) Sheet names - you have a space following the number (Game# 1448 ) not (Game# 1448) as expected by algorithm. Delete it. Solved.
2) Skipped formulas. Indeed, you changed some of them. There was no formula like =IF(condition,truepart,xxxxx-1) before. Of course procedure is highly dedicated, and could not deal with such changed formula properly. I added this functionality and re-introduced msgboxes. They popup when user intervention IS required, because for instance formula points "out of the sheet boundaries", etc.
3) B25 incrementation - added (as expected :-P)
4) On my "nothing special" machine (slow i5, 4GBRAM, Win7 32, Ex2010 32, HDD, other programs running) the updated file generates/prepares new sheet in some 10 seconds. May be because I do not have other files (source data). Anyway - try it.

The code:

Please Login or Register  to view this content.

[Larbec]

Kaper,

This is everything I wanted and more. I can't thank you enough EXCELLENT and THANK YOU THANK YOU THANK YOU!!!

How can I remove the error message box? I like it but would only like to run that on new spreadsheets introduced. Whats its picking up is my other games that are hidden which is causing the Macro to stop each time for me to answer and click OK.

Thanks

[Kaper]

Just delete, or better comment-out (put apostrophe at the beginning) the line

Please Login or Register  to view this content.

[Larbec]

Just delete, or better comment-out (put apostrophe at the beginning) the line

Please Login or Register  to view this content.

Excellent idea to comment out. I can't thank you enough Kaper. I've ask many others for help and you've been the only one that could master the task I'm in Awe (-:

[Larbec]

I am embarrassed to even ask, can you please put the apostrophe around them and show me where to "comment out" that please. I have tried and must be something wrong. Macro runs GREAT!!!!

BTW, I ran 10 sheets in about 45 seconds (-: but it gave me --- instead of advancing the number perhaps because I started with Game#249 and not like Game#2490

Thanks


dashes and not advance number.png

[Kaper]

1)

Please Login or Register  to view this content.

2)
Problems with numbering: if I can see there was again no space before number >Game#249< while code expects >Game# 249< (>< signs used just to emphasize that there is no space before and especially after the number).

[Larbec]

1)

Please Login or Register  to view this content.

2)
Problems with numbering: if I can see there was again no space before number >Game#249< while code expects >Game# 249< (>< signs used just to emphasize that there is no space before and especially after the number).

Thank you Kaper. I've put the spacing in my notes