Returning values

Hi Li Kim

To run the macro you need to create a new module in the VBE.

To do this open your workbook and close down all other workbooks. Then press ALT F11. In the resulting window click on the insert menu and select "module"

In the code window (Large one on the right) paste the following code.

Sub CopyData()



    Dim intRowCount As Integer

    Dim intColumnCount As Integer

    Dim intColumns As Integer

    

    Application.ScreenUpdating = False

    

    Sheets("Pages").Select

    

    Range("A2").CurrentRegion.Sort key1:=Range("F1"), order1:=xlDescending

        

    intColumns = Sheets("Pages").Range("A1").CurrentRegion.Columns.Count

    

    For intRowCount = 1 To 21

    

        Sheets("Pages").Select

    

        Range(Cells(intRowCount, 1), Cells(intRowCount, intColumns)).Select

        

        Selection.Copy

        

        Sheets("Editorial Dashboards").Select

        

        Cells(intRowCount, 1).Select

        

        ActiveSheet.Paste

        

               

    

    Next intRowCount

    

    Application.CutCopyMode = False

    Range("A1").Select

    



End Sub

You will then have a macro called CopyData which you can run in the normal way. If you have problems it will be because your sheet does not match all the assumptions I have made. For example I have assumed that the data starts in cell A1.

You could try tweaking the code, but if you have major problems you could just email me the workbook and I will fix the problems. My email is stephen@stl-training.co.uk


Regards

Stephen