Create a new tab, next number

[cartmanbrra]

I do standard monthly invoicing, and want to automate having to create a new tab and move a set of percentages from column I to F.

Id like to copy tab called "PC 01" and create a new tab called "PC 02" as an exact duplicate of the tab. Then I want to move the percentages in I31:I43 and I48:I50 to the same rows in column F.

Thank you

 

[Dim Me as xlNoob]

Hello,

You can try see if this code works for what you need:

Sub NewMonth()
    Dim sh As Worksheet, newSh As Worksheet, intPC As Variant
    
    For Each sh In Worksheets
        If sh.Name Like "PC*" Then
            intPC = intPC + 1
        End If
    Next sh
    
    intPC = Format(intPC, "#00")
    
    Worksheets("PC " & intPC).Copy after:=Worksheets("PC " & intPC)
    Set newSh = Worksheets("PC " & intPC & " (2)")
    With newSh
        .Name = "PC " & Format(intPC + 1, "#00")
        .Range("I31:I43").Cut Range("F31")
        .Range("I48:I50").Cut Range("F48")
    End With
End Sub

I was assuming that you would want to keep creating new tabs every month and keep the old ones, so this just counts all the "PC" tabs and creates the next one in order. Is that what you're after?

 

[cartmanbrra]

Hey thanks for helping me out. I get a subscript out of range error on this line

[COLOR=#333333]Worksheets("PC " & intPC).Copy after:=Worksheets("PC " & intPC)[/COLOR]

 

[cartmanbrra]

I should mention my one and only tab right now is "PC 01"

 

[Dim Me as xlNoob]

I just tried this and could only replicate that error if the sheet name was "PC 01" (with the quotes actually in the name). Is your sheet named like that? Or is it actually just PC 01? I would say the issue is to do with the sheet name, another possibility is if you had another workbook active at the time maybe the code was searching that workbook instead of the one where the PC 01 sheet actually is?

Can you tell me what the value of intPC was at the time of the error? Do get the value you can run the code and select Debug when it finds the error, then just over your mouse over an instance of intPC and tooltip should appear giving the value. Or when in debugging you can open the immediate window (ctr+g) and type ?intPC and hit enter, then it will print the value of intPC on the next line (if empty it will just be a blank line).

 

[cartmanbrra]

Ah yes there was a space after PC 01. It works but when i run i get three popups

1. the name 'A" already exists. Click yes to use that version, or no to rename
2. the name B exists....
3. the name table already exists....

This also causes problems with my data - its a bit more complicated than I thought.


Is it possible, instead of having a set range of cells to move, can it search for any percentage (only) in coulmn I and move it to the same row in column G?

 

[Dim Me as xlNoob]

Do have named ranges or somthing in the spreadsheet? Possibly copying the sheet is producing a conflict? Or is it coming up with those errors when it is attempting to move the data from column I to column G?

 

[cartmanbrra]

The error seems to be when the sheet is copying over.

Im good with a fixed set of rows, otherwise I suppose it gets too confusing.

The only other hitch is once in a while Ill have a tab, say PC 02R. Is there anyway to have it work regardless of an R being at the end or not?

 

[Dim Me as xlNoob]

Having it work with an R suffix in some sheet names will be easy.

I'm still hung up on the other issue. I can't replicate the error. I've tried putting in different named ranges and tables, referencing them in other formulas, and even conditionally formatting them and conditionally formatting other cells with a reference to the ranges and tables. But when I copy the sheet, excel just renames the new table and updates all the references to the new name. Interestingly with the ranges it keeps the same name but changes the scope to the worksheet only. But still no errors and everything calculates and formats correctly!

Sorry but I just want to be sure of something, can you run the code with a few things commented out like this?

Sub NewMonth()
    Dim sh As Worksheet, newSh As Worksheet, intPC As Variant
    
    For Each sh In Worksheets
        If sh.Name Like "PC*" Then
            intPC = intPC + 1
        End If
    Next sh
    
    intPC = Format(intPC, "#00")
    
    Worksheets("PC " & intPC).Copy after:=Worksheets("PC " & intPC)
    Set newSh = Worksheets("PC " & intPC & " (2)")
'    With newSh
'        .Name = "PC " & Format(intPC + 1, "#00")
'        .Range("I31:I43").Cut Range("F31")
'        .Range("I48:I50").Cut Range("F48")
'    End With
End Sub

 

[cartmanbrra]

It was from some old junk in the worksheet, your code works perfectly thank you, any thoughts on R issue?