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
ADVERTISEMENT
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
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
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
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"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("I4").Select
ActiveCell.FormulaR1C1 = "Invoiced Amount"
Range("I5").Select
Columns("I:I").EntireColumn.AutoFit
Selection.FormulaArray = _
"=INDEX(VLookup!R2C2:R242C4,MATCH(RC[-3]&RC[-2],R5C6:R2500C6&R5C7:R2500C7,0),3)*R[3]C[-1]"
Selection.Copy
Range("I6:I1000").Select...................
View 5 Replies
View Related
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
Rows(i).Delete
Else
End If
Next i
Next mywSheet
End Sub
View 9 Replies
View Related
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
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 8
Columns("H:H").Select
Selection.ColumnWidth = 35
Columns("I:I").Select
Selection.ColumnWidth = 12
Selection.NumberFormat = "#,##0.00"
Columns("J:J").Select
Selection.ColumnWidth = 12
Selection.NumberFormat = "#,##0.00"
End With
View 9 Replies
View Related
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
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
color
Formatting
Next ws
End Sub
View 3 Replies
View Related
Jul 25, 2014
Code:
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
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
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.
VB:
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
[Code]....
View 3 Replies
View Related
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
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.
Code:
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
View Related
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
Dec 8, 2009
I am trying to set up a macro that hides certain columns of data in an automated spreadsheet that I don't need. How do I make the macro that hides the columns apply to all spreadsheets that are open except for the one I am in?
View 9 Replies
View Related
May 11, 2009
I am trying to record a macro that edits a rows data, and simply copies it to a new cell further along on the same row,. but i then want it to move to the next row down, and apply the same macro to that run, and continue until there is no data in the last row,. How do I get the macro to continue to the end of the data.
View 5 Replies
View Related
Jul 30, 2008
I've made a spreadsheet at home on Excel 2007 which has up to 5 Conditional Formatting rules per cell which works fine. The problem is when I save as 97-2003 version to send to work, the old version of Excel only supports 3 rules as you know, is there a way of applying 4 or 5 rules when specific text is populated in a cell on the old version of Excel?
View 14 Replies
View Related
Oct 3, 2009
I have a worksheet that I am always adding sheets to by duplicating an already existing tab. The tab ,"Current Invoice", is nested between sheets called "first" and "last". All duplicate sheets will also be nested between the "First" and "Last" tabs. What I would like to do is have a macro that affects all tabs between the "First" and "Last" only, even when the bookend tabs are hidden.
This is what I've started with.
HTML Sheets(Array("First", "Last")).Select
ActiveWindow.SmallScroll Down:=18
Range("U29:U190").Select
Selection.Copy
Range("AA29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W29:W190").Select
Application.CutCopyMode = False
Selection.Copy
Range("AB29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
View 4 Replies
View Related
Dec 2, 2009
I need to run the below code on all work sheets:
Sub Remove_Duplicate()
On Error Resume Next
Columns("a").Insert
With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
.Formula = "=If(CountIf(B$1:B1,B1)=1,"""",False)"
.SpecialCells(-4123, 4).EntireRow.Delete
End With
Columns("a").Delete
End Sub
Basically I have lots of worksheets and I want to remove the duplicates in column B for each work sheet with a duplicate entry...
View 9 Replies
View Related
Jan 18, 2014
i want to apply a macro for all sheets that start with "MCC ENTRY" and "MCC EXIT"
i.e. MCC ENTRY 1, MCC ENTRY 2, MCC ENTRY 3, etc. & MCC EXIT 1, MCC EXIT 2, MCC EXIT 3, ETC.
View 1 Replies
View Related
May 14, 2007
I poked around and found a macro to highlight the active cell, but I want to limit it to be active only for range A1:A37. Any ideas? I put in the Set OldCell = Range("A1:A37"), but all it does is turn every cell you pick blue and it stays that way, so I'm not sure how to limit it.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldCell As Range
Set OldCell = Range("a1:a37")
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
End If
Target.Interior.ColorIndex = 8
Set OldCell = Target
End Sub
View 9 Replies
View Related
Aug 10, 2008
Could somebody update the below code so that it actions all open workbooks except the master (Book2). Also, so that it appends the data (which is basically 2 neighbouring colums of data) to the Master starting in column A
Sub Format_RAW()
'
' Format_RAW Macro
'
' Keyboard Shortcut: Ctrl+s
'
Selection.AutoFilter
ActiveSheet.Range("$A$1:$P$5105").AutoFilter Field:=7, Criteria1:=Array( _
"H1", "H2", "H3", "H4"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$P$5105").AutoFilter Field:=1, Criteria1:= _
"Tu"
Range("G7:H7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
End Sub
View 9 Replies
View Related
Jan 31, 2009
Sub elaseval()
For each cell in Range(“B7:B50”)
Range("cell.Value").Select
Selection.Copy
Sheets("Input").Select
Range("$E$7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sensitivity").Select
Range("C” & cell.Row : ”T” & cell.Row").Select
Selection.Copy
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub
I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.
View 9 Replies
View Related
Dec 19, 2009
I'm Working on the next part of my long term project.
I need to count the number of Cells Selected in MacroTest2.xls column BG.
( Selection.count) ?
I need to Open Workbook MacroTest3.csv. It has a Header and only Row 2 filled with data.
(Windows("MacroTest3.csv").Activate)
I need to Pulldown/Copy row 2 a number of times so that the Number of Duplicate Rows Created is Equal to the number of Selected Cells in the Count in MacroTest2.xls.
I have been able to do it based on a Static Number of Selected Cells (3) ( the existing Row + 2 ) by recording the manual event with this code.
Rows("2:2").Select
Selection.AutoFill Destination:=Rows("2:4"), Type:=xlFillCopy
Rows("2:4").Select
I'm trying to understand how to declare the variable and apply it, so that the Number of Rows of Template Data in MacroTest3.csv is Equal to the Number of Selected Cells in MacroTest2.xls column BG.
This is part of a Macro that will then transfer some same selected Range of Cell Data as well. It is Currently working but not with a variable number of selected cells. I can post the whole code if it would help you to see the whole picture.
View 9 Replies
View Related
Jun 21, 2014
I have data in columns that I want to create charts for. However, this data is dynamic and the number of cells where there is data varies. The rest of the cells that don't have numbers have 0s, however if I applied a macro to the whole column all of the zeros would show up in the resulting charts. How can I create a macro where I can highlight just the cells (the ones with non-zero numbers) that I want a chart to be generated for. Or is there a way to ignore zeros/blanks completely in the macro?
Example: here are two columns with data. I want to make a macro that creates a chart by just highlighting the actual numbers and ignoring the zeros.
Mexico
0.171896
USA
0.132952
Germany
0.113626
Colombia
0.096198
[code]....
View 1 Replies
View Related
Feb 29, 2008
I'm trying to find a macro to apply conditional formatting to a large number of cells,
What I would like to do is when cell E96 has a value of a, cells E3:F95 are shaded in grey. Then when cell G96 has a value of a, cells G3:H96 are shaded in grey, and so on down to IU96 having a value of a and cells IU3:IV96 shaded in grey.
View 9 Replies
View Related
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
Oct 11, 2007
I have a workbook in which I have 31 sheets. I've also recorded a macro that works great when I run it on one sheet, but it comes up with an error when I try to run it on grouped worksheets. I searched Google, and a few articles I saw said that in order to run a macro on grouped sheets, you have to use loops. I don't know if this is true, but I don't know how to run loops anyway, so. I want to run the macro on 30 of the 31 sheets. I was going to put the code in, but when I did that my post didn't work, so I'm thinking there might be a limit on the length of a post.
View 5 Replies
View Related
Aug 5, 2008
I am creating a series of macros for a workbook, and at the beginning of each macro I unprotect all sheets and at the end of each macro I protect the sheets once more. The issue I am having is that even with these macros in place, I continue to receive an Excel alert message saying:
"The cell or chart that you are trying to change is protected and therefore read-only. To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password."
The macro will then run. Everything works as it should, but I do not want this error message to pop up. I have tried inserting
Application.DisplayAlerts = False
into the code, but it does not stop the message.
View 9 Replies
View Related
