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
ADVERTISEMENT
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
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
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
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
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
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
Apr 17, 2014
I have the following macro:
[Code] .....
So right now the macro is run in every single worksheet in the workbook. Unfortunately, it appears the TRIM function erases formulas in cells. I want this macro to apply only to certain worksheets. Say the worksheets i want the macro to run on are named A1-A100.
View 5 Replies
View Related
Mar 11, 2014
I have software that exports to an excel file. The files are one or two page reports. The way the software dumps the reports into excel, it comes out looking pretty rough. The rows and columns aren't spaced very well, the fonts look like garbage, and nobody likes them.
I would like to apply formatting changes to these reports automatically without the end user having to do anything. So as soon as the document is opened up it bolds the column headers, adjusts column widths etc. The reports are auto generated so no formatting can be done when the reports are made.
There may also be different formatting done to different reports. The files will come out named something like, "Report_A_DDMMYYY.xls, Report_B_DDMMYYY.xls," etc. Report A, and Report B will likely need different formatting.
I'm not opposed to using VBA or whatever might be necessary to make this happen. I can learn anything I need to pretty quickly, I just don't know where to start with this.
View 4 Replies
View Related
Feb 7, 2014
I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
View 4 Replies
View Related
Mar 31, 2009
I have a spreadsheet which has all the names of trips from a warehouse, the day that they operate (1,2,3 etc) and the job line allocated to each trip. It looks something like that:
A B C
DAYTRIPCUST
I would like to create a macro that will be creating 7 new workbooks and then in those workbooks as many sheets as the trips. In these sheets, the customers should be displayed.
How do I write it? I could not find how to have a "dynamic choice" in the macro. I.e. not to have the criteria as "1", "trips1" but to choose from the range of inputs that are available.
View 9 Replies
View Related
May 11, 2009
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
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
Mar 25, 2014
I have what is probably a simple request to all the VBA experts out there, and simply want to take a template sheet in a workbook and copy it across to 100 sheets numbered/named 101 -200, and add the name of each sheet as a text reference, e.g. sheet name 101 will have a cell within the worksheet that refers to sheet 101.
I have attached a simplified spreadsheet which indicates what i'm trying to achieve.
View 7 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
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 14, 2013
Macro command to run a macro in all sheets of a workbook
View 6 Replies
View Related
Feb 3, 2013
I have a macro (see below) that simply formats a sheet for printing. I am currently clicking individually on each sheet in the workbook, and running the macro. Is there a way to have it automatically loop through all the sheets in the workbook?
My current macro is:
Sub Set_Print()
'
' Set_Print Macro
'
Application.ScreenUpdating = False
[Code] .......
I have about 10sheets / workbook, and 80 workbooks to do, all saved in the same folder.
View 2 Replies
View Related
Jan 24, 2014
I'd like to create a macro that will take 10 workbooks and add them to a new work book, but have each workbook as a separate sheet.
So every month from work I get a report file labeled as the current month (JAN_14). Then there about 19 folders in that folders labeled with product names; then in those folders are two excel files (they're all named MSTASCH_QUICKLOOK_1 and MSTASCH_QUICKLOOK). I figure if I can see the macro to combine 10 workbooks I can change it to do less or more workbooks. I had tried a macro that takes the workbooks from one file and puts them into two seperate sheets (although I would love to have the order changed of how they're put into the workbook).
VB:
Sub MergeSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
[Code]....
View 4 Replies
View Related
Oct 18, 2013
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].....
View 4 Replies
View Related
Jun 3, 2014
I have a workbook that I edit daily then pass along to another team. All of my modules are passwork protected and there are also several sheets in the workbook that I hide before email it to the other team. I hide the sheets by changing the sheet properties in the VBA editor to "2 - xlSheetveryhidden".
This isn't a very time consuming process but I am wondering if there is a way to automate it through VBA?
I tried the the fail safe of "see what happens when I record it" but all I got was an empty Module.
View 2 Replies
View Related
Mar 2, 2009
my code to loop through sheets in another workbook does not want to work. in This Workbook (SourceWb), I copy the current workbook using SaveCopyAs and then reopen the new workbook. from ThisWorkbook (SourceWb), I am trying to delete any sheet between "First" and "Last" where a specific criteria is not met. so starting from the For Each to get my loop values, where PMs is a list of people's initials:
For Each c In Range("PMs")
If c.Value = "" Then GoTo AfterArrays
View 9 Replies
View Related
Jan 13, 2009
Could some one amend the VBA below to allow a continous loop on moving to the previous sheet within a workbook. Currently when I move to the first sheet if I use the shortcut key I get a debug error.
View 6 Replies
View Related
May 12, 2011
I have 20 excel files each having more that 1000 records, the format and header for all the files are same.
I need to consolidate all these 20 different files into one excel sheet, having only one header row at the top. Since I need to perform this step frequently, I am planning to automate it.
View 9 Replies
View Related
