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


ADVERTISEMENT

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

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 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"
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

Macro Combining Work Sheets Into One

Mar 14, 2008

I have the below macro in a workbook that I need to combine the two sheets into one, (sheet1 and sheet2). Both sheets in use column "A" for id, but for some reason it is only copying over sheet one? One sheet has 12 columns and the other has 13, not sure if that is making the difference?

View 13 Replies View Related

How Can I Make A Macro Work To Hidden Sheets?

Aug 21, 2009

OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"

Is there anyway around this?

Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?

View 13 Replies View Related

Macro To Work On 1 Sheet Not All Sheets In Workbook

May 24, 2006

Well this excel workbook has lots of sheets each for a specific region eg, Europe, austin etc and also sheets which have relevant data that gets used in the formula.

Now, say for eg lets considered the sheet called austin.

it does the calculations on the headcount needed for call center.
so the columns refer to a formula called gets() which then calls the erlanc function.

Now the problem here is this gets() function takes a value called calc

and this calc just a cell in one of the sheets called table. Now if I change the headcount value in the austin sheet nothing happens. Then I need to change the value of calc cell say make it to 5 or 6 and hit enter. It starts calulating the values to forecast thye headcount, but it does so for all the sheets . so it is taking a lot of time.

how to get make it run only for one sheet.

View 9 Replies View Related

Look Up Function Does Not Work: Look If The Record Is In Another Range And Apply Some Maths

Dec 22, 2009

i used excel as a calculator but when i needed to make some effort to reach a solution for my problem it let me down, so i will write my problem
i have a record in my data base and i want excel to look if the record is in another range and apply some maths. so i wrote this formula

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
Rows(i).Delete
Else
End If
Next i

Next mywSheet

End Sub

View 9 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

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

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
color
Formatting
Next ws

End Sub

View 3 Replies View Related

Apply Codes To All New Sheets Added

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

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.

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

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

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.

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

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

How To Get A Macro To Work With Any Work Book Name

Aug 24, 2009

i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook

the current name is

AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls

i have attached the code in notepad ...

View 8 Replies View Related

Work Book About 20 Sheets In It

Mar 19, 2005

got work book that has about 20 sheets in it.....there are links throught the workbook. ......one sheet in particular when i go to it, it freezes up...and i get the dreaded "Send Error Report to Microsoft" window"...and we all know what happens next.....excel shuts right down.

I can open the workbook and click on ANY sheet....but when i click on this one particular sheet in the book i get the error message

This work book is a template....its the estimating work book i use at work...and whenever i get a new job to estimate i open up this template.....put in my data and save the workbook as the job title.....so basically i have about 50 of these workbooks......and it doenst matter which one i open.......whenever i go to this one same sheet in any workbook i get the freeze

and the freaky thing about this is it does it randomly....I make save this template (as a new work book) when i get a new job to estimate.....and i never have any problems.......but today for some crazy reason its doing what i mentioned above......it did this about a month ago too.......same exact problem.

when it happened the first time i tried all kinds of stuff, and the only thing that worked was i had to go to windows explore...save all the files to a memory chip.......go to a different pc with my version of windows (XP)....open each file up......re-save it.......go back to my pc...reload it

View 9 Replies View Related

Code To Work On Some Sheets Only Across All The Books

Nov 14, 2006

I use a code to work on all the sheets across all the files. But now I want the code work only on sheet named Paid across all the files.

Sub PP()
Dim wb As Workbook
Dim ws As Worksheet
Dim varWBnames As Variant
Dim varItem As Variant

varWBnames = Array("Book4.xls", "Book5.xls", "Book6.xls")

For Each varItem In varWBnames
Set wb = Workbooks(varItem)

For Each ws In wb.Worksheets
ws.Activate
ws.Range("A1").Formula = "=A2+A3"
Next ws
Next varItem
End Sub

View 9 Replies View Related

Transferring Sheets To New Work Book

Feb 1, 2007

i want to do a macro, that will copy and transfer Selected worksheets to a new Workbook? so guessing, you select the sheets, then click the button and it copies then opens new blank workbook and puts them in?

View 9 Replies View Related

Change Sheets In New Workbook Does Not Work

Apr 21, 2009

I am using Excel 2003.

I tried to change the number of new sheets in a new workbook, e.g. from 3 to 1.
But the option has no effect on new workbooks. They still show 3 sheets.

View 9 Replies View Related

How To Compare Data From 2 Sheets And Put Into A New Work Sheet

Feb 28, 2009

In sheet one I have data as follows ...

View 7 Replies View Related

Editing Combobox VBA To Work With 2 Sheets Instead Of Same Sheet

Jul 16, 2014

Problem- I need a combobox on sheet1 to reference a column in sheet2 and return only a distinct list.

[Code].....

If I put my sheet2 column info on sheet1, and then reference it appropriately as built in the code above, it code works great. So I know it's close. I cannot change it to work with 2 sheets though. I've tried to "set wsSheet2 = wbBook.Worksheets("Sheet2")" and call it 'where I think it's appropriate', which is the problem. I don't know what's appropriate.

changing the steps in the code above, where in my case the combobox is on sheet1, and the range/data referenced is on sheet2?

This is what I've changed the original VBA to, and when I run the debug, it picks up the last "with...combobox1" statement as the error (error 1004, app/object defined error):

[Code] ......

View 1 Replies View Related

Sorting And Copying Results Into New Work Sheets

Jun 3, 2009

I have a work sheet with about 35,000 lines of data. Every day I have to sort the list by product category (I use auto filter to separate) and then copy the results into a new worksheet, there are about 300 product catagories. I'm new to macros but I'm wondering if there is a macro that can automate this process.

View 6 Replies View Related

VLookup To Work For Multiple Ranges On Different Sheets

Feb 23, 2012

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16

[Code] .....

As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?

Sheet10.Range("B12) = ..... ....... .....

This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?

View 6 Replies View Related

How Do I Apply A Macro To All Open Spreadsheets Except The One The Macro Is In

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

Macro Apply To Next Row

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







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