Solved

Insert data on every worksheet based on current worksheet

Posted on 2014-02-20
11
277 Views
Last Modified: 2014-02-20
I need to insert the same header row [Row 1 in sheet called Data] into every tab in my workbook. Can an expert provide me with VBA to do this

Thanks
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39872771
For this not required macro.

Step 1 copy your Header (Data sheet)

Step 2 Right click on sheet tab - select All sheet.

Step 3 Press ctrl & click on Data Sheet so it will get unselected from group

Step 4 select any sheet and past your header.

Step 5 Done.

Thanks
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39872790
try this macros

Sub CopyRowHeader()
     
    Application.CutCopyMode = True
     
    Dim Counter As Long, i As Long
     
    Counter = Sheets.Count
    For i = 1 To Counter
        Sheets("Sheet1").Cells(1, 1).EntireRow.Copy
        Sheets(i).Cells(1, 1).PasteSpecial
         
    Next i
     
    Application.CutCopyMode = False
End Sub


OR

Sub CopyHeader()
    Dim wsSheet As Worksheet
    For Each wsSheet In ThisWorkbook.Worksheets
        wsSheet.Rows(1).Value = Worksheets("Sheet1").Rows(1).Value
    Next wsSheet
End Sub


refer from
http://www.ozgrid.com/forum/showthread.php?t=77844
0
 

Author Comment

by:Jagwarman
ID: 39872803
Pratima Pharande thanks for that but, they both insert the header in row 1 but they do not shift the data in the sheet down by one row therefor they overwrite the data on row one.

Can you assist
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 39872807
itjockey there could be 100 sheets so this is not something I want to do manually

Thanks
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39872811
For this not required macro.

Step 1 copy your Header (Data sheet)

Step 2 Right click on sheet tab - select All sheet.

Step 3 Press ctrl & click on Data Sheet so it will get unselected from group

Step 4 select any sheet Add 1 row which shift down data to all sheet.

Step 5 Past your header  data in row 1.

Step 6 Done.

Thanks
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39872820
This is one time process you don't have to do it for each & every sheet.

But I guess you prefer VBA...  :)

Thanks
0
 

Author Comment

by:Jagwarman
ID: 39872826
itjockey thanks but yes as it is part of a much bigger Macro I prefer VBA
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39872835
try something like this

 For i = 1 To Counter
Sheets(i).Range("A:A").FillDown
        Sheets("Sheet1").Cells(1, 1).EntireRow.Copy

        Sheets(i).Cells(1, 1).PasteSpecial
         
    Next i
0
 

Author Comment

by:Jagwarman
ID: 39872992
Pratima Pharande

It's asking me for a variable. I'm not very good at this yet

Thanks
0
 

Author Comment

by:Jagwarman
ID: 39873049
Pratima Pharande

I added
Dim Counter As Long, i As Long
and it ran but it did not copy the header row from my sheet called 'Data' to any of the other sheets. I did change sheet1 in the code to Data
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 total points
ID: 39873140
Try this..

Sub copyheader()
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim ws1 As Worksheet

    Set ws1 = Sheets("Data")

    For Each ws In ActiveWorkbook.Worksheets

        If ws.Name <> "Data" Then
            ws.select
            ws1.Rows("1:1").Copy
            ws.Rows("1:1").Insert Shift:=xlDown
            Application.CutCopyMode = False
            ws.Range("a1").Select

        End If
    Next ws
ws1.select
    Application.ScreenUpdating = True

End Sub

Open in new window


Saurabh..
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

728 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