How to copy sheet & change col headers

Hi Guys,

I have a few questions regarding excel.

[1]

I want to copy all the contents of a sheet to a new sheet .How could it be done.

[2]

How could I change the Column Headers(A,B,C,D) in a sheet to some concrete names.

[3]

How could I see all the comments I have wrote in a meaninful way...

[4]

After writing the excel document,I want to give it to others.But I dont want anyone to edit this. Is that possible.


I think thats a list now

Thanks
P

1, 2, and 3 are answered in the following macro.

For 4 - Search in Help for Protect Workbook or Protect Wroksheet

Sub copyIt()
Dim shtCur As Worksheet
'1) copy current sheet to new sheet
Set shtCur = ActiveSheet
Worksheets.Add after:=Worksheets(Worksheets.Count)
shtCur.Cells.Copy Destination:=Range("a1")

'2) create headers
Cells(1, 1) = "Header1"
Cells(1, 2) = "Header2"
Cells(1, 3) = "Header3"
Cells(1, 4) = "Header4"

'3) copies comment from activesheet to new sheet Uses same sheet source as the copy section above

Dim cmnt As Comment, i As Integer
Worksheets.Add after:=Worksheets(Worksheets.Count)
Cells(1, 1) = "Sheet"
Cells(1, 2) = "Comment"

For Each cmnt In shtCur.Comments
Cells(i + 2, 1) = cmnt.Parent.Parent.Name & " " & cmnt.Parent.Address
Cells(i + 2, 2) = Mid(cmnt.Text, InStr(cmnt.Text, ":") + 2, 99)
i = i + 1
Columns("a:b").AutoFit
Next
End Sub

How should I use this code.?
Isnt there any menu items which I could use

1. Non-VBA way, right click on the tab and click Move or Copy then just decided on your options.

2. I don't believe you can actually change the A,B,C and the top of the spreadsheet. Normally row A is used for your "custom" headers.

3. You can print the comments on a separate page using the Page Setup under the File Menu (Sheets Tab)

4. Tools - Protection - Protect Sheet