In my database I am often inserting a new row in between two rows of data and then entering information across 8 cells. I have to manually enter all of the same information on sheet 2, again between two rows that already have data. Is there a macro or formula that would work so that I don't have to enter it twice?
View 5 RepliesI am trying to take the tables you will see in the dummy page on the second and third sheet and have them output onto the first sheet based on the parameters of the drop down menus. Is there a way to have a button on the first page that controls a macro to do this?
View 4 Replies View RelatedSome of the sheets in my spreadsheet contain some data and graphs with a few macros that are run when a button is pressed. At the bottom I have some comments with a macro that is run to timestamp when these comments are updated or edited.
What I would like to do is lock the entire sheet so that the data and graphs can't be tampered with, apart from the comments part at the bottom because I'd like to be able to have the comments editable. So far I have managed to do that, but when I edit a comment and the timestamp code is run it creates a run-time error 1004. I could just remove the timestamp code I guess but I'd like to keep it there... figuring that there must be a solution.
How can I work around this problem ? Either by being able to keep the sheet locked and still have the timestamp code run, or have it temporarily unlock the sheet - run the code - and then re-lock the sheet.
I have a work book with 4 sheets in it. Now i want to make a summery sheet for this.
View 2 Replies View RelatedI want to create a macro that will allow me to create a set number of copies of a specific sheet within the workbook. I have one sheet within my workbook entitled "Tab". I want to create a macro that will ask me how many copies I want to create and then proceed to create all the copies. i.e.: select to create 4 copies, with the result being: Tab[original], Tab(2), Tab(3), Tab(4), Tab(5)
View 2 Replies View RelatedI downloaded a Mileage Log Template. I made 1 sheet for March 2013, I want to make another sheet for April, May, June, etc. How do I make the sheets all the same, i.e., the same format with the same cells and everything else?
View 3 Replies View RelatedI have an excel sheet, where I want to insert a column or more columns between row C and D. The file is attached below. Usually, I would right click row D and choose "insert column". This does not work here. How would you do it?
View 2 Replies View RelatedI've got the calendar to work by right clicking in a cell and selecting insert date, however I'd like the calendar to pop up when a cell is clicked on.
View 12 Replies View RelatedEXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies View RelatedI have a workbook which I use for invoicing customers. Sheet one is the inovice template. Sheet two is the contact details of all my clients (i.e. column headings are Name, Address 1, Address 2, Town/City etc.).
In the invoice sheet I would like to be able to select the name of the client from a dropdown so that it automatically fills in the address into the other cells of the invoice.
I have a piece of code with a couple of errors. The code selects a certain row and then copies and inserts this row on the last line with data, the number of rows is decided by the user via a form. The line of code with the error is highlighted, think it is a syntax/grammar error.
Code:
Sub InsertRows()
'
' Inserting Rows
ActiveSheet.Unprotect
FormRowInsert.Show
Application.ScreenUpdating = False
[code]....
write a code to insert and auto adjust multiple pictures in a sheet with address of the pictures are in a column, You can download the sheet from below link for details.
Insert Pic - Download - 4shared.
When using
VB :ActiveSheet.QueryTables.Add
How would you get the code to overwrite whatever is in the worksheet as opposed to adding new columns?
I am trying to create a formula that references information on two different sheets, but i want it to show me the values that are newly created when a new row is inserted. The two current sheets are Master and Data.
I have a formula on Master that reads ='Data'!$K$3 to reference the value on Sheet data in cell K3. When a new row is inserted showing me the current readings, I want the Master sheet to show me the NEW value in cell K3 on the data sheet. The problem is, the insert of a row moves the formula on the Master sheet to now be ='Data'!$K$4. How can i STOP the insertion of a row from changing the formula so i continue to see the value i need?
I have very old file which contains lot of sheets. And I am unable to insert rows in any of the sheets in the file. It throws an error "Cannot shift object off sheet".
View 2 Replies View RelatedI'm Trying to change the value of a row when a determine condition happens, and I was thinking of doing it after the insert was made, but maybe it's easier before the insert (?) The thing is i've tried both, and i'm doing it wrong because it's not working
Code:
With ThisWorkbook.Worksheets("Site Configuration List")
.Range("A").EntireRow.Offset(1, 0).Insert
' Tried this .Range("A").EntireRow.Interior.Color = 49407
[Code].....
Obviously not at the same time, but no of those work. The call to the sub where the code above is implemented, is inside a loop, however, with each one of the things I've tried, when it gets to the line the program just stops. No error or anything.
I am running excel 2007 and I have 23 worksheets in a workbook. I have no links or hidden rows or colums. I have no merged cells or comments. My biggest sheet is A1:
AW8096. I use VB macros to add lines and move data from one set of workbooks to this one.
I have built several sheets using this proceedure.
All of a sudden I get the error "Cannot shift objects off sheet" when I run
a routine I have 50,000 times. This even happens on new blank sheet so I know its' not on the sheet.
I have checked to make sure there are no hidden comments, row or columns. I have deleted most of the worksheets and I still get the error. I have made copies of the workbook and used different file types to save it. I can create a new black sheet in the workbook that is empty and the same thing happens.
The only way I can get it to work is to creat a new blank workbook and copy and paste the sheets into the new workbook. Yes I have serched the FAQ and knowledge base and tried all of those fixes as well.
I have a sheet with a range of paragraphs of text from cell D2:R2.
How can I get it so that if I put an X in the column under the relevant paragraph, it will insert the paragraph of text into the spreadsheet.
For example. Text lies in cell D2. If I put an X in cell D7, I want the text in D2 to be inserted into C7.
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
I am currently using a macro to copy a sheet from a closed workbook in to my current workbook. However this copying is based on the sheet name. At present when I run the following code
View 11 Replies View RelatedI am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
I am running excel 2010 with windows 7. I created a macro in sheet 1 and I wish to activate the macro from sheet 2 using a form button. I have entered the code below. I know how to perform this function on a more simple macro like adding names to cells. This code is a bit more complex I just dont know where to start.
Code:
Sub LoanData()
'
' LoanData Macro
[Code].....
i need a macro that will compare the p'folios in sheet "Data to Sheet Workings, and then list the p;folios which is in sheet Data but not in workings in Sheet "New", so in the example below this would be ETSTSA ...
View 9 Replies View RelatedI am trying to add the sheet name to the center footer for each worksheet in a workbook. The workbook has about 80 sheets and it is cumbersome to do this manually. I am using Excel 2010.
I have tried to record a macro capturing what I do manually, but when I run the macro on another sheet, it does not add the sheet name to the center footer.
I have tried searching for a macro online and the ones I have found just crash excel.
I know I am probably missing something obvious in my macro code.
I have found this sales forecasting template from the Microsoft excel template section on the web, however, when i insert the new rows, it does not automatically update the "linked" sheets. It is the "detailed sales pipleline management sheet".
View 4 Replies View RelatedI need a Macro which pulls the data from different sheets of excel (which is not formatted properly) to Main Sheet. Also some of the columns will not have the same names, so macro should handle this exception as well.
View 8 Replies View RelatedI need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better.
The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
I'm trying to create a copy of the active sheet and then rename the new copied sheet to what's in cell O4, which is a formula (see below) and then paste value cell O4 in B3 of the copied sheet. However, when I run this macro it doesn't seem to like the second line where I am renaming the sheet (run time error '1004').
"O4" =DATE(YEAR($B$3),MONTH($B$3)+1,DAY($B$3))
Sub NewMonth()
ActiveSheet.Copy Before:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("O4").Value
ActiveSheet.Range("O4").Copy
ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues
End Sub
Is it possible to be able to type a name of a person and have there details appear on the page . i.e in my example in the (search worksheet) i would like to type in a name say C.G( all names will be longer ) and have all the row A7 from sheet A zone to appear in this sheet.this name could be random.Changes with people i need to assess and show individual results to.
View 4 Replies View Relatedthe sheet name is made by a inputbox in sub a()
in this way i make all my sheets with some data in it
afterwards, i run sub b() that does some calcs in each sheet and i want each time a graph in it (not in seperate graphsheet)