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]

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

<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Invoice No	Invoice desc	Product Desc	Product Code	Product Sale	Invoice total
90049140	Blah	Project Management	2134651235	986	2819
		catering	2134651235	589
		linen & laundry	2134651235	986
		portering - afc element	2134651235	258

<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Thats what Id like if its possible

oldman