• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One Macro - Lots of Worksheets. Is it Possible

C

Certificates!

Member
I have a workbook which will eventually have about 100 worksheets.

The sheets are all exact copies of each other, though the data is different.

These two macros are currently on the first few worksheets. Is it possible to come up with something that I can put on the first page instead of doing each page separately.

I know very little about this at all and had to google to find the two I have below:


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Not Intersect(Target, [F3]) Is Nothing Then
            Select Case UCase(Target)
                Case "MEDAL"
                    [X1].EntireColumn.Hidden = True
                    [AB1].EntireColumn.Hidden = False
                    [AE1].EntireColumn.Hidden = True
                Case "STABLEFORD"
                    [X1].EntireColumn.Hidden = False
                    [AB1].EntireColumn.Hidden = True
                    [AE1].EntireColumn.Hidden = True
                Case "PAR"
                    [X1].EntireColumn.Hidden = True
                    [AB1].EntireColumn.Hidden = True
                    [AE1].EntireColumn.Hidden = False
                Case Else
                    [X1].EntireColumn.Hidden = False
                    [AB1].EntireColumn.Hidden = False
                    [AE1].EntireColumn.Hidden = False
            End Select
        End If
    End If
End Sub
Sub Hide_Columns()
  Range("I:J,N:W, Y:AA,AC:AD").EntireColumn.Hidden = True
End Sub
 
Certificates!
Please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
  • Use Relevant words in the Title and in the tag Box, This will aid future searches.
  • Please post, new posts in the correct forums, not as Emails/Messages to people
    • You've opened Your thread to Ask an Excel Question instead of VBA Macros
      >>> Your opened thread has moved to the correct forum <<<
 
Last edited:
Certificates! said:
Is it possible to come up with something that I can put on the first page instead of doing each page separately.
Click to expand...
According to how Excel is designed then you can remove this VBA event procedure from all worksheets​
and use the ThisWorkbook module VBA event procedure called Workbook_SheetChange like explained in VBA help …​
​
 
I'm really sorry but I can't find the help you are refering to. I agree that it is better to read a solution for oneself and to learn it, but without knowing the source of the information, I'm stuck.
 
Last edited by a moderator:
On VBE side, enter on ThisWorkbook module, on top change (General) to Workbook, that creates a Workbook.Open event, procedure,​
on top right side change Open to SheetChange then delete Workbook.Open procedure.​
Write your code within the Workbook_SheetChange event procedure​
and do not forget to delete the Worksheet_Change event procedure from all worksheets modules …​
​
 
You misread obviously : as I wrote you must be in ThisWorkbook module rather than in any worksheet module !​
​
 
I didn't misread. I don't seem to have those options. Either it's too hard for me or your explanation wasn't clear enough. You decide.

Thanks for trying.

I'll call it a day on this one.
 
The reason you don't see that option is that you're not displaying the "Project Explorer" in your VB Editor. Try this:
1) Select View from the ribbon at the top of the VBE.
2) Click on "Project Explorer".

I expect that you'll then see the sub-window that he's trying to point you to.
 
BobBridges said:
The reason you don't see that option is that you're not displaying the "Project Explorer" in your VB Editor. Try this:
1) Select View from the ribbon at the top of the VBE.
2) Click on "Project Explorer".

I expect that you'll then see the sub-window that he's trying to point you to.
Click to expand...

Thank you for a much more helpful post. That's great.
BobBridges said:
The reason you don't see that option is that you're not displaying the "Project Explorer" in your VB Editor. Try this:
1) Select View from the ribbon at the top of the VBE.
2) Click on "Project Explorer".

I expect that you'll then see the sub-window that he's trying to point you to.
Click to expand...
 
You must log in or register to reply here.
Back
Top