Thanks for visiting mypost. I have encountered a difficulty in applying sumif function.
The raw data is likethis.
In worksheet 1, thereare 100 companies' name on one row and 87 expense items on one column
Worksheet 2 is atemplate. I need to create a worksheet for each entity based on worksheet 2.
So I have the followingcode. For creating new tab, it is fine. For applying the sumif function, Ifailed. What I need is to get the raw data from worksheet 1 to worksheet 2.
Can any expert help tomodify the code below? I do not understand why it's not working.
Dim i As Long, entity As Variant
For i = 1 To 100
Worksheets("ws1").Activate
Cells(2, i).Activate
Set entity = ActiveCell
'Creating new tab foreach company and change the tab name
Worksheets("ws2").Copyafter:=Worksheets(Worksheets.Count)
Worksheets("ws2").name =RTrim(Left(entity, 31))
'Apply sumif function(But I dunno how why it doesnt work, and I think I can apply For_Next hereagain)
Range("D8") =Application.SumIf(Worksheets("ws1").Columns("A"),Range("G8"), Worksheets("ws1").Cells(2, i).Columns)
Range("D9") =Application.SumIf(Worksheets("ws1").Columns("A"),Range("G9"), Worksheets("ws1").Cells(2, i).Columns)
Range("D10") = Application.SumIf(Worksheets("ws1").Columns("A"),Range("G10") Worksheets("ws1").Cells(2, i).Columns)
....
Range("D87") =Application.SumIf(Worksheets("ws1").Columns("A"),Range("G87"), Cells(2, i).Columns)
Next
Thanks v. much!
The raw data is likethis.
In worksheet 1, thereare 100 companies' name on one row and 87 expense items on one column
Worksheet 2 is atemplate. I need to create a worksheet for each entity based on worksheet 2.
So I have the followingcode. For creating new tab, it is fine. For applying the sumif function, Ifailed. What I need is to get the raw data from worksheet 1 to worksheet 2.
Can any expert help tomodify the code below? I do not understand why it's not working.
Dim i As Long, entity As Variant
For i = 1 To 100
Worksheets("ws1").Activate
Cells(2, i).Activate
Set entity = ActiveCell
'Creating new tab foreach company and change the tab name
Worksheets("ws2").Copyafter:=Worksheets(Worksheets.Count)
Worksheets("ws2").name =RTrim(Left(entity, 31))
'Apply sumif function(But I dunno how why it doesnt work, and I think I can apply For_Next hereagain)
Range("D8") =Application.SumIf(Worksheets("ws1").Columns("A"),Range("G8"), Worksheets("ws1").Cells(2, i).Columns)
Range("D9") =Application.SumIf(Worksheets("ws1").Columns("A"),Range("G9"), Worksheets("ws1").Cells(2, i).Columns)
Range("D10") = Application.SumIf(Worksheets("ws1").Columns("A"),Range("G10") Worksheets("ws1").Cells(2, i).Columns)
....
Range("D87") =Application.SumIf(Worksheets("ws1").Columns("A"),Range("G87"), Cells(2, i).Columns)
Next
Thanks v. much!