Copying/Reformatting records

[MC09]

Hi,

Please help

I have worksheets that contains couple of records that I need to copy in another worksheet.

Example:

A --- B
​

1 AA1 ---01.01.2008
2 AA2 ---01.01.2008
3 AA3 ---01.01.2008
4 AA4 ---01.01.2008
5
6
7 BB1 ---01.03.2008
8 BB2 ---01.04.2008

RESULT:
1 TITLE ---DATE --- header
2 AA1 ---01.01.2008
3 AA2 ---01.01.2008
4 AA3 ---01.01.2008
5 AA4 ---01.01.2008
6
7
8 TITLE ---DATE --- another table/header
9 BB1 ---01.03.2008
10 BB2 ---01.04.2008

Thanks in advance

 

[venkat1926]

I hope I have understood what you want.


try this macro "test"
the macro "undo" undoes what you have done.
give feedback

Sub test()
Dim rng As Range
With Worksheets("sheet1")
Set rng = .Range("a1")
rng.End(xlDown).End(xlDown).CurrentRegion.Copy
    With Worksheets("sheet2")
    .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
    .Range("a1") = "title"
    .Range("b1") = Date
    .Range("c1") = "header"
    .Range("a1").Select
    End With 'sheet2
 End With 'sheet1
 Application.CutCopyMode = False
End Sub

Sub undo() ' this removed the entreis made in sheet2
With Worksheets("sheet2")
.Cells.Clear
.Range("a1").Select
End With

End Sub