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 and
columns.

Using Sheet Bic_Code1 as an example -

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

I then sort descending, by value and copy back to another area of
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 on
Bic_Code2

I tried every way I know of to get this macro to work for the sumif but
I get a type mismatch error. If I hover in step mode ShtNm is picking up
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

--------------------


--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=40193

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