Counting the number values in a column which is dynamic

[nynamyna]

Hello everybody

I have a macro to count the number of values in a particular column of a sheet and to display it (sheet name is also fixed in this macro which should be changed) This is the forumula I have

=COUNTIF(Comparisons117!G2:G15238,"Hit")

The above formula counts the number of times the word "Hit" repeats in a specific range of cells in a sheet named Comparisons117.

But what I want is to count the number of times the word "Hit" is repeated in a particular coulum and the range should be dynamic. The sheet from which it counts should be the sheet next to the sheet where I am displaying this value

Thanks
Bharath