create new sheet and add headers to the columns

[swaink]

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

[Tom Schreiner]

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

[RET79]

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

[RET79]

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

[RET79]

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 ]

[swaink]

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 ]

[RET79]

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 ]

[swaink]

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

[RET79]

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 ]