Using input excel cell to define sheet name in VBA code

[KG85]

I am using VBA code to delete columns in an excel sheet that are not required. Part of the code references a sheet name. Can I insert additional code so that the worksheet name (referenced here as Sheet1) is pulled from an input or referene cell in the workbook? I need to change the worksheet each month to the name of the month but don't want to have to change the VBA code each month.

Sub DeleteColumns()

Dim delColumn As Range, cell As Range

Set delColumn = Worksheets("Delete").Range("A1:A59")

For Each cell In delColumn.Cells
If Len(cell.Value) > 0 Then
Set ColumnName = Worksheets("Sheet1").Rows(1).Find(cell.Value, , xlValues, xlWhole)
If Not ColumnName Is Nothing Then ColumnName.EntireColumn.Delete
End If
Set ColumnName = Nothing
Next cellUpload Image

End Sub

 

[mumps]

Replace this line of code:

Set ColumnName = Worksheets("Sheet1").Rows(1).Find(cell.Value, , xlValues, xlWhole)

with this line:

Set ColumnName = Worksheets(MonthName(Month(Date))).Rows(1).Find(cell.Value, , xlValues, xlWhole)

 

[KG85]

Replace this line of code:

Set ColumnName = Worksheets("Sheet1").Rows(1).Find(cell.Value, , xlValues, xlWhole)

with this line:

Set ColumnName = Worksheets(MonthName(Month(Date))).Rows(1).Find(cell.Value, , xlValues, xlWhole)

Thank you for this - I don't think I explained myself clearly. I have already set up the workbook to include 12 sheets with each sheet having the name of the month - so when I run the data for the august sheet, I might be doing it in October. What I want to be able to do is define on the instructions sheet a cell that can input which month I am running, so that it refers to the sheet i am working on at the time... It may be I also do other sheets with different titles so need the flexibility to input which sheet I am referencing. Is it possible to use an input cell for the sheet name?

 

[mumps]

Change the reference sheet name (in red) and reference cell (in blue) to suit your needs:

Set ColumnName = Sheets(Sheets("instructions").Range("A1").Value).Rows(1).Find(cell.Value, , xlValues, xlWhole)

Hopefully, I understood correctly.

 

[KG85]

Change the reference sheet name (in red) and reference cell (in blue) to suit your needs:

Set ColumnName = Sheets(Sheets("instructions").Range("A1").Value).Rows(1).Find(cell.Value, , xlValues, xlWhole)

Hopefully, I understood correctly.

Fabulous - thank you so much that has worked like a treat

 

[mumps]

You are very welcome.