Macro modification required (very small)

**nur2544** · 12-21-2012, 03:26 AM

Dear all,
How to add same formula in different sheet ? below macro is running fine for the sheet "i6" very well.
but now i want to add more sheet like (i7,i8,i9,i10,i11,i12,i13,i14,i15) so that when ever i run macro
each & every sheet having same formula at a time. here one things i can create sheet wise macro
but it makes the the overall macro bigger & also size is factor. i need your assistance...

Sub EnterFormula()
With Sheets("i6")  ' here i want to add more sheet like (i7,i8,i9,i10,i11,i12,i13,i14,i15)
.Range("AB6").Formula = "=IF(COUNT(BD6:BL6)=0,"""",SUM(BD6:BL6)/9)"  ' in every sheet need to put same formula 
End With
End Sub

**OllieB** · 12-21-2012, 03:38 AM

First create/insert a code module in your VBA project and copy & paste the following code in that module

Public Sub pub_sub_CreateFormulas()
On Error Resume Next

'#
'# declare private variables
'#
     Dim pvt_xls_Worksheet As Excel.Worksheet

'#
'# loop for all worksheets in the workbook and when the worksheet name starts with an "i", update
'# cell AB6 with the desired formula
'#
     For Each pvt_xls_Worksheet In ThisWorkbook.Worksheets
          If Left$(pvt_xls_Worksheet.Name, 1) = "i" Then
               pvt_xls_Worksheet.Range("AB6").Formula = "=IF(COUNT(BD6:BL6)=0,"""",SUM(BD6:BL6)/9)"
          End If
     Next pvt_xls_Worksheet
     
End Sub

**nur2544** · 12-21-2012, 12:51 PM

Hi,Olaf.Braxhoofden

Wonderful logic. Thanks.

**Winon** · 12-21-2012, 05:18 AM

Hello nur2544,

Or you could change your code to:

Sub EnterFormula()
    Sheets(Array("i6", "i7", "i8", "i9", "i10", "i11", "i12", "i13", "i14", "i15")).Select
    Sheets("i6").Activate
    Range("AB6").Select
    ActiveCell.Formula = "=IF(COUNT(BD6:BL6)=0,"""",SUM(BD6:BL6)/9)"
    Sheets("i6").Select

End Sub

Also see the "Please consider" note at the bottom of this post.

**nur2544** · 12-21-2012, 12:53 PM

Hi,Winon
Excellent..Thanks.

**Winon** · 12-21-2012, 01:34 PM

Hello nur2544,

You are welcome.

Glad we could help.

Thank you for the Rep.

Macro modification required (very small)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro modification required (very small)

Re: Macro modification required (very small)

Re: Macro modification required (very small)

Re: Macro modification required (very small)

Re: Macro modification required (very small)

Re: Macro modification required (very small)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions