Macro To Group / Ungroup Multiple Columns?

Feb 21, 2014

What I need to do is group and collapse certain columns when Cell D4 in my Date tab reads Bud. If it doesn't read Bud, I need it to ungroup those columns and group and collapse other columns.

So for example: If I have 6 columns (A - F), when Date tab cell D4 reads 'Bud'. I want to group columns A and B, and only show C-F. If the cell doesn't read Bud, I want columns A and B to be ungrouped, and Columns E and F to be grouped and collapsed.

I've been racking my brain, using the Record Macro and just cannot get it correct. Granted I have created something similar to HIDE my columns, however I need it to be grouped so that higher management can ungroup to see some details if need be.

Sub GroupColumn()
Dim s As Worksheet
For Each s In ActiveWorkbook.Sheets(Array("Name1", "Name2, "Name3", "Name4"))
If Worksheets("Date").Cells(1, 4).Value = "Bud" Then


View 5 Replies


Group / Ungroup Columns Depending On Value?

Oct 18, 2013

I have a ton of worksheets that I maintain for Budget, LE, LE2, LE3. Every time we complete a Budget I need to group the LE Columns, and once we complete an LE, the budget column needs to be grouped and LE column ungrouped. So twice a year I need to make these changes. It is quite cumber some to change this for multiple tabs.

I already have a technical worksheet to dynamically change the date, naming etc. I want to be able to add a cell that will either read 200 (budget), or 300 (LE). Depending on what his cell reads, I want to group and ungroup their respective columns.

View 2 Replies View Related

Can't See Group / Ungroup Buttons

Dec 15, 2011

I am having a problem with one of my workbooks. I have grouped some columns, but now I can't see the button on the top of the grouped ones. I tried to group/ungroup again and, as there were some hidden rows, I tried to unhide them all and it is still not working.

View 2 Replies View Related

Group / Ungroup - Tree Structure

Mar 6, 2014

I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?

View 1 Replies View Related

Group / Ungroup Within Protected Sheet?

Jun 12, 2013

I have put the following code into my excel workbook to allow me to ungroup sections in a protected sheet. How can I change it to specify multiple worksheets?

Private Sub Workbook_Open()
With Worksheets("P5")
.EnableOutlining = True
.Protect Password:="rob", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

View 4 Replies View Related

Allow For Group/ungroup On Protected Sheet

Jun 1, 2006

I'm building a template for various users. I have protected the sheet with only certain cells available for input. The file is very large and there are some sections that can be group/ungrouped as the user desires.

however, once I protect the sheet, they can no longer click the - or + to expand and collapse. How can I enable group/ungroup on a protected sheet?

View 9 Replies View Related

Group And Ungroup With Mac Office 2004

Dec 19, 2007

Can any Mac users out there confirm that if a sheet is protected that a user cannot group and ungroup rows using the "+" if the sheet is protected using the following...

With Sheet1
.Protect Password:="******", UserInterfaceOnly:=True
.EnableOutlining = True
End With

It works fine on my PC Office 2003 but I hear it won't work on a Mac but I can't confirm it.

View 3 Replies View Related

Protect Sheet With Freedom Of Group / Ungroup

Jun 1, 2014

Once I "protect sheet" , i can not group / ungroup the column / row. after protecting sheet, I want the freedom to group/ungroup the column.

View 2 Replies View Related

Group/Ungroup In A Protected Shared Workbook

Aug 20, 2008

I used some code to allow grouping/ungrouping in a protected sheet. This works great! Enable Outline/Outlining on a Protected Excel Worksheet. However, when I share this protected workbook, all of a sudden grouping and ungrouping is not possible anymore. I get the same error as before, when the workbook was protected, not shared, and did not have the code mentioned above. Error: "You cannot use this command on a protected sheet. To unprotect..." Assumption: Is it possible that the Workbook Open Event is not being triggered if the workbook is a shared workbook?

Question: How can I group/ungroup columns in a shared (and protected) workbook?

View 6 Replies View Related

How Do You Allow Hide/unhide Or Group/ungroup Features While Protected

Dec 20, 2007

We have a file that needs to be updated monthly by running macros, but the macros are being blocked by protection. I would like to put some code at the start of the macro that unlocks all sheets and then at the end locks them up again. Actually, all I really need is a macro to do just that unlock all sheets, and then another one that locks them back up again as we will need to lock and unlock at various times and it is annoying to have to do it by sheet ....

View 14 Replies View Related

Copy Multiple Columns Groups To 1 Group Of X Columns

Aug 6, 2009


The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............

View 7 Replies View Related

VBA - Ungroup All Rows Using Macro?

Mar 9, 2014

I've Sheet with Group off rows i want to ungroup All rows and do some calculation then

View 1 Replies View Related

Reffering To A Group Of Columns

Jun 18, 2009

I need some formula to enable, me ,to make some manipulations. What I need is to present the colored cells (in row 3). I will use those results in another formula where I will use address etc. I assume it can be accomplished with a combination of COLUMN() and MOD or OFFSET - but I could not find it myself.

View 2 Replies View Related

Group And Transpose Columns

Apr 6, 2004

How do I transpose as shown below?
I have to columns of data, Site number and Disc number. I have sorted by unique site number but I don't know how to transpose the data.

******** ******************** ************************************************************************>Microsoft Excel - testing.xls___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

View 9 Replies View Related

Group And Outline Columns

May 25, 2007

i have seen this cool feature in a workbook, but I am unable to find out how implement it myself. I can't really explain it (thus I am unable to find good search results) so I have added a little picture of what I mean. Its about adding controls for hiding and unhiding columns. How do I get such nice plusses and minusses into my workbook? I use Excel 2002

View 4 Replies View Related

Move Group Of Cells Over 4 Columns

Jan 21, 2009

I need to move a portion of info over 4 columns. I need to do this after a certain criteria is met.

1 Mem# Mem Name Sta Area
2 123C Joe Smith G bev
3 4 Wine 4.95 6
4 5 wine 5.95 6
5 2 food 6.95 5
6 124G Fred Choi C Bev
7 4 Wine 4.95 6
8 5 wine 5.95 6
9 2 food 6.95 5

A3, B3,c3,d3 need to shift over 4 clmns then when nxt member #(a6) is met rows below need to shift over 4 clmns until nxt mem #, so on and so forth. Please Help. New to the macro scene.

View 10 Replies View Related

Macro To Multiple Two Columns Then Sum?

Aug 10, 2009

I have a work book with 97 sheets, all of the sheets are identical in format but contain different data. I need to multiple column F (which is a price) by column G (which is a quantity). I then need to sum the results and spit this number out on a new sheet. So in then end I would have 97 entries on the new sheet with this new sum which would give me are total stock value on hand.

View 4 Replies View Related

Pivot Table - Group Columns By Date

Jun 16, 2014

Not sure if this is possible but I have a sheet of data which is effectively a project plan. It has a list of resources, role and phase etc. I then have lots of rows which are w/c dates and under each "week" I have the amount of hours or days they work. So for each person I can record their hours etc.

I then need a Pivot which basically collates the data and displays each phase's total hours but month. There maybe multiple phases or and each person may appear in each phase

I've attached an example - Excel Problem.xlsx‎

View 1 Replies View Related

Tab: Show The Marks When I Group The Columns Or Rows

May 14, 2009

I have this tab that won't show the marks when I group the columns or rows.

I know it is grouped becuase if you copy the data to the blank tab the marks appear fine. Copying the data is an easy work around but I would really like to know the root cause of this. The issue was only with this tab. I used to have a ton of other tabs and they grouped fine.

View 3 Replies View Related

Group Columns Of Unsorted Data Based On Key Table

May 1, 2008

I need to create a vba macro which will sort data according to a code it is denominated by and then will apply specific values from a code key to the data. I really need to have some help with this as although my vba is getting a bit better this is definately beyond me.

View 3 Replies View Related

Macro To Act Differently On Selected Multiple Columns?

Jan 18, 2014

I have data spread over several columns and this data is added from different files which has a different format than the one required to be in the Consolidated File.

I get results from a portal for Insurance certification, which has Exam Date, Issue Date and Validity Date which is copied manually and pasted from this file and to my master consolidated sheet...then I have to fetch other details for these certified employees from the HR Database which has the data in different formats...

So have to manually copy the format from the previous rows and then copy to the copied data, since this is done more than once in a day its tedious and time consuming so can this be done with a Macro..

I made my own small code but dont know how to make it act differently on selected columns....

I would select the entire matrix and then want the macro to run differently, taking into consideration the column names


I also need to add a vlookup formula for some column based on the column B's value...

Ex: This is for Col C

[Code] .....

So how do I write the code in such a way that the code acts on its own differently.

View 14 Replies View Related

Macro - Copy Multiple Columns Down To End Of The Data?

Feb 18, 2014

I am trying create a macro that will create a new work sheet on an existing workbook. I have minimum experience writing a macro, but I do know how to effectively record one. The issue is, I need the macro to copy multiple columns down to the end of the data and pasting it into the new workbook at the end of the data already pasted-- rows of data are frequently added to the workbook.

View 4 Replies View Related

Delete Multiple Columns In Different Range Using Macro

Aug 6, 2012

Selection.Delete Shift:=xlToLeft

When I wrte a macro as above, i get error "the command cannot be used with selections that contain rows or columns and also other cells. Try selecting only entire rows, columns or just group of cells".

Actually i want delete all columns except B:B and F:F

View 2 Replies View Related

Group On Multiple Worksheets

Dec 14, 2004

In an Excel Workbook with multiple worksheets set up exactly the same way, is there a way to select all sheets and perform "group and Outline" functions. I find that when I select all sheets, the "group and outline functons are not functional.

View 2 Replies View Related

VBA To Ungroup All Shapes On A Page

Feb 23, 2007

I need code to do this: When I select a range of cells, say E20:H24 then hit a button the code behind the button would ungroup all the shapes in that area. So if I have grouped shapes elsewhere, they would be uneffected.

View 9 Replies View Related

Ungroup Sheets Before Saving

Sep 4, 2008

I don't do a lot of VBA, so this should be really simple, but I'm going to defer to the experts! Thank you! I often need to group tabs in Excel 2007, but oh the horror if it saves while those tabs are grouped and someone opens it up and starts typing! Is there a piece of code I can enter that will ungroup all the tabs before the file saves?

View 9 Replies View Related

Ungroup Rows On Cell Click

Jan 30, 2007

Is it possible to ungroup all rows using a cell click instead of the default option that excel gives?

I know this seems a bit pointless but i submit my excel spreadsheets to many people that do not notice the plus & minus signs that excel creates & misses the hidden data.

This way would allow me to put a simple Open / Close text in a cell that would be obvious to click on.

View 4 Replies View Related

Group Multiple Sheet Tabs Into One Master Tab?

Mar 10, 2014

Is there a posibility that using VBA i can group sheettabs (Sheet 1, Sheet 2, Sheet 3) into 1 single master tab (ex. "MASTER") ?

Since I will be having 20 sheets in a excel file I would like to group them together.

View 4 Replies View Related

Sorting Data In Multiple Tabs By Group

Dec 31, 2012

I have imported data into excel arranged as per the following and there are separate tabs per year. I a trying to summarize and phase the balances per client..i.e 07,08,09,10,11,12.

Therefore if client x balance in 2007 = 10, 2008=11, 2009=10, 2010=2, 2011=3 and 2012 = 5 and so on...then total the balance owed as of now and also phase it as illustrated below. I've tried vlook up's?


[Code] .....

View 1 Replies View Related

Formula Reference Cells & Ungroup Worksheet

Oct 21, 2007

"Enter a formula that adds the total gross sales for the first quarter in cell B6 in the Q1 worksheet and total gross sales for the current quarter in cell G18 in the Q2 worksheet"

**First ungroup the grouped worksheet

How am i going to formula a total gross that i don't even know what is the first quarter? i don't see anything for first quarter? and in Q2 worksheet G18 i already did =SUM formula Ungroup the worksheet? i don't think my worksheet is even group?? how i ungroup it?

View 14 Replies View Related

Copyrights 2005-15, All rights reserved