Need a excel macro to copy headers

Sub acopy()
Sheet1.Rows(1).Copy Destination:= Sheet2.Rows(1)

End Sub

Add this code to the ThisWorkbook code module. It will copy the header row (row 1) to the new sheet from the last active sheet whenever a new sheet is inserted.

Option Explicit

Private mLastActiveSheet As Object

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   Application.OnTime Now(), "ThisWorkbook.Workbook_AfterNewSheet"
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   Set mLastActiveSheet = Sh
End Sub

Private Sub Workbook_AfterNewSheet()
   ActiveSheet.Rows(1).Value = mLastActiveSheet.Rows(1).Value
End Sub

Kevin

This copies only to sheet 2 i want it to copy to all sheets and to any new sheet inserted too.Sheet name can be any name but the headers should be copied.

This macro will copy the header from the active sheet to all other sheets.

Public Sub CopyHeaders()

   Dim Worksheet As Worksheet
   For Each Worksheet In Worksheets
      If Worksheet.Name <> ActiveSheet.Name Then
         Worksheet.Rows(1).Value = ActiveSheet.Rows(1).Value
      End If
   Next Worksheet

End Sub

Kevin

Private Sub Workbook_AfterNewSheet()
   ActiveSheet.Rows(1).Value = ActiveWorkBook.Sheets(1).Rows(1).Value
End Sub

' insert this in the ThisWorkBook module

Private Sub Workbook_AfterNewSheet()
   ActiveSheet.Rows(1).Value = ActiveWorkBook.Sheets(1).Rows(1).Value
End Sub

Public Sub aCopy()
   For i=2 to ActiveWorkBook.Sheets.Count
           ActiveWorkBook.Sheets(1).Rows(1).Value = ActiveSheet.Sheets(1).Rows(1).Value
    Next
End Sub

Hitesh,

You sure you know what you are doing there buddy?

You might want to take a closer look at your code. Maybe stop copying mine unless you know how it works.

Kevin

I get this.

Run time error 91

With what code?

Kevin

hey kevin i know exactly what i am doing and i am not copying your code

Really? How does this routine work and when is it called:

Private Sub Workbook_AfterNewSheet()
   ActiveSheet.Rows(1).Value = ActiveWorkBook.Sheets(1).Rows(1).Value
End Sub

Kevin

i made a mistake in the previous code sharath here you go
Private Sub Workbook_AfterNewSheet()
   ActiveSheet.Rows(1).Value = ActiveWorkbook.Sheets(1).Rows(1).Value
End Sub

Public Sub aCopy()
   For i = 2 To ActiveWorkbook.Sheets.Count
           ActiveWorkbook.Sheets(i).Rows(1) = ActiveWorkbook.Sheets(1).Rows(1)
    Next
End Sub

o yes kevin i know you are a genius
here the final macro
Public Sub aCopy()
   For i = 2 To ActiveWorkbook.Sheets.Count
           ActiveWorkbook.Sheets(1).Rows(1) = ActiveWorkbook.Sheets(1).Rows(1)
    Next
End Sub


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Rows(1).Value = ActiveWorkbook.Sheets(1).Rows(1).Value
End Sub

Hitesh,

I used this code.

Public Sub aCopy()
   For i = 2 To ActiveWorkbook.Sheets.Count
           ActiveWorkbook.Sheets(1).Rows(1) = ActiveWorkbook.Sheets(1).Rows(1)
    Next
End Sub


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Rows(1).Value = ActiveWorkbook.Sheets(1).Rows(1).Value
End Sub

But the headers what are in sheet 1 are disapearing....

sorry made another error
Public Sub aCopy()
   For i = 2 To ActiveWorkbook.Sheets.Count
           ActiveWorkbook.Sheets(i).Rows(1) = ActiveWorkbook.Sheets(1).Rows(1)
    Next
End Sub


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Rows(1).Value = ActiveWorkbook.Sheets(1).Rows(1).Value
End Sub

hitesh
Still not coping the header from sheet 1 to sheet 2 and sheet 3.When i create a new sheet also this does not copy after running the macro.

there the deserving kevin gets the points :),