Excel - hide colum based on cell value on each worksheets
Asked By Norvascom
31-Jan-10 11:36 PM
I have a workbook with several worksheets. I am trying to write a VBA
macro that would look at row 5 to see if any cells indicate "Hide
column". If it does, it would hide all the columns indicating "Hide
column" on this worksheet. Then it would continue the same process to
the next worksheet, and the next one ...
I currently have the macro working for the individual worksheet (see
below), but I would like to have it work to do all the worksheets of
the workbook.
Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For a = 5 To 200
If Range("A5").Offset(0, a).Value = "Hide column" Then Range
("A5").Offset(0, a).EntireColumn.Hidden = True
Next a
Application.ScreenUpdating = True
Thanks in advance for your help.
Sheets
(1)
Worksheet
(1)
Workbook
(1)
Macro
(1)
VBA
(1)
EntireColumn.Hidden
(1)
XlCalculationManual
(1)
HideColumn
(1)
Per Jessen replied to Norvascom
Hi
This should do it:
Sub HideColumn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each sh In ThisWorkbook.Sheets
For a = 5 To 200
If sh.Range("A5").Offset(0, a).Value = "Hide column" Then
sh.Range("A5").Offset(0, a).EntireColumn.Hidden = True
End If
Next a
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Regards,
Per
Norvascom replied to Per Jessen
en
=3D True
a4d-aadb-d108ebce68b7@d30g2000vbl.googlegroups.com...
Thanks, it works perfectly.
Excel Outils pour optimiser macro? Bonjour: Je souhaiterais savoir s'il existe des outils et ou techniques pour diagnostiquer et / ou optimiser et / ou nettoyer le code d'une macro afin d'augmenter la rapidit = E9 de traitement ou les points de ralentissement. Remerciements et et merci. Est-ce que tu peux m'aider = E0 identifier les elements de la macro ci- dessous = E0 modifier pour acc = E9l = E9rer la vitesse de traitement? Beaucoup de ces lignes ont = E9t = E9 r = E9alis = E9es avec l'enregistreur de Macro ce qui = ne doit pas participer = E0 la fluidit = E9 de la macro. Remerciements et sinc = E8res salutations, Christophe Sub ShowtimeCompanyScheduler() If MsgBox(Prompt: = 3D"Are you really _ Title: = 3D"Generate Company Schedules") = 3D vbNo Then Exit Sub End If ' Showtime Scheduler Macro ' Macro enregistr = E9e le 29 / 04 / 2003 par Christophe Application.ScreenUpdating = 3D False ' Unprotect Worksheet ActiveWorkbook.Unprotect Password: = 3D"* ** **" Worksheets("Company Data").Select ' save last changes made at Worksheet "Country
Excel Drawing across Data from different workbook using long list. Hi folks, This one's a real doosie! :rolleyes: :-( Although some of have two workbooks. Book1 is my user interface, and Book2 is just a data storage workbook with a single worksheet. In Book2 I have a column of names for which I have created a dynamic all the other data for that name from Book2 will be drawn over using the Macro. At present I think I have to write this buttons Macro to use SELECT CASE. But I'll have 3000 CASE value lines of code to write. Surely there has to be a better way than typing in 3000 lines of VBA code for this? . . . . can I use a few different SUB / END SUB sections, and denote X or something in the code to draw the data across, and in my button macro only denote what X is equal to, VBA automatically adjusting to the value of X ? Now for the doosie. . . . . . I actually have 72
Excel How to copy rows into an Excel *template* with vba My vba knowledge is very limited. Appreciate it if someone can show me how to do this it from the master list into an Excel template and save it as a new workbook with the Product Family ID appended in the name. I also need the Prod. Family Family ID | Country Sample Excel Template: = = = = = = = = = = = = = = = = = = = = = = There are 3 worksheets in the template. 1st & 3rd worksheet just contains instructions & summary. The 2nd worksheet, "Product List", is the worksheet where I want to copy the data in from the master list. Product Family ID Excel template contains macros and modules (the Custom calculation fields in the template are custom vba functions, and there's a bunch of other code under Worksheet_Change and in the "Product List" worksheet itself). In addition, for each new workbook created: 1) The worksheet in the Excel template
Excel Excel button problem Hi All I have a macro that copies a worksheet in the active workbook and puts it into a new workbook - then formats it and deletes any buttons on the worksheet. On the first click on the button the macro works ok. On the second click, it fails because the all assigned macros on all buttons in the active workbook changed from "mba" to "book1!mba". Book 1 does not exists (was not opened, was have never experienced this problem before?? Can anyone help to solve this problem? FYI The macro to do this is called "mba". It lives in the active workbook within "Module 1
PS For what it's worth, I tried recording the process of clicking on a macro button to bring up the "form" and doing these 4 steps but all that was recorded was: Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10 / 20 / 2007 by Dean Application.Run "'CPT-10-19-07 DM-3.xls 60 blank little circles. When you select one of these buttons it is running a macro that is using GetOpenFilename. What you need to do is create a table of project numbers and filenames. This can easily be done using an Array statement in VBA Filenames = Array("Name1", "Name2", "Name3"). You could also have a base name for each file do is to post the old code at this website because you need a new macro. You can't just have a new macro call the old macro because the method of selecting the filenames will be different. I think from your description