• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Insert data on every worksheet based on current worksheet

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
Jagwarman
Asked:
Jagwarman
  • 5
  • 3
  • 2
  • +1
1 Solution
 
Naresh PatelTraderCommented:
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
 
Pratima PharandeCommented:
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
 
JagwarmanAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JagwarmanAuthor Commented:
itjockey there could be 100 sheets so this is not something I want to do manually

Thanks
0
 
Naresh PatelTraderCommented:
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
 
Naresh PatelTraderCommented:
This is one time process you don't have to do it for each & every sheet.

But I guess you prefer VBA...  :)

Thanks
0
 
JagwarmanAuthor Commented:
itjockey thanks but yes as it is part of a much bigger Macro I prefer VBA
0
 
Pratima PharandeCommented:
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
 
JagwarmanAuthor Commented:
Pratima Pharande

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

Thanks
0
 
JagwarmanAuthor Commented:
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
 
Saurabh Singh TeotiaCommented:
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

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.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now