VBA - Apply A Code To All Selected Sheets To Group

Feb 24, 2014

I would like to group some columns to all the sheets that I will have selected. Unfortunately the below code only apply the code to the sheet I am looking at.

Sub Group()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
With ws.Range("F:Q").Group
End With
Next ws
End Sub

View 3 Replies


Apply Macro Code To All Tabs / Sheets

Feb 26, 2008

I havae the following macro which i recorded in Excel. I want this Macro to run after another macro that groups data and creats tabs. The following macro will then add a column and run an array formula. I think this can be done in a loop but i'm not sure how to do it. This is working but takes a long time and times out by the time it reaches the last tab.

Application.Run "TotalHrs"
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = "Invoiced Amount"
Selection.FormulaArray = _

View 5 Replies View Related

Apply Row Count Of Sub Group In Each Row Of Sub Group

May 28, 2014

In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.

View 2 Replies View Related

Apply Formula To Selected Cells Via Macro

Jul 6, 2009

i m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136

the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.

View 5 Replies View Related

VBA To Apply Borders Around Every Cell In Selected Range

Oct 19, 2012

I'm looking for the least amount of code to apply a border around every cell in a selected range. The standard With Selection approach is to go through six times for the sides, top, bottom, and insides, which results in a lot of code.

I attempted a single code line approach (Selection.Borders.LineStyle = xlContinuous), but I cannot seem to influence the color or thickness of the line. I'm not looking for fancy here...standard xlThin in black color is all I need.

View 3 Replies View Related

Assign Same Value To Selected Cells Within The Same Group?

Jul 29, 2014

I have a table with 2 columns A & B and desired outcome in column C (see attached sample).

I need to assign the same value to cells (in column A) located within the same group (in column A) using the following rules:

If any cell value within the same group = W220 or W210 or E240 or E250, then assign value "Group A"

If any cell value within the same group = P210 or C100, then assign value "Group B"

If any cell value within the same group = N230 or N250, then assign value "Group C"

View 3 Replies View Related

Group Shapes In A Certain Selected Area

Jan 10, 2007

I don't think this is possible, but I thought I would ask.

I have buttons on a sheet.

Then I have drawings, that I make, using different shapes.

Instead of selecting all shapes on the sheet, I want to just select the shapes in a range say B17:F28 so I can group them.

View 9 Replies View Related

How To Have Only One Checkbox In A Group Of 3 Selected At A Time

Mar 23, 2008

I have a spreadsheet that consists for 30 rows of groups of three checkboxes on each row. I want to have only one checkbox per row checked at a time. If the user checks one box while another it already checked then I want that checbox to be unchecked.

I want checkboxes to work like groups of option buttons. Actually, option buttons would be fine but with option buttons there is always one clicked but I need them all clear until the user clicks one. They will start off clear but if the user clicks one by mistake there is no way that I know of to clear it again. Checkboxes will clear again if you click it again so I thought I would use them.

how to have a group of three option buttons or checkboxes that will begin unchecked and be able to uncheck all three is one is checked in error,

View 9 Replies View Related

Group Selected Worksheets By Color

Jan 8, 2007

I would like to do is to sort only selected sheets. Can someone give me this additional code that can be incorporated in the code below. (If I just select the sheets I want sorted and run the code below, it sorts all worksheets irrespective of whether it is active or not).

Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
With ActiveWindow.SelectedSheets
For N = 2 To .Count ................

View 7 Replies View Related

Making Sure An Option Button Has Been Selected Within A Group Before Exiting Survey

Nov 26, 2009

I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.

View 2 Replies View Related

Apply To All Sheets Not Working

Mar 10, 2009

why the "apply to all worksheets" portion of this code is not working? I appreciate your time.

Sub Delete_0activityaccount()
Dim mywSheet As Excel.Worksheet
For Each mywSheet In ActiveWorkbook.Worksheets

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 8 Step -1
If Cells(i, 4).Value = 0 And Cells(i, 5).Value = 0 And Cells(i, 6).Value = 0 And Cells(i, 7).Value = 0 And Cells(i, 8).Value = 0 _
And Cells(i, 9).Value = 0 And Cells(i, 10).Value = 0 And Cells(i, 11).Value = 0 And Cells(i, 12).Value = 0 _
And Cells(i, 13).Value = 0 And Cells(i, 14).Value = 0 And Cells(i, 15).Value = 0 And Cells(i, 16).Value = 0 _
And Cells(i, 17).Value = 0 And Cells(i, 18).Value = 0 Then
End If
Next i

Next mywSheet

End Sub

View 9 Replies View Related

Apply Macro To Some Sheets

Jun 27, 2007

i used your recommended summary page that you posted somewhere in ozgrid. i have this workbook which has 6 worksheets.

1st sheet: summary page. this adds all the sheets between top and bottom
2nd sheet: "TOP"
3rd sheet: "Red"
4th sheet: "Blue"
5th sheet: "Yellow"
6th sheet: "BOTTOM"

i have this macro which performs some copy-paste-compute codes. my problem is this: i don't know what codes to use so that the macro will be applied to only those sheets between TOP and BOTTOM...

View 6 Replies View Related

Apply Same Formats To All Sheets But One

Jan 18, 2008

I'm trying to apply some settings to all sheets in my workbook except the first sheet called Total. For some reason, the settings are not applied to all sheets but simply stays on the sheet I select.

Also, how can I ensure the changes are only applied to the sheets: A, B, C, D etc. but not to Total?

Sub Test()

Dim ws As Worksheet

For Each ws In Worksheets

With Selection.Font
.Name = "Calibri"
.Size = 8
Selection.ColumnWidth = 35
Selection.ColumnWidth = 12
Selection.NumberFormat = "#,##0.00"
Selection.ColumnWidth = 12
Selection.NumberFormat = "#,##0.00"

End With

View 9 Replies View Related

Apply Conditional Formatting Once Across 168 Sheets

Jul 3, 2014

I have a workbook that contains 168 sheets of data (it's an extract from a PM tool) which is effectively a status report from each project in our portfolio. Contained within each status report are some financial data that shows a Plan number and a Forecast number for which I want to apply conditional formatting to this section (this is the same section for each sheet), to all the 168 sheets without having to go individually into each sheet. I have searched here and all the varying responses to a similar situation as mine, do not cater for the number of sheets that I have. And I need to do this on a monthly basis at monthend. So in the example below I want to apply conditional formatting if the Forecast (Cols D & G) are greater than Plan (Cols B & E). Is there a way of doing this just with the conditional formatting or would it need a VBA script?

Col A Col B Col C Col D Col E Col F Col G
Financial Summary - Selected Project Currency: USD

Current year total cost
Overall project cost

[Code] .....

View 1 Replies View Related

Apply Subroutines - Looping Through Sheets

Sep 28, 2012

I'm trying to apply subroutines that I wrote to all sheets in a workbook before a save but it's only applying them to the active sheet.

Here's my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
Next ws

End Sub

View 3 Replies View Related

Apply Codes To All New Sheets Added

Jul 25, 2014

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet1").Range("B3").Value >= -Date And Sheets("Sheet1").Range(" B3").Value

View 9 Replies View Related

Apply Macro To All Work Sheets

Dec 20, 2006

How do I apply 1 Macro to all the sheets in a Work book. That is one Macro should execute the function in all the worksheets of the Workbook.

View 9 Replies View Related

Apply Column Width To All The Sheets In Workbook?

Aug 28, 2013

I have a workbook with over a 100 worksheets and need a macro that will apply the following column width to all the worksheets. Col S → 4; Col T → 5; Col U 5.2; Col V → 5; Col W → 4; Col X → 7.5

View 2 Replies View Related

Apply Double Click Event To All Sheets

Jul 16, 2014

I am trying to apply a double click event to all sheets. It works if I apply to each sheet but I won't to prevent from having to copy and paste into each new sheet. I am trying this code in ThisWorkbook but doesn't seem to work.


Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
' If the cell is clear
If Sh.Target.Interior.ColorIndex = xlNone Or Sh.Target.Interior.Color <> vbCyan Then
' Then change the background color to yellow
Sh.Target.Interior.Color = vbCyan


View 3 Replies View Related

Apply Freeze Pane To All 31 Sheets Simultaneously?

Feb 22, 2014

I have 31 identical spreadsheets in one workbook. Is there a way to apply Freeze Pane to all 31 sheets simultaneously?

View 4 Replies View Related

Macro To Apply Subtotals To Multiple Sheets

Jul 7, 2006

I am trying to loop through all of my worksheets in my workbook to apply a subotal to each of the sheets. I can get it to work with applying to just one named sheet, but I cannot get the loop to work. The sheets named "data" and "PriceList" do not need the subtotal applied.

Below is the code I am using:

Sub SubTotals()
Dim LastRow As Long
Dim wsDst As Worksheet

View 5 Replies View Related

Apply Macro To All Sheets/Worksheet In Workbook

Aug 31, 2006

I need to apply the following code to all the sheets in my workbook (they are all identical format)

rivate Sub mymacro1()
Application .OnTime TimeValue("10:27:00"), "MyMacro1"
Dim objOL As Object
Dim objItem As Object
Dim lngRow As Long

Set objOL = CreateObject(" Outlook.Application")

lngRow = 6
Do While activehsheet. Cells(lngRow, 1) <> ""
If ActiveSheet.Cells(lngRow, 6).Value < Date Then
Set objItem = objOL.CreateItem(0) 'constant olMailItem = 0

With objItem
.Body = "The training review for employee: " & ActiveSheet.Cells(lngRow - 5, 2) & " is due today """.....................

I don't know what syntax to use to 'globalise' if you like the macro to perform the action in the code to all the sheets.

View 4 Replies View Related

Apply AdvancedFilter To Several Sheets & Copy Results To New Worksheet

Nov 30, 2009

The data to be filtered is in several sheets, and once filtered is to be copied to a destination sheet (in this case "Temp"). The criteria for advanced filter is on an altogether different sheet (in this case "Reports"). The macro is actually simplified for the purpose of the question, and I want to re-use the code several times, hence the use of variable "filterRng". When I run it, I get the subject error at the bolded line in the code below. I'm thinking that the Advanced Filter doesn't like a variable as a range reference, as it runs perfectly well if the commented out line below the problem line is used instead.

Sub Test()

Dim i As Integer
Dim rngData As Range
Dim filterRng As Range

Set filterRng = Sheets("Reports").Range("A121:K124")

Application. ScreenUpdating = False
Application.DisplayAlerts = False

View 4 Replies View Related

Apply The Code For Entire Row 4?

Aug 2, 2014

I have amended the code but it's for columns. I want this formatting for entire row 4 only and not for columns. Because it's disturbing my program if there is any data using ':' sign then the same formatting is applied which I don't want.

[Code] .....

The ':' sign is not changing to bold. I want to bold ':' sign too.

How to amend the code for entire row 4?

View 5 Replies View Related

How To Apply VBA Code To Every Workbook And Worksheet

Jul 29, 2014

I'm using the following VBA code that highlights a selected cell or range of cells when selected (to more easily see what cells I've selected).

[Code] .......

I'd like to use this code on every worksheet within every workbook that I open. Right now I've only inserted the code in "This Worksheet" in a single workbook and that's the only place it works. I tried creating an Add In with the code so that it worked in all workbooks, but it doesn't work.

View 2 Replies View Related

Macro: Look At The Last Code In Col And Apply To Next Sequence

Dec 14, 2008

Step 1. In Col E a list of amounts will be pasted on a daily basis.

Step2. I need a macro that will look at the last code in Col F and apply the next sequence so $36,543.00 will have OPS003, since i might paste more than one amount in Col E i would like the macro to do the same thing also in Col F. I have a formula in G which will tell me what amounts are outstanding and which have cleared.

AmountCode 12,545.00 OPS001 1,236.00 OPS002 36,543.00

View 9 Replies View Related

Apply Code To All Worksheets In Workbook

Nov 20, 2007

I recorded a macro in an Excel Workbook which contains 65 worksheets (--this is something received on a quarterly basis for which I have no control). The macro is successful within the workbook created however, a new quarter's data may or maynot have some of the referenced worksheets. 64.9 Waiv - 1 may exists and the others (2, 3, 4, etc) not. The macro fails and prompts for Debug if a worksheet does not exist.

Sheets("64.9 Waiv - 1").Select
Sheets("64.9 Waiv - 2").Select
Sheets("64.9 Waiv - 3").Select
Sheets("64.9 Waiv - 4").Select
Sheets("64.9 Waiv - 5").Select
Sheets("64.9 Waiv - 6").Select
Sheets("64.9 Waiv - 7").Select
Sheets("64.9 Waiv - 8").Select
Sheets("64.9 Waiv - 9").Select
Sheets("64.9 Waiv - 10").Select

Is it possible to alter the macro to look for and only invoke the code if a worksheet past 1 were to exist?

This is what I am requesting the macro do:

Sheets("64.9 Waiv - 1").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll ToRight:=5
Range("J8").Select ...............

View 9 Replies View Related

Code To Apply Calculation To Different Sets Of Data

Mar 5, 2013

I have an excel spreadsheet that performs a particular calculations using a large set of data. However, I have over 1000 sets of data that need to be feed into this sheet and obtain the output calculation. Obviously I don't want to have to copy and paste in each new set of data to obtain my result as this would take a very long time. I am brand new to VBA, so was wondering if there is a way to write code in VBA that will automatically perform these calculations for one data set, save the output into a cell, and then move to the next data set and so on?

View 4 Replies View Related

Apply VBA Code To Multiple Columns As Oppose To Just One

Dec 7, 2009

The code below restricts users from inputting anything other than an uppercase "X" in a cell within column N. If the user types a lowercase "x", it would automatically convert it to uppercase. If any other value is entered other than "X", a message box will fire.

The code is specific to column N (column 13). However, I would like to apply this to multiple columns, such as columns 13,14,17,20 .....

View 9 Replies View Related

Apply AutoFilter Across Columns Macro Code

Apr 30, 2008

I have to create autofilters dynamically on x columns based on the selection I make. I created this code but is not working.

LastColumn = Cells. Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
Range(Cells(17, 1), Cells(17, LastColumn)).Select
With Selection
.AutoFilter = True
End With

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved