1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Column Headers

Discussion in 'Business Applications' started by computerman29642, Mar 26, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Is it possible to have the column headers from the first page repeat on every page after?
     
  2. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I just realized that it is not that simple.

    I have multiple column headers throughout the worksheet.


    For Example:

    Test (Column Header)
    Data (Row 2)
    Data (Row 3)
    Data (Row 4)
    Data (Row 5)
    etc....

    Test 2 (Column Header)
    Data

    Once the data ends for the first header, I then need the second header to repeat.
     
  3. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    yes, just requires a little bit of vba; code behind the ThisWorkbook object.
     
  4. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have come up with this for the first set of headers

    Code:
    ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
    ActiveSheet.PageSetup.PrintTitleColumns = "$A:$C"
    
    I have not been able to get the code to determine when the first section data ends, and the next section begins.

    Would it be possible to have a fix column header that displays on all pages, and then do what is mentioned above?
     
  5. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Try this, behind the ThisWorkbook, in vba.
    Code:
    Option Explicit
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws As Worksheet
    Dim t As String 'Target Address
    t = Target.Address
        If Not Intersect(Target, Range("A1:D1")) Is Nothing Then
            Application.EnableEvents = False
            
            For Each ws In ActiveWorkbook.Sheets
                ws.Range(t) = Target.Value
                
            Next ws
            
            Application.EnableEvents = True
            
        End If
    End Sub
    
    
     
  6. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I do not actually type anything into the worksheet. I have a macro that pulls in all the other data, and setups the column headers.

    Will that cause a problem with the code you have provided?
     
  7. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    I would suggest you use:

    Application.EnableEvents = False
    and then...
    Application.EnableEvents = True
    ...whilst you copy the data into the workbook.

    Alternatively if you get problems with this, you could set up a cell that must have a particular value in before it runs the code I've given you, as an off switch, that you fill with the appropriate value when you use your other macro to fill the data. The bit I've written currently acts on anything that changes in cells A1:D1 only.
     
  8. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    MRdNK, could you explain what your code is doing?
     
  9. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Yep. I've add some comments to the module, hope this helps.

    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'On change of any cell in this workbook
    Dim ws As Worksheet
    Dim t As String
        
        'If the Target range, is within Range A1:D1 Then
        If Not Intersect(Target, Range("A1:D1")) Is Nothing Then
            t = Target.Address
            
            'Turn events off, so that it won't keep re-evaluating each cell as each sheet is changed.
            Application.EnableEvents = False
            
            'For each worksheet in the current workbook
            For Each ws In ActiveWorkbook.Sheets
                
                'Select worksheet, range (Target.Address)
                ws.Range(t) = Target.Value
                
            'Loop to next worksheet
            Next ws
            
            'Turn events back on.
            Application.EnableEvents = True
            
        End If
    End Sub
    
     
  10. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I apologize, but I am still confused with your code. How does your code repeat the column headers?
     
  11. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Would it be easier to have the macro search for the page/print breaks, an dthen insert the headers again (If you are able to search for breaks)?
     
  12. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Anything put or changed in A1:D1 will be the same on every worksheet in the workbook.
     
  13. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have been playing around with the code, but I have yet to get it to work.

    Do I just change the "A1 : D1" reference once the first list has ended, and the second list begins?
     
  14. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Are you putting the code behind the "ThisWorkboook" in the VBA Environment.
     
  15. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/812983

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice