VBA code for Tabs and various info

[oldmandave]

Hi

Im looking for a VBA/macro that will create tabs with numbers from C1 to BZ1
and then using the data in the master sheet populate the tabs as well as some fixed data and fields
I have invoice number C1:BZ1
Invoice description C2:BZ2

Product description in A3:A88
Product Code in B3:B88

and then in Cells C3 to BZ88 random numbers, theres a sum in C3:C89

I need some fixed words in A1:A5 and a few other randoms words in random cells
buts its all fixed on every tab

I know its a bit of and ask but need to know how to switch a few thisng round as well as different departments put in slightly different formats

THanks

and old man

 

[cerfani]

Sub AddSheets()
    Dim c As Long
    c = 3
    Dim address As String
    Dim s As Worksheet
    Do While True
        address = Replace(Cells(1, c).Address, "$", "")
        Set s = Sheets.Add
        s.Name = address
        If address = "BZ1" Then Exit Sub
        c = c + 1
    Loop
End Sub

try that (untested)

I just noticed you asked for more... this code will just add sheets named using those cell addresses... is that what you meant?? It also needs to be updated to pull info to those sheets

 

[ranman256]

start w this:

Sub BuildTabs()
Dim rng As Range, cel As Range


'make sheets
Set rng = Range("C1:BZ1")
 For Each cel In rng
    Sheets.Add
    ActiveSheet.Name = cel.Value
    Range("A1").Value = "Invoice:"
    Range("A2").Value = "Descr:"
 
    Range("b1").Value = cel.Value
    Range("b2").Value = cel.Offset(1, 0).Value
    
    Range("A3").Value = "words"
    Range("A4").Value = "words"
    Range("A5").Value = "words"
    
 Next   'sheet
 
Set cel = Nothing
Set rng = Nothing
End Sub

 

[oldmandave]

[TABLE="width: 1725"]
<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Invoice:[/TD]
[TD] [/TD]
[TD="align: right"]90049140[/TD]
[TD="align: right"]90049141[/TD]
[TD="align: right"]90049142[/TD]
[TD="align: right"]90049146[/TD]
[TD="align: right"]90049502[/TD]
[TD="align: right"]90049503[/TD]
[TD="align: right"]90049504[/TD]
[TD="align: right"]90049506[/TD]
[TD="align: right"]90049661[/TD]
[TD="align: right"]90049662[/TD]
[TD="align: right"]90049663[/TD]
[TD="align: right"]90049664[/TD]
[TD="align: right"]90049665[/TD]
[TD="align: right"]90049666[/TD]
[/TR]
[TR]
[TD]Descr:[/TD]
[TD] [/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[TD]Blah[/TD]
[/TR]
[TR]
[TD]Project Management[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]986.00[/TD]
[TD] [/TD]
[TD="align: right"]153.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]insurance brokers fees[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]electricity[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]gas[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]water[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]456.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]sewerage[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]telephony - rental & circuit charge (229)[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]84,759.00[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]telephony -calls[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]availability charge[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]56,415.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]456.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]availability charge- variation
[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]132.00[/TD]
[TD="align: right"]231.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]486.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]rebate
[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]869,475.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]catering - afc element[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]456.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]catering - contract variations[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]catering[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]589.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]cleaning (domestic services - afc element)[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]cleaning (domestic services)[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]pest control[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]estates[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]linen & laundry[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]986.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]50,707.33[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]portering - afc element[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]258.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]heart centre portering[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]portering[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]portering other[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]waste[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]additional beds (winter contingency)[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]11,036.65[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]heart centre mgt fee[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]help desk variation[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]fm management & helpdesk[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]departmental provisions (additional catering)[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5,962.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]net income
[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Lifecycle charges[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]snack boxes (additional catering)
[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2,589.00[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]switchboard - element
[/TD]
[TD="align: right"]2134651235[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2,819.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]153.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]8,551.00[/TD]
[TD="align: right"]920,314.33[/TD]
[TD="align: right"]67,682.65[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]942.00[/TD]
[TD="align: right"]912.00[/TD]
[TD="align: right"]84,759.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
</tbody>[/TABLE]


Heres a copy of the data im trying to get a tab for each of the columns of data
So on every tab a1 has invioce No and under it is data
on every tab a2 will have invoice descr and data under it
Like below

[TABLE="width: 586"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Invoice No[/TD]
[TD]Invoice desc[/TD]
[TD]Product Desc[/TD]
[TD]Product Code[/TD]
[TD]Product Sale[/TD]
[TD]Invoice total[/TD]
[/TR]
[TR]
[TD="align: right"]90049140[/TD]
[TD]Blah[/TD]
[TD]Project Management[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]986[/TD]
[TD="align: right"]2819[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]catering[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]589[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]linen & laundry[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]986[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]portering - afc element[/TD]
[TD="align: right"]2134651235[/TD]
[TD="align: right"]258[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thats what Id like if its possible

oldman