?
Solved

Need a excel macro to copy headers

Posted on 2007-07-29
18
Medium Priority
?
360 Views
Last Modified: 2010-03-05
Hi,

I need a way to copy the headers alone to new sheets in an excel.Only the headers has to be copied to new sheets .

Regards
Sharath
0
Comment
Question by:bsharath
  • 8
  • 6
  • 4
18 Comments
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590345
Sub acopy()
Sheet1.Rows(1).Copy Destination:= Sheet2.Rows(1)

End Sub
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 19590354
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
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590356
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 19590366
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
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590376
Private Sub Workbook_AfterNewSheet()
   ActiveSheet.Rows(1).Value = ActiveWorkBook.Sheets(1).Rows(1).Value
End Sub
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590388
' 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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 19590411
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
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590424
I get this.

Run time error 91
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 19590431
With what code?

Kevin
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590688
hey kevin i know exactly what i am doing and i am not copying your code
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 19590694
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
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590698
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
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590704
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
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590724
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....
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590734
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
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 19590736
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
0
 
LVL 11

Author Comment

by:bsharath
ID: 19590798
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.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19590801
there the deserving kevin gets the points :),
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question