Hi,
A couple of weeks ago I got help from a very kind person called "yudlugar" in this forum but not\w I need to modify his solution a bit. I cant seem to reopen the last thread so that is why I made a new one. Here is a link to the old one:http://www.excelforum.com/excel-prog...-vba-help.html


Formula: copy to clipboard 


Sub Macro_1()

Application.ScreenUpdating = False

Dim wS1, wS2, count_wS1, count_wS2, i

Set wS1 = Sheets("Current Layout") 'Change current layout to sheet name as appropriate

Set wS2 = Sheets.Add

count_wS1 = 2

count_wS2 = 2

Do Until wS1.Range("B" & count_wS1) = ""

    wS1.Range("F1:AE1").Copy

    wS2.Range("A" & count_wS2).PasteSpecial Transpose:=True

    wS2.Range("B" & count_wS2 & ":B" & count_wS2 + 25) = wS1.Range("A" & count_wS1)

    wS2.Range("C" & count_wS2 & ":C" & count_wS2 + 25) = wS1.Range("B" & count_wS1)

    wS2.Range("D" & count_wS2 & ":D" & count_wS2 + 25) = wS1.Range("D" & count_wS1)

    wS2.Range("E" & count_wS2 & ":E" & count_wS2 + 25) = wS1.Range("E" & count_wS1)

    For i = 6 To 31

        wS1.Range(Cells(count_wS1, i).Address & ":" & Cells(count_wS1 + 10, i).Address).Copy

        wS2.Cells(count_wS2 + i - 6, 6).PasteSpecial Transpose:=True

    Next

    count_wS1 = count_wS1 + 11

    count_wS2 = count_wS2 + 26

Loop

Application.ScreenUpdating = True

End Sub


What has happened is that I am adding rows to the data each month (right now up to ROW AE) and therefore the macro does not work any more. I have updated the code manually by changing the values highlighted in red above, but that is not very efficient, and also very complicated to explain to someone else how to do....

Therefore, I was hoping that someone could show me how this can be done automatically. For example by counting how many of the cells in row 1 contains numbers, i guess it would be easy to do this, but I only know how to count in excel hehe and not VBA.

For example now it is from A to AE = 31 rows... And from F (6) to AE (31) = 26

Thanks a lot in advance!

Kind Regards