Need help doing the vba project

[krishna008]

Hi All,

I'm newbie to the VBA, I'm planning to do some vba project. My problem statement is,

I've an excel sheet with SHEET 1 (Where i'm planning to keep all my macro functions) And Sheet 2 (My master data sheet), sheet 3 ....

I've to do below operations
1) A drop down list combobox which gives the list of all tabs in the workbook. Once I select the Tab from the list Next operation is step 2
2) I need to remove all the rows based on one column filter. (I need a combobox with list of all headers in that sheet (typically on Row A). If I select one column I need another combobox with all the values under that column. (basically I'm trying to filter the values ) once I filter the values I need to delete all the rows that are having the value selected in second ddlb.
3) After step 2, I need to delete few columns. I need a combobox with all the headers with select option)
4) Save the sheet 2 as a new file.

 

[hernantorres23]

Hi, let me help you, with you first step, the below code make a range named and then use it, in data validation

Book1
	A	B	C	D	E
1
2
3	List of sheets
4	Sheet3
5
6
7
Master

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=listSheets

Option Explicit

'Hernan Torres, Mikel ERP
'December 9, 2019

Sub hojas_en_libro()
'with this routine, you will always have a update list for all sheets in your book
Dim i, j As Integer
Dim nombre As String
Dim listSheets As Variant
Dim a As Range

Sheet1.Activate
Range("AZ:AZ").ClearContents
j = ThisWorkbook.Sheets.Count
Set a = Range("AZ2", Cells(j, "AZ"))
For i = 1 To j
nombre = ThisWorkbook.Sheets.Item(i).Name
Cells(i, 52) = nombre
Next
'then those list, will be add to names in workbook
'and finally also will be added in a validation list
ActiveWorkbook.Names.Add "listSheets", , , , , , , , , a
End Sub

 

[hernantorres23]

For more help, you will need provide more details.
Please, try prepare a data example using add-in specifically for this, it can be found here XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

 

[krishna008]

Hi, let me help you, with you first step, the below code make a range named and then use it, in data validation

Book1
	A	B	C	D	E
1
2
3	List of sheets
4	Sheet3
5
6
7
Master

Cells with Data Validation
Cell	Allow	Criteria
A4	List	=listSheets

Option Explicit

'Hernan Torres, Mikel ERP
'December 9, 2019

Sub hojas_en_libro()
'with this routine, you will always have a update list for all sheets in your book
Dim i, j As Integer
Dim nombre As String
Dim listSheets As Variant
Dim a As Range

Sheet1.Activate
Range("AZ:AZ").ClearContents
j = ThisWorkbook.Sheets.Count
Set a = Range("AZ2", Cells(j, "AZ"))
For i = 1 To j
nombre = ThisWorkbook.Sheets.Item(i).Name
Cells(i, 52) = nombre
Next
'then those list, will be add to names in workbook
'and finally also will be added in a validation list
ActiveWorkbook.Names.Add "listSheets", , , , , , , , , a
End Sub

Thank you for your help.