create new sheet and add headers to the columns

swaink

Active Member
Joined
Feb 15, 2002
Messages
432
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi Kevin
Tis not clear???
Edit the column headers?
The cells below the column headers?
Both?
Please reply
Tom
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top