Using input excel cell to define sheet name in VBA code

KG85

New Member
Joined
Jul 11, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Replace this line of code:
VBA Code:
Set ColumnName = Worksheets("Sheet1").Rows(1).Find(cell.Value, , xlValues, xlWhole)
with this line:
VBA Code:
Set ColumnName = Worksheets(MonthName(Month(Date))).Rows(1).Find(cell.Value, , xlValues, xlWhole)
 
Upvote 0
Replace this line of code:
VBA Code:
Set ColumnName = Worksheets("Sheet1").Rows(1).Find(cell.Value, , xlValues, xlWhole)
with this line:
VBA Code:
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?
 
Upvote 0
Change the reference sheet name (in red) and reference cell (in blue) to suit your needs:
Rich (BB code):
Set ColumnName = Sheets(Sheets("instructions").Range("A1").Value).Rows(1).Find(cell.Value, , xlValues, xlWhole)
Hopefully, I understood correctly.
 
Upvote 0
Solution
Change the reference sheet name (in red) and reference cell (in blue) to suit your needs:
Rich (BB code):
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
 
Upvote 0

Forum statistics

Threads
1,224,226
Messages
6,177,277
Members
452,765
Latest member
Erka Gizli

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