Create Sheet from Rows in a Table

[Beneindias]

Hi guys,

I'm creating a file to calculate employees wage and taxes.

In order to avoid having a table with close to 80 columns, I'm looking at having a sheet to work like a resume with taxes and amount to transfer to the employee and have individual sheets where the calculations happen.

To build this, I looked at creating a table row from each sheet, but only found a solution to create sheets from table rows. I already have VBA code that can copy a template that I have, and change the name of the sheet to the employee name.

Now, my first problem is that after my sheets are created, I can't create new sheets when I update my "resume" table. Meaning that if I have a new employee and add is name to the end of the table, my code will not create a new sheet to this new name.

Other thing, I would like to update my code so that it look for a specific header in a table instead of a cell range as I have it now.

My code looks like this:

Sub Rows_to_New_Sheet()
Dim A, W_S As Worksheet, LastRow
On Error Resume Next
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For Each A In Range("B7:B" & LastRow)
    If A.Value <> "" Then
        Set W_S = Worksheets(A.Value)
        If W_S Is Nothing Then
            Sheets("Salários 1").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = Application.Proper(A.Value)
        End If
    End If
Next A
End Sub

"Salários 1" is the sheet that I'm considering as a Template.

And another thing, in the "resume" table, I have cells that will take values from this new sheets. Is there any way that this formulas will automaticaly update to seek cells in the new sheet name?
Like, if I have a formula in my table that takes a value from the sheet named "Ana", but then I create a row and sheet called "Sara" I need that formula in the new row to update to the sheet called "Sara".
Is that possible?

Thanks for the help

 

[Beneindias]

I'm open to change my plan and have a vba code that creates a table row for each new sheet if it's easyer.
It was my first plan, but I couldn't find suitable code to make it hapen, so I went the other way around

 

[Beneindias]

Ok, changed the formula to look for the "Name" collumn in my table instead of a range, and its copying my template sheet and changing the sheets name as intended.

Still have all the other problems, though.

 

[Beneindias]

Bump

 

[Beneindias]

Hey guys

Updated my code a little.
Now I'm abble o put some formulas in the table when I create sheets, but, this one only puts the formula in the first row, and linking to the last sheet...
The rest of the formulas in the rows are behaving like normal rows in a table, sreating formulas to the same sheet with different rows...

My code:

Sub Rows_to_New_Sheet()
Dim A, W_S As Worksheet, LastRow
On Error Resume Next
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For Each A In Range("Tabela10[Nome]")
    If A.Value <> "" Then
        Set W_S = Worksheets(A.Value)
        If W_S Is Nothing Then
            Sheets("Salários 1").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = Application.Proper(A.Value)
            Worksheets("Resumo").Select
            Range("Tabela10[Categoria]").Select
            ActiveCell = "='" & A.Value & "'!B3"
            Range("Tabela10[Valores Sujeitos a Desconto]").Select
            ActiveCell = "='" & A.Value & "'!E30"
            Range("Tabela10[Subsídio de alimentação]").Select
            ActiveCell = "='" & A.Value & "'!E34"
            Range("Tabela10[Total Remunerações]").Select
            ActiveCell = "='" & A.Value & "'!E38"
            Range("Tabela10[Totla de Descontos]").Select
            ActiveCell = "='" & A.Value & "'!J28"
            Range("Tabela10[Valor Líquido a receber]").Select
            ActiveCell = "='" & A.Value & "'!J37"
            Range("Tabela10[Transf. Bancária]").Select
            ActiveCell = "='" & A.Value & "'!J40"
            Range("Tabela10[Valores Extra]").Select
            ActiveCell = "='" & A.Value & "'!E44"
            Range("Tabela10[Total a receber]").Select
            ActiveCell = "='" & A.Value & "'!E49"
        End If
    End If
Next A
End Sub

vencimentos V3.xlsm
	B	C	D
6	Nome	Categoria	Valores Sujeitos a Desconto
7	Tony	PEDREIRO 2ª	975,00
8	John	0	0,00
9	Smith	0	0,00
Resumo

Cell Formulas
Range	Formula
C7:C9	C7	=Smith!B3
D7:D9	D7	=Smith!E30

As you can see, the formulas should be:
C7 -> =Tony!B3
C8 -> =John!B3
C9 -> =Smith!B3

but, instead, I'm getting:
C7 -> =Smith!B3
C8 -> =Smith!B4
C9 -> =Smith!B5

What am I doing wrong?

Also, I still have the problem that this code only works the first time and does not update to new rows.

Thanks for all of your help

 

[Beneindias]

Up