writing the column names in first row, based on the sheet name

[Sheela]

I am new to programming. This site has been very helpful for me. I am
learning as I am working.
I would need to write a macro to name columns based on the sheet name.
This code will run through all the sheets in the workbook and fill the first
row, based on the sheet name.

If sheetname name is “name1†then column names are ( “Col1â€, “Col2â€, “Col3â€)
( these will be written in the first row)
Else if sheetname is “name2†then column names are (“some1†“some2â€)

Could someone please give me a sample template of code. I am thinking of
select case statement. But do not know how to write this in VBA code.

Thank you very much for you help in advance

sheela

 

[JLGWhiz]

This snippet would check each sheet for its name and then enter the column
headings for columns A - D based on the sheet name.

Dim sh As Worksheet
For Each sh In ThisWorkbook.Sheets
If sh.Name = "name1" Then
For i = 1 To 4 'or ever how many cols
sh.Cells(1, i) = "Col" & i
Next
ElseIf sh.Name = "name2" Then
For i = 1 to 4"
sh.cells(1, i) = "some" & i
Next
End If
Next

You can alter the number of columns and add ElseIf statements for as many
variations as you need.

 

[Otto Moehrbach]

Sheela
Do you have just 2 sheets or a bunch? Are the column headers always some
text followed by a number starting with 1? If so then a For loop
incorporating a Select Case construct would do it. Something like this
perhaps: HTH Otto
Sub Sheela()
Dim sh As Worksheet
Dim TheWord As String
For Each sh In ThisWorkbook.Sheets
Select Case sh.Name
Case "name1": TheWord = "Col"
Case "name2": TheWord = "some"
'etc, etc, etc
End Select
With sh
.Range("A1") = TheWord & "1"
.Range("A1").AutoFill Destination:=.Range("A1:N1")
End With
Next sh
End Sub

 

[Barb Reinhardt]

Dim WS as excel.worksheet


for each WS in ThisWorkbook.worksheets
mytext = ""
if ws.name = "name1" then
myText = "Col"
elseif WS.name = "name2" then
myText = "some"
end if

if not mytext = "" then
for i = 1 to 10
ws.cells(1,i).value = mytext & i
next i
end if
Next WS

Select case would also work if you want to use that.

HTH,
Barb Reinhardt

 

[Sheela]

I am sorry I wasn't clear in my query.

There will be multiple sheets and each time the number of sheets will be
different and it can be as big as 50 sheets, so Select case will be
preferable.

Another thing is the column names will be actual names like ( " Time Used",
"Dosage" , "ID", "Count of Cells", " Viral Load" ) etc.
So we cannot use the i loop.

Sheela.