Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 208
Default Help ! How do I do this in VB Script

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(D5:D16)"
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
  #2   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,646
Default Help ! How do I do this in VB Script

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(D5:D16)"
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:

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(D5:D16)"
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

  #3   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 2,480
Default Help ! How do I do this in VB Script

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(D5:D16)"
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

--
Regards
Roger Govier

"Sean" wrote in message
...
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(D5:D16)"
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


  #4   Report Post  
Posted to microsoft.public.excel.setup
external usenet poster
 
Posts: 208
Default Help ! How do I do this in VB Script

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.

"Sean" wrote:

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(D5:D16)"
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
help with the VB script Igneshwara reddy[_2_] Excel Worksheet Functions 4 March 6th 07 09:54 PM
I need some VB script please rlee1999 Excel Discussion (Misc queries) 2 October 25th 06 05:46 PM
VB Script Krista Excel Worksheet Functions 4 May 20th 06 03:12 PM
VB script help - please!! Anthony Excel Discussion (Misc queries) 1 July 13th 05 01:19 AM
VBA script help..Please !!!! Anthony Excel Discussion (Misc queries) 6 June 6th 05 01:40 PM


All times are GMT +1. The time now is 05:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"