Still struggling with the calculator. Is there anyway to get a macro to insert a set number of worksheets. The set number would be the sum of two fields on Sheet 1.
Example attached. I have searched the forum and can now use a macro to insert a worksheet but have no idea how to tailor it.
I would like to run a macro against a worksheet that will have variations in the amount of data (rows not columns). The column headings will always stay the same.
Once the basics in the macro run (formatting) I will need a certain statement to "lookup" values in a column (see values below) "count" them and give me a total.
I have attached a before and after file showing what I would like the macro to ultimateley do because it is so time consuming. Any assistance would be most appreciated.
.083=0-2hrs
.125-.50=3hrs-12hrs
.541-1.0=13hrs-1day
1.04-5=25hrs-5days
See SLA Response Days (column)
I have a spreadsheet whereby in each cell of a row there may be a formula or/& conditional statement applicable. I would like to create a macro that can perform the insert of the new row as well copy down from cell above applicable functions as well as conditional formats that maybe resident. Can this be done?
View 8 Replies View RelatedI am new to VBA but I am trying to insert text and lock all duplicate cells except for the first one. Below is the macro I am trying to make. I keep getting an error on the line:
If(COUNTIF($A$9:$A$76,A9)=1,FALSE,NOT(COUNTIF($A$9:A9,A9)=1))
This is my macro:
Sub SetVoters()
'
' SetVoters Macro
'
'
Sub SetVoters()
'
' SetVoters Macro
'
'
ActiveSheet.Unprotect
[Code] ......
Need to look at a number in a cell, inserts that many rows below that row, then repeats this for following rows that also have cells that indicate how many rows to insert.
Attached is a simple sample data sheet with how data looks before and how it should look after.
I have a macro that runs in Sheet 1 and enters the word "Complete" in cell P of the active row. The same cell that requires the word "Complete" in Sheet 2 is in cell T of the active row. Is there a way to have the same shortcut that will identify which worksheet it is in and go to the appropriate cell?
Code:
Range("P" & ActiveCell.Row).Select
ActiveCell.FormulaR1C1 = "Completed"
ActiveCell.Offset(0, 1).Range("A1").Select
Pls help me do a macro that upon clicking a button it will copy and paste a column heading (located in Row1 of Sheet1) to all non-empty worksheets (Sheet 2 to Sheet N) in the workbook simultaneously. Note that each worksheet has no column heading and has the same number of columns as that of the column header in Sheet1. Worksheet 2 to Worksheet N contains data extracted daily.
View 7 Replies View RelatedWriting a macro to insert two columns with IF formulas into a Worksheet. The 2 columns to be added would be after Column G on Sheet 1.
The new Column H would be
"=IF(G1>=Q#,G#/Q#,0)"
And the new Column I would be
"=IF(G#>=Q#,MOD(G#,Q#),G#)",
Where # represents the row number.
I have a folder for each month, and in the folder there are worksheets for each day of the month. eg for January folder, it will contain 31 worksheets. They all have the same column headings. I want to know if there is any way i can use a macro to automatically insert a formula in each worksheet, using loop?
View 2 Replies View RelatedI have a protected worksheet, which I have 2 macros, 1 to spell check and 1 to insert rows, they both unprotect the sheet and re protect it again once they have completed. The problem I am having is that when I protect the work sheet first time round I tick the box to allow users to insert rows, once the Macros run they disable this functionality. Is there anyway I can include this in my Macros or do I need to add a new button!
View 9 Replies View RelatedIs that possibe to use the "templeate" sheet and lookup the "Master" sheet to create a new worksheet called "result"
View 2 Replies View RelatedI am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.
The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.
I am using the following ....
when I insert a row into the sheet called WBS - Explanation, it only copies over into columns A:D in my worksheet called Timeline & Key Tasks. Unfortunately, I need the whole row to be inserted in the Timline & Key Tasks worksheet.
I can see how the coding has been set up and why this is happening, unfortunately I don't have the knowledge to workout how to change it to the code to include the whole row without deleting information, which I don't want to do.
In the attached file there is a monthly Worksheet with 10 daily worksheets. The existing macro consolidates the daily worksheet text into the monthly but what I would like to do is have it insert a Row IF it copies valid data into the monthly Worksheet (thus I could not run out of rows but not need a blank worksheet with say 30 empty rows.)
View 1 Replies View RelatedDoc1.docx
I need the Left picture to look like the Right picture... There's over 30,000 lines of data.
I have a list of numbers say in column A, but when the number "41", "25", "90", or "92" is in that column, I want a text message to print 6 columns to the right of it (same row) that says "service".
I have a spreadsheet that is 7 columns wide and contains blocks of data. The data blocks vary in size typically between 3 and 12 rows. Between each block of data is a blank row (there are no blank rows within the data blocks). There may be up to 500 rows in the spreadsheet consisting of maybe 30 varying size blocks of data.
I am attempting to produce a Macro that will insert page breaks into the spreadsheet, the requirement being that non of the blocks of data are split across two pages. I would need to be able to input the available number of rows per page (to allow for headers and footers), the Macro would then need to count down that many rows, find the last blank line before that point and insert a page break. It would then need to count from the position of the inserted page break to determine where to place the next page break etc.
I have written a vba program that creates a report in excel. Most of the pages of the report are created at runtime. I have an image on a hidden worksheet that I need to place into one of the worksheets that is created at runtime. The image is an excel chart that I copied and pasted as a picture. I did this so that I could resize it easily and all of the elements of the chart would maintain their relative sizes. I have tried:
View 2 Replies View RelatedIs it possible to insert text an a cell value in conditional formating, i,e Ive got the conditional formatting:
=AND(H$4>=$B5;H$4<=$C5)
I want to insert this text whenever this condition is true once and not to repeay it:
="Load " &TEXT(G$5;"dd-mmmm") -- where G$5 is a vallue cell_
I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.
View 9 Replies View RelatedI run a report that has several blocks of information with blank rows separating them. The blocks change from time to time and across pages so they are not always in the exact same rows.
I'd like a macro that can insert page breaks around a certain number of rows (say 60), but not insert one between two rows that have any content. If the page break would fall between two filled rows I'd want it to go to the nearest blank row instead.
The pages would be about the same size but the blocks of information would not be broken up. That's my idea, anayway. I don't know VB so I can't be sure it's even possible.
Provided this is possible, I'd greatly appreciate the help, as would all the other people in my organization who have to do this on a daily basis.
I use the following code to insert a row of set parameters in one of my worksheets.
View 10 Replies View RelatedI was wondering if it is at all possible to insert one worksheet into another one? Specifically, I have a report that I have created, but someone requested a section at the top that would require completely different sized columns than the rest of the report uses. Is it possible to embed another sheet into this report so I can resize the columns without it affecting the rest of my report?
View 6 Replies View RelatedWorksheet full at Column IV how can I insert more
View 1 Replies View RelatedTrying to find a code which will insert all images into specified cells.
[url]
All of the images are in a single folder C:/My Documents/Pictures
All are .jpg images which contain the prefix EV_
ie: image1 is called EV_10020003.jpg
image2 is called EV_40B00001.jpg
image3 is called EV_C003001.jpg
etc.
Throughout my worksheet I have cells containing text of an image
ie: in F23 contains the text EV_40B00001
in G50 the text EV_C003001
etc...
My aim would be to use a command button to search through my worksheet (Sheet1) and load up each image to their corresponding cell.
I have a approximately 17 workbooks, all with multiple worksheets within. I have a logo that I now want to add to each worksheet in every book.
What I would like to do is the following, on each worksheet.
Add 13 rows starting from the top A1 (this is to make room for a logo I want to add)Add the logo JPG file (which covers the range A1:F13) . This will be the same picture for all sheets. The picture properties should be "Locked" and "Don't move or size with cells". The path of where my logo is stored is C:UsersGrantDropboxEffExAdminLogosEffEx_logo-05Then select the entire contents of the workbook and uncheck "Locked" from the format control.Then apply protection (which leaves the picture locked) but allowing selection, inserting and formatting of rows/columns.
This will allow the users to still format columns and rows eg. autofit columns, but they cannot select and delete the logo.
I have tried using the Macro Recorder but when I select all sheets, I see that it records the actual sheet names and so this will obviously not work for when applying to other workbooks. Also, it did not record any of the Protection commands which I did.
how I can do this?
Can I get a macro to insert a new worksheet each time it runs and rename it to a value held in a cell on another tab eg Sheet1, cell A1 - this value will change each time the macro runs so there wont be any duplicated tab names ?
I have got a worksheet called data with values in column M7 to M31 which are only there if they meet a criteria >0
if there is a value in cell M9 only, i would like to select the value of M9 and also the description in B9 and insert this into another worksheet called W1 on row C43 and i43 and repeat on C44 and i44 if values are >0 in M11, this would create 2 rows and would expand if there was more matches >0
I have a workbook with a worksheet called Summary that has formulas that refer to other worksheets (say A, B, and C) in the same workbook.
What I want to do (with VBA because I have to do this many times) is copy this Summary worksheet into several other workbooks, all of which also have their corresponding worksheets A, B, and C.
I do *not* want the copied Summary worksheet's formulas to refer to the old workbook. If I copy the worksheet by hand, this is what happens.
Instead I want the copied worksheet's formulas to reference the worksheets of the workbook they are now in.
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub