Help ! How do I do this in VB Script

[Sean]

I have a spreadsheet with a Summany sheet and 235 deatil sheets.

I have various scripts that create the tabs, which are basically a summary
of monthly values for each of our suppliers.

I want to use vbscript to add totals to each supplier sheet but im having
great difficulty in doing what i want in a short piece of code.

I can do it for an individual supplier with the following code:

Private Sub CommandButton1_Click()


Sheets("ACME").Select

ActiveSheet.Range("C18").Value = "TOTALS"
ActiveSheet.Range("D18").Value = "=sum(D516)"
ActiveSheet.Range("E18").Value = "=sum(E5:E16)"
ActiveSheet.Range("F18").Value = "=sum(F5:F16)"
ActiveSheet.Range("G18").Value = "=sum(G5:G16)"
ActiveSheet.Range("H18").Value = "=sum(H5:H16)"
ActiveSheet.Range("I18").Value = "=sum(I5:I16)"
ActiveSheet.Range("J18").Value = "=sum(J5:J16)"
ActiveSheet.Range("K18").Value = "=sum(K5:K16)"
ActiveSheet.Range("L18").Value = "=sum(L5:L16)"

End Sub

But I dont want to have to creat 200+ procedures to fill in totals for each
supplier so how do I change sheets and put the above onto each one.

The main sheet has a list of Supplier names from cell A5 to A200+ so I can
pick them from there but how do I do this ?

Im sorry if this does not make sense but have been trying to do this for
hours and is driving me mad.

Thanks for any help

Sean

 

[Stefi]

Try something like this (not tested):

Private Sub CommandButton1_Click()
Dim supp As Range, supprng As Range
Sheets("Summary").Select
Set supprng = Range("A5:A235")
For Each supp In supprng
Sheets(supp.Value).Range("C18").Value = "TOTALS"
Sheets(supp.Value).Range("D18").Formula = "=sum(D516)"
Sheets(supp.Value).Range("E18").Formula = "=sum(E5:E16)"
Sheets(supp.Value).Range("F18").Formula = "=sum(F5:F16)"
Sheets(supp.Value).Range("G18").Formula = "=sum(G5:G16)"
Sheets(supp.Value).Range("H18").Formula = "=sum(H5:H16)"
Sheets(supp.Value).Range("I18").Formula = "=sum(I5:I16)"
Sheets(supp.Value).Range("J18").Formula = "=sum(J5:J16)"
Sheets(supp.Value).Range("K18").Formula = "=sum(K5:K16)"
Sheets(supp.Value).Range("L18").Formula = "=sum(L5:L16)"
Next supp
End Sub

Regards,
Stefi


„Sean†ezt írta:

 

[Roger Govier]

Hi Sean

Try
Private Sub CommandButton1_Click()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then

ws.Range("C18").Value = "TOTALS"
ws.Range("D18").Value = "=sum(D516)"
ws.Range("E18").Value = "=sum(E5:E16)"
ws.Range("F18").Value = "=sum(F5:F16)"
ws.Range("G18").Value = "=sum(G5:G16)"
ws.Range("H18").Value = "=sum(H5:H16)"
ws.Range("I18").Value = "=sum(I5:I16)"
ws.Range("J18").Value = "=sum(J5:J16)"
ws.Range("K18").Value = "=sum(K5:K16)"
ws.Range("L18").Value = "=sum(L5:L16)"
End If
Next
End Sub

 

[Sean]

Thanks Stefi / Roger ....

I have now sorted this, I realised that I could just put my code into the
macro that creates the supplier tabs in the first case. This then inserts
the totals line as it creates the supplier tab.

Thanks though for your help as your code has given me an idea for another
feature I need to add.

Cheers

Sean.