Using Sumif In A Macro

[Timbo]

I have a worksheet with 20 sheets in it named Bic_Code1, Bic_Code2 etc
I also have a sheet called CopyFromTo.

The Sheets are all the same size with the same number of rows an
columns.

Using Sheet Bic_Code1 as an example -

At the bottom of the sheet is a block of data that I want to copy t
the
CopyFromTo sheet, run a de dupe macro, then go to I1 of the CopyToFro
Sheet and do the following calculation-
sumif(Bic_Code1("D661"),CopyToFrom("D1"),sumif(Bic_Code1("I6:I61")

I then sort descending, by value and copy back to another area o
Bic_Code1.

Then I want to go to sheet Bic_Code2 and do the same and so on.

My macro works perfectly except for the sumif part.

I have identified the Bic_Code1 sheet using

WorksheetName = ("Bic_Code1")

When the macro loops around I have a second statement
WorksheetName = ActiveSheet.Name so that it knows it is now o
Bic_Code2

I tried every way I know of to get this macro to work for the sumif bu
I get a type mismatch error. If I hover in step mode ShtNm is picking u
Bic_Code1 so it must be something else.

All help much appreciated.


Code
-------------------
Dim CopyFromTo
Dim ShtNm

CopyFromTo = ("CopyFromTo")

ShtNm = WorksheetName

Range("I1").Select
With SumIf
Worksheet.Formula = SumIf(Sheets(ShtNm).Range("$D$6:$D$51"), Sheets(CopyFromTo).Range("E1"), Sheets(ShtNm).Range("$I$6:$I$51"))
End With

 

[RadarEye]

Hi Timbo

In Excel 2003 I have created this:

Sub CreateSumIf()
Dim CopyFromTo As String
Dim ShtNm As String
Dim sFormula As String

CopyFromTo = ("CopyfromTo")

ShtNm = WorksheetName
Range("I1").Select

sFormula = "=SumIf(" & ShtNm & "!$D$6:$D$51, " & _
CopyFromTo & "!E1," & ShtNm & "!$I$6:$I$51)"

Range("I1").Formula = sFormula
End Sub

HTH,

Wouter