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