Code changes automatically with the sheet name.

[RAJESH1960]

Hello Experts

I have a query. How is it possible to change the name of the sheet in the code? For instance, the name of the sheet is “Kotak” then when I run the code it should show “Kotak” in the code. If I change the name of the bank to “ICICI Bank” then the code should automatically select / detect the sheet name and change to “ICICI Bank”.

 

[RAJESH1960]

I might be misunderstanding what's changing... if the sheet name itself is changing, you can reference the sheet's codename (instead of its index or its name as it appears in the tabs).

With a sheet selected, if you go to the immediate window in VBA and run ?activesheet.codename, it will return Sheet12 or similar. Then, you can use With Sheet12 instead of With Sheets("Bank"). Sheet12 will always point to the sheet regardless of what it's named.

I was just imagining whether it is possible to include a code in such a way that when the code is run, it will open a message box like which will ask to enter the name of the bank. Once I enter the name of the bank the code can run till the end and also replace the (as per details) to the name of the bank as written in the code.

 

[Joe4]

I was just imagining whether it is possible to include a code in such a way that when the code is run, it will open a message box like which will ask to enter the name of the bank. Once I enter the name of the bank the code can run till the end and also replace the (as per details) to the name of the bank as written in the code.

That is not difficult to do. But you would use an InputBox, not a MsgBox, as MsgBox's cannot take free-form input, i.e.

Dim bank as String
bank = InputBox("Please enter the name of your bank")

Then if you wanted to give the ActiveSheet that name, you could do so like this:

ActiveSheet.Name = bank

And you could refer to this sheet name by variable in the rest of your code, i.e.

With Sheets(bank)

So everything in the code is dynamic, and nothing is hard-coded.

 

[RAJESH1960]

That is not difficult to do. But you would use an InputBox, not a MsgBox, as MsgBox's cannot take free-form input, i.e.

Dim bank as String
bank = InputBox("Please enter the name of your bank")

Then if you wanted to give the ActiveSheet that name, you could do so like this:

ActiveSheet.Name = bank

And you could refer to this sheet name by variable in the rest of your code, i.e.

With Sheets(bank)

So everything in the code is dynamic, and nothing is hard-coded.

Too advanced for me to understand. It will take time to write these kind of codes. ?

 

[Joe4]

Too advanced for me to understand

There is only a few lines of code there, and nothing complex.
There is lots of information that can be found out there with Google searches, like this one: InputBox Function in Excel VBA

If you are going to be using VBA code a bit (which appears to be the case from most of your recent posts), I would recommend picking up a good introductory book on it or taking some on-line learning. Either that, or hire a good consultant to write all the code for you!

 

[RAJESH1960]

There is only a few lines of code there, and nothing complex.
There is lots of information that can be found out there with Google searches, like this one: InputBox Function in Excel VBA

If you are going to be using VBA code a bit (which appears to be the case from most of your recent posts), I would recommend picking up a good introductory book on it or taking some on-line learning. Either that, or hire a good consultant to write all the code for you!

Joe4. During lockdown, I have watched more than 500 videos and learnt from scratch, excel and a bit of advanced excel in the past one year. I am also learning the codes shared by the group and I really appreciate their help. Except for 2-3 codes I understand how the code works. It is just out of interest I am learning coding and what ever is connected to accounts. There is so much you could do with excel and VBA to make accounts easier and I am trying to do so. I am having fun and not willing to hire a consultant to write the codes.?

 

[Joe4]

There is so much you could do with excel and VBA to make accounts easier and I am trying to do so. I am having fun and not willing to hire a consultant to write the codes.

Excellent! Glad to hear it!
I am always happy to hear about people who are willing to learn it themselves instead of having someone else do it for them.
You are right there is a lot to learn with VBA. Just keep plugging away at it, and you will get there.
Just make sure that you learn it all in a logical order. It can be tempting to jump around, but it is important to build a good basic fundamental knowledge in which to learn from. Things like using Input Boxes are variables are pretty basic things that should be able to find in most beginner VBA books.

Good luck Excel-ling!

 

[RAJESH1960]

Excellent! Glad to hear it!
I am always happy to hear about people who are willing to learn it themselves instead of having someone else do it for them.
You are right there is a lot to learn with VBA. Just keep plugging away at it, and you will get there.
Just make sure that you learn it all in a logical order. It can be tempting to jump around, but it is important to build a good basic fundamental knowledge in which to learn from. Things like using Input Boxes are variables are pretty basic things that should be able to find in most beginner VBA books.

Good luck Excel-ling!

Thanks Joe4 for understanding. By the way, my project is tested and completed. The bank data is the sheet sent by the client. What I was trying to do was clean the data, arrange the rows and columns with the required format and generate an xml file which will be saved on the desktop. These were regular steps for every file send by the client and it took almost an hour to generate an xml file. Now at the click of a button I can generate an xml and import it to our office software. But there are a few problems I am facing. ?The code doesn't run when the rows of the sheet are different. At a few places I need help to select ranges to copy and paste which can be done in altering the code with some advanced codes which I am not good at. I am posting each problem at a time and collecting the right information. Will try and complete it tonight.

 

[Joe4]

I am posting each problem at a time and collecting the right information.

Sounds like you are going about it the right way.