Macro To Perform Operations On All Worksheets In A Workbook

Mar 30, 2007

I need a macro that will perform a set of oprations on all worksheets in a workbook. The names and number of worksheets will not always be the same. I have found several macros that look like they will do this, but when I try to add my code they don't seem to work.

ADVERTISEMENT

Put Together A Set Of Macros That Perform Certain Operations Within A File

Jun 22, 2009

I'm try to put together a set of macros that perform certain operations within a file. However I've got a bit stuck on the following, and I can't see where I've gone wrong:

View 3 Replies View Related

Loop Across Worksheets: Perform The Same Process To All The Worksheets In My Workbook

Aug 10, 2009

I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:

View 2 Replies View Related

Perform Same Task To Multiple Worksheets That Contain Text Parameter

Jun 21, 2014

I am trying to do the following: I want to replace the values in A1:A3 with "0" and clear the contents of B1:B3 for all worksheets that contain "ILA" in the worksheet name. Is there a simple way to do this or another thread that already answers this question? I tried to search on the internet but found nothing useful. Here is a sample file (NOTE: there are no macros in this workbook even though it is saved as an .xlsm).

Clear and replace macro on multiple tabs.xlsm

View 5 Replies View Related

Getting A Commandbutton Macro To Do Operations On An Adjacent Worksheet

Oct 17, 2008

I am trying to make things easier for the next user by having one command button run the macros for the other worksheets (so they don't have to switch between worksheet)

However,
I get "Runtime error '1004' Application-defined or object-defined error"

View 8 Replies View Related

Faster Copy And Paste Operations In A Macro

Aug 8, 2007

To speed up copy and paste operations in a macro, I've heard that one can replace the following:

Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
with

Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
The second code should run faster as the clipboard is not used to store the copied info.

When I tried this, though, I didn't notice any improvement in macro running speed.

View 9 Replies View Related

Use VBA In Workbook A To Perform Actions In Workbook B

Apr 17, 2014

I was asked to parse and chart some data files. The data are obtained from a data acquisition system sampling a pressure transducer at about 300 samples per second. Since the start of the pressure event is created by a chemical reaction, there is no easy way to gauge when the the event will begin. As a consequence, the data acquisition system is left running continuously while waiting for the event.

Needless to say, at 300Hz, the data file grow very large, quickly. The data preceding the pressure event are close to zero and can be removed simply by using code which deletes rows with values less than a given threshold. An example of this code is:

Code:
Private Sub CommandButton1_Click()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("B20:B500000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) < 0.15 Then

[Code] ....

There are other operations that need to be performed as well. My question is whether if I have a workbook open with this and other code, will Excel allow operations on one or more open workbooks from this workbook? I would think that you can because you can move or copy a worksheet to another open workbook.

View 5 Replies View Related

Open Workbook And Perform Sumproduct

Feb 21, 2014

I have a workbook that contains two sheets that has 20k of rows of data and in a summry sheet I have a SUMPRODUCT and SUMIF formula together which performs the sum function on the large set of data based on a range of criteria on another sheet. Here's the formula:

=SUMPRODUCT(SUMIFS(BalSht!$F$2:$F$20175,BalSht!$B$2:$B$20175,CedantCode,BalSht!$K$2:$K$20175,'2. Booked Financials'!$A46))

The problem I have, is that by combining the large data set into my workbook it is becoming slow and unwieldy. I was thinking of having a macro button that when the user has input the criteria, they click the button and it then opens up another workbook and performs the sumproduct(sumif formula.

View 5 Replies View Related

Perform Same Task On All Sheets In A Workbook

Oct 31, 2008

I am trying to make certain changes to the page setup on all sheets in a workbook. Here is my code but something is not working. I think I need to somehow select the next sheet because the macro is only performing the task on the active worksheet. My Visual Basic skills are somewhat limited so I'm sure this is not the most efficient way.

View 3 Replies View Related

Macro Request - If Macro Is Unable To Perform One Of Its Jobs Then Move Onto Next Line

Oct 3, 2012

I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.

However, if the user is not online, the query is unable to refresh and the macro just hangs.

Is there a code which will enable me to say " if unable to refresh then move on to the next line"?

here's the code below.

Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub

View 2 Replies View Related

Set Macro For All Worksheets (active Workbook)

Nov 14, 2006

Sub display_all_false()
With Application
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Control Toolbox").Visible = False
.CommandBars("Drawing").Visible = False
End With
End Sub

But, when I run the code, seems there're run-time errors detected at these codes:
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False

1. Is there any way to correct these codes?
2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)?
3. Can the command menus (File Edit View Insert Format ...) be hidden also?
4. Does looping method for each worksheet

View 9 Replies View Related

Apply Macro To Specific Worksheets In Workbook

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

Macro To Freeze Panes To All Worksheets In Workbook

May 27, 2010

I am trying to include code into the end of my macro that will look in all worksheets and freeze pane row $1:$1

View 9 Replies View Related

Macro - Automatically List All Worksheets Within Workbook?

Feb 7, 2014

How do you automatically list all the worksheets within a workbook? I did some searching and decided to use the following code:

Sub ListWorksheets()

'Lists all the sheet names in the workbook onto the _
first sheet (starting at cell a4)

Dim i As Integer
For i = 1 To Worksheets.Count
Worksheets(1).Cells(i, 1) = Worksheets(i).Name
Next i

End Sub

It would be very useful if I could control the row in which the data is returned. "starting at cell [U]a4[U] seems to be that part of the command, but it still returns in the 1st row.

View 3 Replies View Related

Macro To Copy And Paste Specific Worksheets Into New Workbook

Dec 23, 2011

My problem is as such; each month I receive an application for payment. This will contain around 20-30 worksheets. However I only need to interrogate around 3-4 specific ones. The worksheets I require contain data describing the plant/equipment a company has purchased, or materials purchased over a period of time. Each month the worksheets are updated with the previous months data appended to the bottom. I am required to established if the equipments have been purchased at the correct rate.

The worksheets are all protected thus in order to interrogate them I am required to copy an paste their contents into a new work book in order to format them and insert my new "assessment" columns. I need to keep the work sheets separate in the new work book as they have different layouts however they have the same layout and work sheet names each month.

I needs a macro which would allow me to open up a new book them copy the specific worksheets from a specific file into the new work book. I then have a separate macro which re-formats them into the layout I require.

View 9 Replies View Related

Macro To Find Named Worksheets In Workbook And Delete?

Nov 27, 2012

I need macro to find worksheets (tabs) starting with word 'sample' in name and delete all instances of worksheets starting with that word.

View 1 Replies View Related

Creating Macro To Print Specific Worksheets In Workbook

May 15, 2014

I have an excel workbook with about 40 worksheets. I have a formula in A1 of each sheet that returns a 1 or a 0 depending on whether or not the name of the worksheet is in a list.

What I am trying to do is create a macro that will print all worksheets that have a 1 in A1.

View 2 Replies View Related

Macro - Opening, Copying And Saving Workbook To Worksheets

Jul 25, 2008

I have been trying to create a Macro that can do the following.

Open a Excel Workbook, Copy the Worksheets that I need (ignoring ones that are not needed.), Save them in the correct location with a new folder of date and time and saving them as csv files.

I have all the elements but can't seem to get them to gel correctly.

Just wondering if anyone has anything like this that I could adapt or edit.

View 9 Replies View Related

Macro To Perform Multiple Jobs?

Jul 30, 2013

I need a macro which will perform 5 simple tasks on my Customer sheet once a button is clicked:

1) Change Cell C15 (Drop down box) Value from "Confirmation" to "Delivery Note"
2) Put the current date in Cell H43 (I do not want this to update everytime i re-open the spreadsheet however) - is this possible?
3) Value of H23 to be the same as C16 on the Delivery Sheet ( guessing this will be =Delivery!$C$16)
4) Print Customer sheet
5) Print Delivery sheet

I'm guessing the hardest part is to stop the date changing?

View 3 Replies View Related

Macro To Perform Math Computation

Dec 16, 2006

I am looking for the easiest way for my worksheet to perform the following:

My 1st sheet (Sheet1) has columns A-C
In Column "A" there is a Cust # call it "P200"
In column "B" I have an invoice amount of $500.00
In column "C" -In this case, I need this field to calculate 15% of the amount in column "B"....and return the result of $75.00 (however, this 15% figure will vary based on data looked up in (Sheet2)

ie... in (Sheet2)

Column "A" will list all cust #'s that my company has - A1=P100, A2=P105, A3=P125, A4=P127, A5=P200 etc....

Column "B" has each respective discount amount for that customer by %... B1=10%, B2=10%, B3=12%, B4=20%, B5=15%

So the macro I need for (Sheet1- column C) will need to search "Sheet2" column "A" to find the "P200" out of the long list and then take the corresponding % amount in column "B" (in this case it's 15%) and use this figure in (Sheet 1) to perform the calc.

View 9 Replies View Related

Macro To Push Results Of All Formula On Multiple Worksheets To Another Workbook

Mar 28, 2014

I inherited a price list workbook made up of multiple worksheets (some 30 pages). Each year the new prices (including % increase) are calculated by a formula in the row below each price (100's of rows and formulae).

Thus:
C1 might say £10
D1 says =C1+(C1*2.5%) £10.25 (it actually does some rounding too)
etc

It may seem odd having both rows but MD can see the before and after. The new price 10.25 must however be manually copied to C1 to avoid circular referencing and then D1 row hidden. Very laborious to repeat 100's of times.

Is there a way of first copying the entire workbook for the new year (easy) then by means of a macro looping through each formula cell of all sheets in workbook 1 and pushing the result to the new workbook and to the correct sheet and then to the same location but to the cell above. e.g. pushing £10.25 from D1 of sheet 1 workbook 1 to C1 of sheet 1 workbook 2 to become the new price for the coming year?

View 9 Replies View Related

Macro To Create Worksheet 'xxxx' And List All Worksheets In Workbook

Feb 2, 2009

I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)

View 4 Replies View Related

Excel 2010 :: MACRO - Copy All Worksheets Into Workbook Output

Aug 26, 2013

I need to copy all sheets from a workbook to a another workbook (create a copy of the one I am using) and this is the cod I have:

Sub NewReport()
Dim Workbook1 As Workbook
Dim Workbook2 As Workbook
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

[code].....

The problem I have is that the output only have the sheet1!

I am using Excel 2010

View 4 Replies View Related

Error Handling Within Errors (macro To Create New Worksheets In The Workbook)

Aug 12, 2009

I am currently creating a database which involves using a macro to create new worksheets in the workbook. When the macro is run and a work sheet is being inserted, an input box asks the user for a worksheet name.

As you can imagine, the worksheet does not like it when the worksheet name input by the user, is the same as one already existing and so throws up a 1004 error.

In order to resolve this error I have included an error handling code to request the user to input a differnet worksheet name, as the one previously inserted exists.

My problem: It all works fine until the user types in an existing worksheet name twice, so once initially and again when the error handler has prompted a second attempt. On the second incorrect input a 1004 error warning is displayed.

I would like the error handler to keep repeating until a worksheet name that doesn't exist is inserted by the user. Is this possible?

My code so far:

Sub NewTrancheSheet()
'
Sheets("Tranche Sheet Template").Visible = True
Sheets("Tranche Sheet Template").Select

Sheets("Tranche Sheet Template").Copy Before:=Sheets(1)

Sheets("Tranche Sheet Template (2)").Select
Sheets("Tranche Sheet Template (2)").Move After:=Sheets(Sheets.Count)
Sheets("Tranche Sheet Template (2)").Select.................

View 9 Replies View Related

Reduce Macro Perform Array Of Sheet (not All Sheets)

Feb 3, 2010

How can I make this macro easier (smaller)? The same action is performed on an array of sheets (not all sheets).

View 4 Replies View Related

Reduce File Size Using Macro To Perform 500,000 Calculations

Dec 10, 2008

I have this formula that checks a line of values to see whether it is within the constraints of a separate list of values.

=if(AND(Data1!A4="count1",Data1!D4>="bin1",Data1!D4

View 9 Replies View Related

Excel 2010 :: Macro To Perform Find And Replace Function

Apr 8, 2014

I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.

So for example:

Sheet1 says the following on column N:
cat
dog
lion
bear

Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear

Column B
B1: 2
B2: 8
B3:15

I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.

View 1 Replies View Related

VBA Macro To Select Cells With Certain Value And Perform Action On Adjacent Cell

Mar 10, 2014

Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.

Attached File : LoopingVBA.xlsx‎

View 3 Replies View Related

How To Create Search Macro Button That Allows To Search In Multiple Worksheets In One Workbook

Oct 11, 2011

Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do

Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then

[Code]....

View 1 Replies View Related

Add Two If Operations To VBA Function

Nov 13, 2012

I'm trying to take a vba function and add a second if clause. Here's the function:

VB:
Function getdes(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRn Then
holder = holder & ce.Offset(0, 2).Value & vbLf & ""
End If
Next ce
getdes = Left(holder, Len(holder) - 2)
End Function

I want to do something like:

VB:
Function getdes(DRng As Range, LURng As Range)
For Each ce In LURng
If ce.Value = DRn If ce.Offset (0,-02).Value = "y" Then
holder = holder & ce.Offset(0, 2).Value & vbLf & ""
End If
Next ce
getdes = Left(holder, Len(holder) - 2)
End Function

View 3 Replies View Related

Dates Operations

Jun 17, 2006

Does excel contain a format of : "number of months"?

Example : I have 3 cells
A1 : should contain the number of months (3 months)
A2 : should contain a date with this format (dd/mm/aaaa)
A3 : should contain A1+A2

View 4 Replies View Related

Order Of Operations Within A Formula

Aug 16, 2008

I have "bashed into shape" a formula that involves 2 sets of parenthesis. After progressing beyond "#REF! or #NAME errors, Excel's automated Help suggested to add a comma, and the formula functions as wanted, I thought I was "Smart".

Later, when saving the formula to my `Formula Workbook', "Order of Operations" crossed my mind. - I think this formula is backwards, but `Excel Help' only suggested the addition of a comma? Maybe the all inclusive parenthes allowed the order to be understood?

The Formula: =AVERAGE((E56:AH56),COUNTIF(E56:AH56,">0"))

Q: Shouldn't the COUNTIF occur before the AVERAGE? Did the extra Parenthis automate the order of Operation within the Parenthis?

View 9 Replies View Related

Mathematical Range Operations In VBA

Jun 2, 2006

I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator.
In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!

'Initializing tagrow so we can test what train we are trending'
tagrow = Cells(12, 2).Value

'Only Propylene flows need adjustment in A and B trains, calculate everything'
If tagrow = 7 Or tagrow = 8 Then
Sheet2.Calculate
Range("D15:D115").Value = Range("C15:C115").Value / 1000
End If

View 3 Replies View Related

One Cell To Control 3 Operations

Mar 7, 2007

I need to have the contents of cell A1 (a 3 component list) control a formula in cell E1. If A1 is "add", then E1 should be B1 x D1. If A1 is "delete", then E1 should be B1 x D1 x .6, and if A1 is "delete ilo", then E1 should be B1 x D1 x (-1).

A1 B1 C1 D1 E1

list qty. list price of C1 item formula needed
( lookup)

View 5 Replies View Related

Defining Order Of Operations In Formula?

Mar 5, 2014

I have a formula that looks like this:

=12*NETWORKDAYS(C2,D2)-12+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)

This formula calculated the time between two dates (Start date in C2, End Date in C3) between the hours of 06:00 and 18:00 on weekdays. This is so I can work out how long an incident has spend within the group at the time when our service should be operating.

I was given this formula by someone on this forum a while back and i was new to excel and formulas so I just took it at face value since it worked. Now I want to disect it so I can use it in other situations where similar calculations are needed. I want to try to break it up into easier to understand chunks to try to understand what it is I need to change in order to do these related calculations.

NETWORKDAYS(D2,D2)

Is this trying to distinguish if D2 is a weekday?

MOD(D2,1)*24 Is this not the same as (1-D2)*24
NETWORKDAYS(C2,C2)*MOD(C2,1) Is this not the same as: IF(weekday(C2)>5,(C2-1)*7,0)
IF(NETWORKDAYS(D2,D2),....,....) What is the comparison here? if NETWORKDAYS(D2,D2) = 0 or 1?

Is this equivalent?

=12*(
NETWORKDAYS(C2,D2)-12
+IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1)*24,6,18),18)
-MEDIAN(NETWORKDAYS(C2,C2)*MOD(C2,1)*24,6,18)
)

View 1 Replies View Related

Multiple Copy & Paste Operations

Jul 28, 2007

I want to copy several separate sections on a worksheet and place these sequentially in an array, then paste these in order into another worksheet.

I have written a macro to copy and paste between the two spreadsheets, however, this is rather basic as it involves straddling between the two and performing multiple alternate copy and paste operations, as only one section can be copied at any one time.

View 9 Replies View Related

Nested IF Formula, Order Of Operations

Jan 16, 2009

I am trying to write a formula in 2003. The basic premise is below and I'm having problems getting all the senarios to work out.

Cells:
E20 = Calc'ed amount
E22 = Min amount (Sometimes equals zero - No Min)
E23 = Max amount (Sometimes equals zero - No Max)
E24 = Additional amount to add

=IF(AND(E22=0,E23=0),SUM(E20,E24),IF(AND(E220,E230),IF(AND(E20>E22,E20E23,SUM(E23,E24),IF(AND(E20E22),SUM(E20,E24),IF(AND(E20

View 9 Replies View Related

Copy Worksheets From Active Workbook To A New Workbook

Apr 22, 2009

I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.

MS VB Script error:
Runtime error '9':
Subscript out of range

Any advise would be greatly appreciated.

Thank you

Code is below..

Sub saveall()
'
'
For Each ws In ActiveWorkbook.Worksheets

ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
Sheets(I).Select
Sheets(I).Move
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Next ws

End Sub

View 9 Replies View Related

Multiple Operations And Formulas In Spread Sheet

Jul 18, 2007

I need to create a spread sheet that in Col A has 3 variables, each of which I need to triger 1)fill of that row, 2)different formula's in different columns within that row. Is this possible in excel?

View 9 Replies View Related

Copying Data From Multiple Worksheets To Multiple Worksheets In Another Workbook VBA

May 14, 2012

I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.

My current Coding Snippets that I want to use look like the following:

Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String

[code]....

Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.

View 4 Replies View Related

Match And Copy: Declare The Variables Correctly And Have The Operations Done In A Series Of Do/ Loops Or For/Nexts

Feb 2, 2007

I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.

Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.

View 2 Replies View Related

ADVERTISEMENT