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
Rows(i).Delete
Else
End If
Next i
Next mywSheet
End Sub
View 9 Replies
ADVERTISEMENT
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
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
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
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
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
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
Jun 21, 2013
I have a workbook which has multiple sheets all with the same format.
When I make a change in one sheet it seems to impact another. There cells are not referenced. I have selected all sheets and selected ungroup.
I need to update numbers on individual sheets, i get about 40% done and when I look back all the numbers are now different.
View 2 Replies
View Related
Feb 21, 2014
I have a macro to loop through all the sheets in a workbook and apply a filter in column C. the filter is by Date and all sheets have the exact same format. For some reason the macro will go through the loop but not change to the next sheet. The message box line however does go from sheet to sheet.
[Code] ......
I attached a 2 tab work book example. When i run the program, it only applies it to Sheet 1.
Attached File : Filter_Example.xlsm
View 3 Replies
View Related
Aug 8, 2014
By chance I have opened a sheet with some macro and after that 'right click' is not working in any of the excel sheets (even newly created ones) in sheet names tab.
i.e., I can't delete/rename/insert etc in any sheet by rightclicking the sheet name.
Even the Edit->'delete sheet' is disabled. However, Insert->worksheet is enabled.
How to make the right click enabled. (excel version 2003)
View 5 Replies
View Related
Jan 25, 2010
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
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
Nov 23, 2013
I want to apply conditional formatting by VBA. Cell Q2 contains the month number, (in this case 11). I want each cell in the range D2:D50 to be filled in red and the word 'UNPAID' appear if Q2 is greater than 10 and the cell is blank, otherwise no formatting at all. I'd like this to be triggered on the Worksheet_SelectionChange event if at all possible.
View 4 Replies
View Related
Sep 14, 2012
I am trying to create a macro (which will go in an add-in, using Excel 2007) which will apply a custom format to any selected cells which have their formulae hidden (Format Cells, Protection, Hidden). A similar macro works fine for locked cells.
Here is the UDF I wrote, which returns True/False based on the Hidden status of a cell:
VB:
Public Function Hidden(Check_Cell As Range)
Hidden = Check_Cell.FormulaHidden = True
End Function
[Code]....
just place all three pieces of code into a module, and change the first line of the two macros to a standard "Sub Macro1()" type format.
View 9 Replies
View Related
Jul 25, 2013
Have a look at the attachment : Book1.xlsx
What I want to do is to apply vlookup in cell E2 and whenever we apply vlookup taking lookup value 'A2' the value in the adjacent cells B2, C2 and D2 should show up in the "Ans' column cell "E2".
View 1 Replies
View Related
Dec 16, 2013
I might use the filter as a selection from a combobox, or I might honestly wish to create 15 separate charts.
I have a spreadsheet. Three columns out of maybe six in immediate interest: Date, Name and score.
In "name" I have about 15 different students. I wish to create a chart that will extract the data for one of them which I would select - on the vertical ("y") axis have the scores (from "score") which range in whole numbers from 1 to 9, and on the horizontal ("x") axis show the dates (from "Date") of each score.
Complicating my thinking is the fact that a date can have more than one score, but seldom more than three.
I would then want to add a trend line through the graph, which, if I recall, is fairly easy to do.
I am wondering if I might at some point need to control the length of history, for though now I am working with about three months and over that period of time no one has more than 18 total scores.
View 3 Replies
View Related
Apr 15, 2014
I started cycling and I'm wanting some data. Referencing the below attached document, I want to take the info on Tab "Log - January 2014" and apply that information to Tab "Stats - Ride Type Data". I was able to use the countif formula for things such as temperature, wind, and feeling, but after staring at the computer for hours I cannot figure out how to apply that data to specific types of riding. I am wanting to know how many miles I travel via commute, road, or townie (shopping).
Cycling Log.xlsx
View 1 Replies
View Related
Aug 4, 2014
How I can apply an IF function for four or more columns. for example: if the value is the same in four or more cells, IF function can type OK, if not something else.
what I am doing is this : =IF(H2=I2=J2=K2=L2,"OK","Not Equal") but I always receive the value"not equal" even when the all cells have the same value. The cells values are found using formula so I did copy and paste value but I received the same result.
View 2 Replies
View Related
Nov 26, 2008
is it possible to apply a formula to a whole column just one time, so I don't have to copy/paste it every time I insert a new row?
View 14 Replies
View Related
Sep 22, 2009
Instead of looping through 700,000 lines of data and applying a formula one by one, isn't there a way to simply apply the lines below to all cells in a range at once?
View 3 Replies
View Related
Oct 12, 2009
I have a spreadsheet where users will filter it using autofilter. I then have two cells where the val/volume of each account is totalled, this works fine. However I would also like to be able to say which is the most recurring product within the account and how many times it occurs. I have attached my sheet below, I hope this helps. Eg, Account 1 worst product is..... with a volume of...
View 5 Replies
View Related
Jan 22, 2010
Is there anyway to apply the $ to cell references in formulas across a range of cells? For example, I currently have the following in Column A:
=AAA_SR_F_1!$G5
=AAA_SR_F_1!$G6
=AAA_SR_F_1!$G7
And the following for Column B:
=BBB_SR_F_1!$G5
=BBB_SR_F_1!$G6
=BBB_SR_F_1!$G7
Is there anyway to apply the $ to the entire range listed, i.e. AAA_SR_F_!!$G$5, etc.
View 2 Replies
View Related