Excel VB Programing

[sakthi_nj]

Could anyone help me how to program in excel using the variables.

I have done a coding for a set of data's in a sheet to copy and display in
the Index sheet.

Similarly i have to do the same for the remaining 46 sheets.

Could anyone help me how to do that with a single program, by changing the
sheet name with reference to the dropdown box value which i used to select
these 46 sheets.

The data range in all the sheets are same.

[mdmackillop]

Welcome to VBAX
Can you post a small sample workbook (not all 46 sheets). Use Manage Attachments in the Go Advanced reply section.
Regards
MD

[Bob Phillips]

Assuming the dropdown is Data Validation, just grab the cell value



With Worksheets(Range("C1").Value)

'do stuff with this sheet such as
.Range("A2:H2").Copy
Worksheets("Index").Range("A1").Paste
End With

[sakthi_nj]

Attached here is the sample program with 3 sheets. Here i manually copied the program from 1st sheet and just changed the sheet name for the 2nd sheet. Could you please advice is there any way that we can define the sheet names in the program as variables and change it with reference to the selection in "Pump Type"(ref my excel file).

Thanks for your replies.
Merci.

[Bob Phillips]

Did you read my response?

[sakthi_nj]

Thats the way i programed the 1st sheet. Please have a look at my excel sheet you would know better what my problem is. Instead of duplicating the program by changing the sheet name, Is there any possible ways to related that sheet name as a variable and ref. with the sheet selection in the first drop down box.

[Jan Karel Pieterse]

I added a userform which enables you to select the sheets to work on.
See module "modJKP" for the code.

[sakthi_nj]

Thanks for your Form Module. I want the same format what i have posted in Excel. I don't want to use userform. Can you advice me just to change only the string "CPX" with the sheet names with reference to a cell value. Please advice me.

[Jan Karel Pieterse]

Call the modified module I created with the sheetname as its argument.

So in the example below you call the subroutine called
'IForgotHowINamedTheModule' with the sheetname "CPX" as the argument:

IForgotHowINamedTheModule "CPX"