How to create new sheets and cell formulas based on variables?

[BillPeterson]

I create a new tab every month for keeping track of credit card expenses. I do this by copying the latest sheet, and then manually editing several cells to get ready for the next cycle. Instead of copying the "June" tab and ending up with "June (2)", I'd like it to automatically rename it to "July". I decided to at least make it generic for now, and the code below works, but then of course I have to manually change "XX" to "23" (i.e. current year), and then "xxx" to "Jul" (i.e. current month). Would be great to create a variable for these such that it just extracts the current month and year from a formula like "today()" and then renames the tab to the next month. Any suggestions?

    Sheets("CCXXxxx").Select
    Sheets("CCXXxxx").Copy Before:=Sheets(1)
    Sheets("CCXXxxx (2)").Select
    Sheets("CCXXxxx (2)").Name = "CCYYxxx"

Thanks in advance for your help! You guys are geniuses

 

[Joe4]

Try something like:

Sheets("CCXXxxx (2)").Name = Format(Date, "mmmyy")

or replace the "mmmyy" part with your desired date format.

 

[BillPeterson]

Try something like:

Sheets("CCXXxxx (2)").Name = Format(Date, "mmmyy")

or replace the "mmmyy" part with your desired date format.

Ok I can see what that's doing. Thank you. So how to increment it so it's the following month instead of the current month? Also, how do I add a prefix to the name?

i.e. instead of "23jun" I want "CC23Jul"

Thanks again for the help!

 

[Joe4]

Try this:

Sheets("CCXXxxx (2)").Name = "CC" & Format(DateAdd("m", 1, Date), "mmmyy")

If you pick it apart piece-by-piece, it should make sense what is going on.

 

[BillPeterson]

Perfect. Thank you!

Ok, so my next question is how to update certain formulas now that they are copied from the previous month's sheet. For example, June's sheet has a cell with the following formula:

=CC23May!B1+A1

And I want to automatically update this to increment to point to the previous month.... i.e. the CC23Jul sheet should now point to the June tab (shown below) instead of May's (shown above):

=CC23Jun!B1+A1

 

[Joe4]

Note that:

=CC23May!B1+A1

will add B1 from the "CC23May" sheet with A1 from the current sheet.
If your intention is to add B1 from the "CC23May" sheet to A1 of the "CC23May" sheet, your formula would need to look like:

=CC23May!B1+CC23May!B1A1

Not really sure which way you really intend there.

What I would do is put a cell on your sheet where you calculate the sheet name you want to return, i.e.
if you put this formula in cell B1 of your sheet where you are placing these formulas:

="CC" & TEXT(TODAY(),"yymmm")

it will return this:

You can see this is very similar to our VBA formula, except Excel uses "TODAY()" instead of "DATE" and "TEXT" is the Excel equivalent of the "FORMAT" function.
We can also use the EDATE function to add/remove months from the formula.

So to return the next month ("CC23Jul"), we would use:

="CC" & TEXT(EDATE(TODAY(),1),"yymmm")

and to return the previous month ("CC23May"), we would use:

="CC" & TEXT(EDATE(TODAY(),-1),"yymmm")

Now that we have our sheet name calculated in cell B1, we can replace this formula:

=CC23Jun!B1+A1

with this to make it dynamically reference our calculated sheet name:

=INDIRECT(B1 & "!A1")+A1

If you use that method, you only need to update the one cell calculating the sheet name, and all the other formulas reference that.
That way you only need to update one formula, and not all of them.

 

[BillPeterson]

Thank you so much for the help. I'm amazed how well you guys know this stuff and everytime I come to this site I learn something. I can only imagine how frustrating it must be when users don't clarify their intent haha. I definitely appreciate you highlighting the difference. It's helping me learn and my macro development is certainly getting closer to the goal! Thank you again!

 

[Joe4]

You are welcome.
Glad I was able to help!