i have a macro code but i don't know how to apply it to all sheets in the same workbook
my code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D5:D100"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now() [code]....
There is currently data on each sheet in my workbook. I am trying to get a macro to Copy the formatting of sheet "Statement" and paste the formatting on all other sheets in the workbook, except Sheet.actual
There is also a picture @ the top of sheet statement, that i would like copied and pasted to each sheet except sheet.actual.
Also, on all sheets except for STATEMENT & ACUTAL, i need the formula in cell C11 =vlookup(F14,cardnum,2,false) and in cell A11 = Name :
how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data ...
View 9 Replies View RelatedExcel 2010, Windows 7
I have a whole ton of *.dbf files (98 to be exact), that are from ArcGIS shapefiles. These date1_date2.dbf files have point names, and values, as shown below.
NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
I'm trying to figure out how to transpose each of the dbf files and save them as a text file (tab deliminated). My original plan involved saving them all as text files, then using a convoluted Unix script (I'm what you call a "dirty programmer") to transpose them and then combine them all into a single file. Since there are so many files (and I plan to do this in the future as well), I thought if I could at least automate the text file creation, I'd be ahead of the game. Even better would be a macro that will transpose the data for EACH file, then create a 99 (98 files + header row) row, 5 column text file. Creating the text files (1 text file for each dbf file) automatically .
All the *.dbf files will have exactly the same # rows/columns (2 columns, 6 rows)
This is what I've tried so far.
1)open one of the dbf files in Excel
2)record my marco (using the Personal Macro Workbook option - so it will be available in any workbook)
2a) transpose data (and paste it right below the original data so that the new data is in rows 7 and 8 of the same file)
2b) save as text file (tab deliminated)
2c) stop recording, and end up with this:
NAMERASTERVALU
Point 1-9999.00000000
Point 2-9999.00000000
Point 3-9999.00000000
Point 4-4.93072701
Point 5-8.90071201
NAMEPoint 1Point 2Point 3Point 4Point 5
RASTERVALU-9999.00000000-9999.00000000-9999.00000000-4.93072701-8.90071201
So now I want to apply the macro I just created to all open workbooks (after I bring in all the bdf files), but the macro isn't listed when I click the "view macros" button. All I want to do is apply the steps I recorded in the macro to all open workbooks.
have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
Else
'Allow printing
End If
End With
End Sub
I need to apply borders to a certain range. Is there anything I can do to shed some fat (code) off the macro below?
Range("A8:AD100").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
I have a code that opens a workbook and saves it under one file name and then saves it under 2 different file names, but it keeps on saving them as password protected and I can't work out why. Would it be something to do with the code or something else? The following is the code I am using:
Workbooks.Open "C:BatteriesStore InformationStores" & StoreFile
ActiveWorkbook.SaveAs "C:BatteriesStore InformationOld Stores" _
& StoreFile & " " & Format(Now, "d-mmm-yy"), xlNormal, Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:BatteriesStore InformationStores" _
& StoreFile, xlNormal, Application.DisplayAlerts = False
I have a number of different modules. Each module does something similar, but different to a specific workbook. Instead of running the macro(s) individually is it possible to have a parent-class that calls on each module, and further to have each module *know* which worksheet it should apply to?
Example below:
Code:
Sub Cost_Center_Information_File()
'What it does: applied to a worksheet (there are about 15 worksheets in the workbook) it
'will do some formatting manipulations.
[Code].....
How I can unlock a range of sheets that are password protected in my workbook.
I have 20 identical sheets one for each department which are protected by the same password.
It would be nice if I could rapidly lock and unlock these sheets.
What I need a set rng command in conjunction with a For Next loop?
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED
[code]....
Am trying to find the VBA code to "ask a user for the password to unhide multiple sheets/tabs"
I have got the script for unhiding all sheets which is:
Sub Sheets_Unhide()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = True
Next
End Sub
What i would like though is for it to prompt with a password first before the code is executed for unlocking 50 odd sheets.. I know its not 100% fool proof, but would do for what i need!
In Excel we can apply different formatting styles to the "TEXT" contents of a cell while we are in edit mode. For example we can apply "BOLD" to Brown Fox and "UNDERLINE" to lazy in a string "A quick brown fox jump over a lazy dog.
But when the above string is concatenated from the contents of various cells, excel is unable to apply different formatting (as exampled above) in the concatenated cell. Although we can apply bold, underline etc by selecting the concatenated cell but it is applied to whole string and not to the selected parts of string.
Sheet1 *A1Brown2Fox3Lazy4*5A quick Brown Fox jump over a Lazy DogSpreadsheet FormulasCellFormulaA5="A quick "&A1&" "&A2&" jump over a "&A3&" Dog"
Moreover, MS Word's "mail merge" is capable to apply different formatting to different "Fields" in a single paragraph.
Is there any way so we can apply multiple formatting to the contents of a single cell which is not "Text" but result of "concatenate" in Excel, like MS Word's mail merge or as it is applied in custom format within straight brackets like [Red] etc.
is it possible to have more than one IF function applying to a cell eg. if value in cell A1 is greater than 10 then Positive is written but if value in cell A1 is between 8 and 10 then Suspect is written.
View 2 Replies View RelatedI have a workbook with 10 sheets.
Each sheets has data starting row 14 and column B with row 13 being column Headers.
I want to copy data from each sheet, having column headers suppose A, F & G in to a single sheet. The position of column headers I want to copy are different in each sheets
Means macro will go to sheet1 first, he will copy data from column headers A, F & G and paste in new sheet suppose main.
then macro will go to sheet2, he will copy all the data from column headerA, F & G and paste it in main sheet after the last used row and so on.
I have a folder on a public drive. (\CFDIVIX1PublicPurchasing2009)
This folder contains multiple sub-folders (the sub folders are vendors which I purchase merchandise from) each sub-folder has 1 .xls file in it. Each file has 1 sheet containing all the information I need to retrieve.
Each one of these files has the same header information in cells A1 through I2 so I manually place that when I create this new workbook prior to inserting the macro. Each work sheet has a different # of rows depending on the number of items I buy from that particular vendor but I feel safe saying it never exceeds 250 rows per worksheet.
What I need
For the macro to pull up the first work sheet and pull out the data from cells A3 through I250 and place that data into my new work sheet in the same format. Then I want the code to move on to the next worksheet and pull out the A3 through I250 and place that data in the new worksheet directly below the last row of data that was pulled in. (Also it would be great if the code ignored blank values. Like if the first work sheet it pulled data from only had data in the first 10 rows I don’t want 240 rows of blank rows before the next set of data)
I hope that running this macro will accomplish transferring all of my data from these multiple sheets into a single work sheet containing all of the pricing for all of my vendors. Once I have this I can place a second sheet on the workbook and use a formula to pull out pricing information that I need on a per basis without having to search through 50 vendors to find who it comes from. I don’t control the vendor work sheets that I want to extract data from so I need to use the macro to run it weekly incase my purchasing dept makes changes.
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 formula that I wrote in B2 and then applied vertically to a large list of rows (100+). I applied it vertically by simply double-clicking the cell with the formula, and it cleanly applied the formula to every row in Column B.
This formula now needs to be applied horizonally across columns, for each respective row. I need a way to do this in one swoop, as it would be very time consuming to manually drag each cell in column B.
I've attached a sample spreadsheet with a simple formula just for the sake of an example.
If you have a range of cells with values in them (Q2:AZ2), you can use conditional formatting to identify the top x% of the group and it would essentially treat each cell in comparison to the group. I need to identify if a cell in the group is greater than 3*Stdev(Q2:AZ2). Do I have to create conditional formatting for each individual cell or is there a way to apply a function to the group that would recognize each cell the same way that the canned conditional formatting rules work?
View 1 Replies View RelatedI found the code below and it works perfectly if I want to copy all the other sheets to a master sheet. But, I need to specify specific sheets. Basically I have a workbook consisting of multiple sheets and multiple "master" sheets so I need to specify in the code which sheets it should be copying.
[Code] ........
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...................
How do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.
View 6 Replies View RelatedI have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.
View 3 Replies View RelatedFor simplicity, let's assume I have three columns, "Company," "Policy," and "Balance."
Example:
Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
According to my post preview, the columns are running together, but I do have it arranged in three distinct columns. What I want to do is program a macro to do the following:
Every time the name of the company changes, I want Excel to insert two rows after the last of the group, and have a summation of the "Balance" column in the first inserted row.
This is what it should look like:
Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
$10 <- (this should be directly under the $4, but I'm not good with forum languages)
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
$23 <- (same with this - should be directly under the $4)
I have got the following code to fill series and someone please help me that how to use this macro I can enter thses code in sheet1 on enter event and the result iwill be displayed in sheet2 and if any duplication in series then it is copied in sheet3....
View 9 Replies View RelatedI was messing around with some Macros, as an issue came up where clients were pasting over Data Validated cells (thus removing the validation). Somehow though, the feature where you can select a cell, then pull the active cell down through the column (and then can copy, fill series, etc)has become disabled. I deleted all the macros, and turned them off in the Trust Center, but when I click on a cell the box and plus sign still won't display for me to pull the cell down the column.
View 2 Replies View RelatedHi 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 Related