Add a column Hi, I want to add columns in Sheet2 using a button in Sheet \'Main\

Question

Add a column

Hi,

I want to add columns in Sheet2 using a button in Sheet 'Main'.

The first column that I can add is column C and row 1 in Sheet2.

I made a button and when I click this button, I can add worksheets.

What I want to do is that I want to add columns in Sheet2 and each column header is the same name as new worksheet's name.

For example, I add a sheet, "State", then automatically add a column and then the "State" is on column C1 in Sheet2.

If I add second sheet, " Code", then automatically add a column and them the the column D1 value will be "Code" in Sheet2.

and so on.

Thanks!!

Explanation / Answer

.^Try assigning the following code to the button on the Main sheet:;'@

'==========>>

Option Explicit
'---------->>
Public Sub Tester()
    Dim WB As Workbook
    Dim SH As Worksheet, newSH As Worksheet
    Dim LCol As Long
    Dim Res As Variant
    Const ShName As String = "Sheet2"                            '<<===== Change

    Set WB = ThisWorkbook
    Res = Application.InputBox(Prompt:="Please insert the name of the sheet to be added", _
                               Title:="Sheet name")
    If Res = False Then
        Exit Sub
    End If

    With WB
        Set SH = .Sheets(ShName)
        Set newSH = .Sheets.Add(after:=.Sheets(.Sheets.Count))
        newSH.Name = Res
    End With

    With SH
        LCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        If LCol < 2 Then
            LCol = 2
        End If
        .Cells(1, LCol + 1).Value = Res
    End With
End Sub
'<<==========

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.