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
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...
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 RelatedI 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.
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...................
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 RelatedI want to create a macro to take multiple inputs from sheet 2, apply them simultaneously to the inputs on sheet 1, take sheet 1's output and list next to the inputs on sheet 2. I want to apply this to a spreadsheet with complicated calculations. Example spreadsheet attached. Related Macros I have will do a similar thing but only take one input at a time. Code as follows.
VB:
Sub x()
Dim r As Range
With Sheet2
For Each r In .Range("A2", .Range("A2").End(xlDown))
Sheet1.Range("A2") = r
r.Offset(, 2).Resize(, 3).Value = Sheet1.Range("C2:E2").Value
Next r
End With
End Sub
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
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
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] .....
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
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet1").Range("B3").Value >= -Date And Sheets("Sheet1").Range(" B3").Value
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
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 RelatedI 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]....
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 RelatedI 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
Is there a macro to subtotal by column name then only total certain columns? Like the data below, can I subtotal by Key then I only want the subtotals to show Price, Sale Price, QTY,, Warranty, Credit, and Cash. So I dont want to subtotal % cash?
key Price Sale Price QTY Warranty Credit % Cash Cash Camera 502 61 10 5 3 15% 15 Phone 1346 234 10 5 3 15% 15 TV 13216 479 10 5 3 15% 15 Camera 484 131 10 5 3 15% 15 Phone 3 231 10 5 3 15% 15 TV 24 12 10 5 3 15% 15 Camera 138 21356 10 5 3 15% 15 Phone 3168 12 10 5 3 15% 15 TV 1348 12 10 5 3 15% 15 Camera 502 12346 10 5 3 15% 15 Phone 1346 132456 10 5 3 15% 15 TV 13216 61 10 5 3 15% 15 Camera 484 234 10 5 3 15% 15 Phone 3 479 10 5 3 15% 15 TV 24 131 10 5 3 15% 15 Camera 138 231 10 5 3 15% 15 Phone 3168 12 10 5 3 15% 15 TV 1348 21356 10 5 3 15% 15 Camera 138 12 10 5 3 15% 15 Phone 3168 12 10 5 3 15% 15 TV 1348 12346 10 5 3 15% 15 DVD 1348 132456 10 5 3 15% 15
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
I'm having trouble with this macro I'm trying to write. I have a worksheet that's subtotaled like this:
Cash 100,000
Land 65,000
Equipment 104,000
Kroger Foods Total 269,000
Cash 200,000
Buildings 25,000
Equipment 25,000
Prepaid Rent 50,000
Whole Foods Total 300,000
The data is obviously just an example, but that's how it's set up (a subtotaled worksheet with three columns). My issue is that I need to write a macro to copy these numbers in column 2 (ex. land - $65,000) and paste them in a cell in another worksheet that finds the particular cell to paste it in based off column 1 and column 2.
For instance:
Cash Equipment Land Prepaid Rent
Whole Foods
Kroger
Essentially, I'm trying to find a way to copy the totals in column three and paste them into a cell in a certain row (based on column 1), and column (based on column 2). I'm pretty lost, and I just need some guidance as to how to tackle this. So far my code sets column 1 and 2 as strings and sets the columns in the second worksheet to these strings, and from there I'm lost
I'm getting a runtime 1004 error "Cannot Shift Objects Off Sheet" right at the line when i am trying to collapse a subtotal (showlevels, rowlevel 2). I searched about this and i tested. I cannot find any comments, hidden comments, rows or columns. I cannot find any shapes (ran "Kill_Shapes" posted by Aaron Blood).
Sheets("Oxnard Planning 10 (all)").Activate
'SORT: Del Code (D), then Style (A)
Range("A1").Sort Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("A1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'Subtotal by STYLE
Range("A1").subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Subtotal lines = Bold & Pink
ActiveSheet.Outline.ShowLevels RowLevels:=2 '<< ERROR:Cannot shift Objects off Sheet
With Range(Range("K65536").End(xlUp), "A1").SpecialCells(xlCellTypeVisible)
.Interior.ColorIndex = 38
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=3
Hi i have this macro below which works perfectly to clear the contents of column A. I will have multiple worksheets all named from 1,2,3,4,5 upto 50.
What i would like to do is modify this script below so it will run on the worksheets labelled above.
I have a macro and I want to run that macro on multiple sheets. I don't know how to select multiple sheets. I have selected for example three worksheets.
I have used the following code but the code runs only on sheet 10. what do I need to do to make it work on all the sheets ?
Code:
Sub multiplemacro()
Dim wsh As Worksheet
Sheets(Array("Sheet10", "Sheet11", "Sheet12")).Select
[Code].....
I have multiple sheets and a summary sheet in the beginning. and i need to populate the min value / max value and the avg value for every sheet into the summary sheet.
Example i have a column of numbers in column G and i need the min , max and round(avg) for all sheets in the first summary sheet.
I want to consolidate the various employees' salaries of all months in a sheet. I enter salaries in different sheets month-wise and in each sheet, department-wise. Some employees get commission in various departments. Now, I need to see the details of an employee by giving his name. I should get month-wise his salary, commission and department in which he get commission, across all the sheets.
View 1 Replies View RelatedPrint sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.
(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)
I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:
I have an excell spreadhseet that has more than 100 sheets and I would like to combine all these sheets into one master sheet (Sheet1 = MasterSheet) within this workbook. Each sheet has different number of rows used. I just want used ranges to be copied over to a master file appending the previous copied range.
Sub MergeSheets()
Dim strSheet As Object
Dim LR As Long, LC As Long
Sheets("Sheet1").Name = "MasterSheet"
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For Each strSheet In Sheets
If strSheet.Index 1 Then
[code]....
I am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.
I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."
Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?
I have a macro listed below that I would like to use on multiple sheets within the same workbook.
Sub PrintMacro1()
'
' PrintMacro1 Macro
' To change print format from landscape to protrait
[Code].....
What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:
Sheet 2 - The test
Sheet 3 - Must be hidden
Sheet 4 - The test
Sheet 5- Must be hidden
Sheet 6- The test
Sheet 7 - Must be hidden
I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.