Hi, there.
I know it will take some time for you to go to a stage where
you yourself can start coding extensively and perform lot of
tasks which otherwise will take lot of time to do that manually.
But at this stage, where we are just beginning to get into
the coding part let me show you one trick.
Which involves little coding yet at the same time will give you
immense benefit.
What I'm going to do is, I'm going to combine the power of
Google search with a certain common sense technique.
And a specific technique how to copy the code in the Google page
or any of the blog post and paste in the certain section
of Excel and benefit out of the code.
So take an example.
Let's say we have a set of worksheets which talks about
the different countries, different cities perhaps.
And they are all locations and also additionally I have a
sheet, couple of sheets called working 1,2 and 3.
Now what I wish to do is, of all the worksheets I want to create
an index of names of the worksheets for an easy display
of the content for the end user.
And all these sheet names once they are listed,
should be linked to the respective worksheets.
So that when somebody clicks on it,
it is taken to that worksheet.
Maybe I don't know how to code it.
Well, I atleast know my question.
So what I'm going to do is I'm going to go to Google,
and I'm going to type in my task.
What is my task?
The task could be I wish to create an index of hyperlink
worksheet names or index of links to worksheets.
We will try to become as specific as possible but do note
that please start your search with these three words.
VBA code to.
Now what I found is people do search through internet for
getting the codes but they don't add these 3 important words
which are VBA code to.
If you do add these 3 important magic words VBA code to
before any task that you wish to accomplish, your search results
will be very specific and accurate.
So now let me proceed.
VBA code to put a verb which signify what you wish to do.
And as soon as I write VBA code 2 create it gives me certain
hints, recommendations.
You may get the same recommendations.
Maybe different, this depends on the Google rankings
and the search results.
But based on that let me proceed.
Create index okay I'm getting something called
"index of links to worksheet," let me click on it.
And as the search results come up, I start reading
the headings of the search results.
As one of it says create links to all sheets in a worksheet.
Create a sheet index of Excel worksheets.
Both look same although the wordings are different.
Create an index page or table of contents.
Now since you gave the term VBA code 2, it is leading you to
the accurate results which might be a forum.
Which might be a blog post by different authors.
And since somebody has asked this question in the past they
have answered it out of their time.
And why then we have to reinvent the view.
Why do have to recode it.
If the code is certainly available on the internet.
Well, let me do one thing.
Let me go to create links to all sheets.
The first one.
And as this page opens, I use this page quite often
for reference.
Once this page loads up, I see a blog which says the macro
demonstrated below create hyperlinks to all sheets in
current workbook.
Although let the pop up be closed, yeah.
And I proceed ahead and it says VBA code.
Now even if they didn't say VBA, you notice from our earlier
learnings the head and tail of a macro.
Well this is the head and this is the tail.
That's exactly how I spot a macro even if though it doesn't
look differently.
Now if you're using Chrome which I will strongly recommend.
You'll notice on the top right hand side panel,
you may get this tag sign which says view source.
I will click on it and it opens a window.
And I need to copy this from head till the tail of the macro.
If you're copying from the window, sometimes what happens
you try to copy this but it also copies the numbers.
No I don't need to copy the numbers.
So use a technique which allows you to copy only the code part
not the numbers.
So it depends on the website.
Sometimes you can simply select this
and get rid of the 1 to 11 digits.
Sometimes you get this view source.
Either ways we'll copy from head to tail.
Copy that.
And then how do I use this.
I go to Excel.
I go to developer.
Now this time I'm not recording a macro why because already
a user has written that macro for me.
So I'm going to go to macros.
I see an empty window, ok.
Let me write a dummy name.
Let's say xyz, ok, even a single letter window for that matter,
x.
Now as soon as I press x in the macro name, x will tell me there
is no such macro and hence, it has activated the button called
"create." Ok, fine, let me go to create.
What it does is, if I look at the project explorer, it takes
me right to the workbook which is open and in there
there is a module which automatically it has created.
Additionally in the code panel which I see right now before me,
there is a dummy macro.
Dummy macro means there is a head and there is a tail.
There's no action in between.
Now how do you paste the macro.
Certainly not pasting in between the head and tail.
You cannot have two heads and two tails here.
This is completely erroneous.
So either you delete this current existing one
and then paste it.
Or you might paste below it which is also fine.
You notice a line has been drawn to demarcate the difference.
Either ways it should be between a macro.
Let me delete this dummy macro.
And the name of the macro it says sub create links to all
sheets, fine.
I'm just quickly sifting through the waste codes although it
doesn't makes sense to a user like me right now.
If I'm learning the macros, well I'll discuss this
a little later.
At this moment I just wish to see that if I'm able to get hold
of a code on the internet and I'm able to copy and paste at
the right location of the VBA code.
How do I run and benefit out of it?
I go to Excel.
I then go the macros.
Now since you had deleted the dummy macro and you had inserted
a new code.
The name of the new code being relate links to all sheets.
Alright.
Let me keep my fingers crossed.
And run it.
Notice it does exactly what it is meant for.
It is creating an index of links to all the worksheets.
Did you know how to code?
Perhaps not.
Does that stop you from benefiting out of VBA code?
No.
What is the common sense needed?
Well, define your tasks.
What is that, that you want to achieve.
The key words should be relating to Excel's words.
So when you refer to workbook, it means the workbook of Excel.
Do not use file in that case.
Use worksheets, use hyperlinks, use cells.
These are all the technical terms that you might use.
And do what thereafter, you combine that search task with
the 3 magic words which is VBA code to.
Now you through various search results on the internet.
And if you're using these three magic words most likely you're
going to get more accurate results than not.
And use that code, copy that code, go to macros, write a
dummy name A, instantly what you get is create and once you
create, you might paste that code in here
below or maybe above.
But never in between a head or a tail of another macro.
So this is something we saw, the basics of using Google
and a search in such results to get your work done.