create new sheet and add headers to the columns
create new sheet and add headers to the columns
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: create new sheet and add headers to the columns

  1. #1
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Im using a macro to add a new worksheet and then add the column headers.

    A1 = Date
    B1 = Time
    C1 = Name
    D1 = Location and so on for 20 columns

    Is there a way this can be acheived without editing each cell in turn.

    Best regards

    Kevin

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kevin
    Tis not clear???
    Edit the column headers?
    The cells below the column headers?
    Both?
    Please reply
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-31 06:02, swaink wrote:
    Im using a macro to add a new worksheet and then add the column headers.

    A1 = Date
    B1 = Time
    C1 = Name
    D1 = Location and so on for 20 columns

    Is there a way this can be acheived without editing each cell in turn.

    Best regards

    Kevin
    Do you have these colum headers typed in on another spreadsheet somewhere. If so then yes, this is possible

    Let me know if this is the case.

    RET79

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, let's assume you have your column headings displayed like this on range A1 down on a sheet:

    Colum Headings
    Date
    Time
    Location
    Cost
    etc.

    run the following macro:

    Sub macro1()

    Dim arrcolhead() As Variant, rngcolhead As Range, z As Long
    Set rngcolhead = Range([a1], [a9].End(xlDown))
    z = rngcolhead.Rows.Count
    arrcolhead = rngcolhead
    Sheets.Add
    For k = 2 To z Step 1
    Debug.Print arrcolhead(k, 1)

    Range("A1").Offset(0, k - 2).Value = arrcolhead(k, 1)
    Next k
    End Sub


    It defines a dynamic range, makes into an array then puts the values in a new sheet. There are probably simpler macros available for your purpose I'm sure, but if you learn how this one works then I'm sure you will see many uses for this sort of coding in other applications.

    Hope this helps.

    RET79

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or, a simpler one, assumign your actual headings are listed from A2 down in some sheet:


    Sub simpler()

    Set rngcolhead = Range([a2], [a2].End(xlDown))
    z = rngcolhead.Rows.Count
    Sheets.Add
    Range([A1], [A1].Offset(0, z - 1)) = WorksheetFunction.Transpose(rngcolhead)
    End Sub

    This one is probably better, quicker, as there are no loops.

    RET79

    [ This Message was edited by: RET79 on 2002-03-31 06:40 ]

  6. #6
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi There all,
    sorry for the confusion Tom, Ret79 no i dont have the headers written down on a spread sheet, im trying to achieve it just using the macro, but I could do.

    At the minute Ive just written them into a macro like this
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Location" etc
    as you can see its rather long.

    Kev

    [ This Message was edited by: swaink on 2002-03-31 07:23 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-31 07:21, swaink wrote:
    Hi There all,
    sorry for the confusion Tom, Ret79 no i dont have the headers written down on a spread sheet, im trying to achieve it just using the macro, but I could do.

    At the minute Ive just written them into a macro like this
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Location" etc
    as you can see its rather long.

    Kev

    [ This Message was edited by: swaink on 2002-03-31 07:23 ]

    Just a suggestion...

    I have found through experience that it is better to write the column headings on some dummy input spreadsheet and read them in from a macro rather than put the names directly into the macro. Although what you are doing works fine, it is very inconvenient to make any changes to it.

    For instance, say you wanted to swap two column names around, correct spelling, or add an extra column name or whatever - you would have to change your macro everytime which for many reasons is a bad idea especially so if other people might want to use your macro. Also as you said, your current macro is very laborious to write, imagine if you had 100 or 250 column names?!

    It was for this reason that I started to put colum headings in a column, on any old dummy sheet, then construct the macros I gave you to read off what you had in a column. As the macro recognises the column as a dynamic range, it will work whether you change column names around, add column names to the list, reduce the list or whatever - through experience I have found this makes it a better alternative as any changes are automatically picked up.

    So my advice is, try and make these things general so that changes will be easily made.
    Also, I have found that if there are inputs to the macro, they are best coming from a spreadsheet. In this way, if changes are to be made, you simply edit your inputs on a spreadsheet rather than having to edit them in the code everytime.

    I hope I don't sound patronising, but I'm sure the more you will work with such things the more you will understand what I am going on about!

    Good luck,

    RET79


    [ This Message was edited by: RET79 on 2002-03-31 08:49 ]

  8. #8
    Board Regular swaink's Avatar
    Join Date
    Feb 2002
    Location
    51.421818,-0.977139
    Posts
    432
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I really appreciate your input, and see totally where your coming from I hadn't given any concideration to the fact I may need to edit the thing later.

    One question I have another workbook in which the macros open a *.csv document.

    Could I list the headers in one workbook and transfer them to the new one?

    Kev

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Once you have opened your csv file in excel, try this one:

    Sub Macro1()
    '

    '
    Dim dest As Object
    Dim inputsheet
    Dim rngcolhead As Range
    Dim z As Byte



    Set dest = Application.Workbooks("dest.xls").Worksheets("Sheet1")
    Rows("1:1").Insert

    'create some dummy input sheet with your column headings listed in column A

    Set inputsheet = Application.Workbooks("InputSheet.xls").Worksheets("Sheet1")



    Set rngcolhead = Range([a1], [a1].End(xlDown))
    z = rngcolhead.Rows.Count

    dest.Activate
    Range([a1], [a1].Offset(0, z - 1)) = WorksheetFunction.Transpose(rngcolhead)


    End Sub

    with dest.xl being your imported csv file.

    RET79

    [ This Message was edited by: RET79 on 2002-03-31 11:24 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com