Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing the column names in first row, based on the sheet name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing the column names in first row, based on the sheet name
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. "Sheela" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing the column names in first row, based on the sheet name
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 "Sheela" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing the column names in first row, based on the sheet name
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
writing the column names in first row, based on the sheet name
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. "Barb Reinhardt" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Based new sheet names on cell value | Excel Programming | |||
Change sheet names based on cell contents | Excel Programming | |||
Add data to 2007 table based on column names? | Excel Programming | |||
Dynamic list based on sheet names | Excel Programming | |||
Find a time value in one column based on names in another | Excel Discussion (Misc queries) |