Sorting data from a master sheet to multiple sheets depending on values in a given column

Basically, what I want i to sort data from a master sheet to multiple sheets depending on values in a given column of the master sheet. This very same issue has been addressed at http://www.excelforum.com/excel-prog...tml?p=3226790;

I have been inspired by the codes in the link above, and have made this code:

 
Sub yihaa()
  Dim OutSH As Worksheet
  Dim DataSH As Worksheet
  Set DataSH = Sheets("sheet1")
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  lastrow = DataSH.Cells(Rows.Count, "A").End(xlUp).Row
  If Sheets.Count > 1 Then
    For i = 2 To Sheets.Count
      Sheets(i).Cells.Clear
    Next i
  End If
  For Each ce In DataSH.Range("A1:A" & lastrow)
    If Not IsEmpty(ce) Then
      arr = Split(ce.Value, ",")
      For i = LBound(arr) To UBound(arr)
        'add a new sheet if it doesn't exist
        If Not sheetexists(Trim(arr(i))) Then
          Sheets.Add after:=Sheets(Sheets.Count)
          ActiveSheet.Name = Trim(arr(i))
           ActiveSheet.Range("A1:H1").Value = Array("anlæg", "areal", "etage", "rumtype", "rumnummer", "luftskifte", "højde", "luftmængde")
           
          End If
        Set OutSH = Sheets(Trim(arr(i)))
        ce.EntireRow.Copy Destination:=OutSH.Cells(Rows.Count, 3).End(xlUp).Offset(1, -2)
      Next i
    End If
  Next ce
  
End Sub #

how ever, headings (''anlæg'', ''areal'', ...) are not being copied over in all sheets, meaning that the line below is not working correctly:

  ''ActiveSheet.Range("A1:H1").Value = Array("anlæg", "areal", "etage", "rumtype", "rumnummer", "luftskifte", "højde", "luftmængde")#.

Is there anybody that can help me fixing this issue?? This is my very first VBA task,

Tanks in advance

I do not see any syntax error with this line

  ActiveSheet.Range("A1:H1").Value = Array("anlæg", "areal", _
   "etage", "rumtype", "rumnummer", "luftskifte", "højde", "luftmængde")

Hello AB33, thanks very much for your answer.

I have been playing with my sheet since my last post and found out that the very first time I run the code, it will do it correctly (import headings to the respective sheets). How ever, if I update something in my master sheet and run the code again, headings do not import, meaning that the line above does not rerun.

Else, I have one more issue:

Is there a way to further develop the program above to sum up column ''areal'' in each sheet, and put the result just under the last data in column ''areal''??

I have been trying by adding the following line under the (ActiveSheet.Range (''A1:H1''). value....) syntax, but it is not working for some reason:

 ActiveSheet.N = Cells(Rows.Count, "B").End(xlUp).Row
           Cells(N + 1, "B").Formula = "=SUM(B1:B" & N & ")"

In advance, thanks very much for your help.

kodos10,
It is difficult to pin point the problem with out seeing the code in your actual data. Could you please attach your sample with the code?

I have attached my excel file. So, for instance in Sheet 101, I would like for my macro to sum up the numbers in column B and put the result in B14, and so on. Furthermore, I would like for the titles to run the line below in every sheet, also when I rerun the code.

ActiveSheet.Range("A1:H1").Value = Array("anlæg", "areal", "etage", "rumtype", "rumnummer", "luftskifte", "højde", "luftmængde")
             ActiveSheet.N = Cells(Rows.Count, "B").End(xlUp).Row

Once again, thans for your help

I have written a new code which I normally use it for this task. Try it

Thank you very much AB33, your code works better than the one I had. How ever, it does not calculate the totals of column B in the different sheets. For instance in sheet 101, it should be capable of calculating the total of the numbers in column B and put the result in B14. I had been trying by adding the following syntax in the code I had earlier, but it was not doing it.

ActiveSheet.N = Cells(Rows.Count, "B").End(xlUp).Row
           Cells(N + 1, "B").Formula = "=SUM(B1:B" & N & ")"

I am not sure where to add in your code, and if it is going to run it it. Do you think you can help with this issue??

once again, thank you man....

Do you mean you want to add column B values at the bottom?

Yes, more or less. At the bottom of the last cell containing data in column B.

Try attached

Hello AB33, your code works perfectly. You are the best. However, I have been trying to further develop the code to add a summary sheet. The summary sheet should contain the names of the different sheets, and aside of each name, there should be data from the last row (total row). I have been trying with the following code, I found on the net:

HTML Code: 

The code is doing what it is supposed to do, however, I does not search through the data and find the relevent data by its own, meaning that for instance, I have to write that it should import data from A29 and B29 and range it in a given cell in the summary sheet. So, if I update my sheet, and my totals move to A30 and B30, it will import the wrong data. I have attached my file as it is now. In sheet summary, you can see how the summary sheet is supposed to look like.

Once again thanks man, I preciate a lot that you are taking your time to help me

Kodos,
Your latest request is not related to the original thread. You should have closed the thread and re-start a new one. Check the attached and if you are happy with the solution provided, please close the thread.

Hello AB33, Yes I should have closed this tread and started a new one, but you have been so good at helping in this one... Anyway, rules are of course there to be followed, so I will do that next time. Well, your code is working as intended, so thank you very much man.