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.
View 9 Replies
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 RelatedI'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 RelatedI 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
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"
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.
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.
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.
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 RelatedI 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
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
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.
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 RelatedHow 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.
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.
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 RelatedI 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.
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.
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?
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.
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?
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 RelatedI 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
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.................
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 RelatedI 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
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.
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
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]....
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
