I'm wanting to add a button on the last row of a worksheet to "add rows" above the buttion (entire row). I have some cells (6 total) that have a formula that needs to carry over to the newly created rows. Some cells remain blank but some cells need to have the formulas fill down to the new rows (i.e.: =sum(a2+1), =sum(a3+1)...
Is it possible to give option of choosing how many rows someone wants to insert?
Help in creating the button, option to choose # of rows and the macro to insert those rows.
I have recorded a basic macro that allows me to copy a formulas in cells CF11 to CH11, and it pastes it into cells in columns CJ to CL. Copy of code is below if you need it.
The issue I have is that I need to insert new coumns into the worksheet, and I need to copy the formulas from columns CF to CH, and they will now need to pasted to columns CN to CP. Note that this is a monthly report where we keep the prior months, so each month will need to add new columns. There are 8 tabs in the workbook, and they all use the same macro, just over different rows (columns all line up).
Is there a way that I can get the macro to paste into the correct column without me having to adjust the macro each time?
simple little procedure to insert columns on two sheets and call each by the same name. Macro runs off a command button and inserts a column at E:E and gives the user an input box with which to name the inserted columns.
see the attachment. You will see that I have recorded two macros:
1) To insert a new row for new people. 2) To insert a row for new animals.
With regards to 1), I would like to create a macro that copies the contents of the row with the penultimate name in the people section, and for a new row to be inserted beneath the copied cell. Similarly, with regards to 2) I would like to create a macro that copies the contents of the row with the penultimate name in the animal section, and for a new row to be inserted beneath the copied cell.
The issue that I have is that the macros do not copy the penultimate cell in each section, but copy a particular row, say row 11 each time I want to insert a new animal. So if I had inserted numerous new rows for the people section, and subsequently wanted to insert a new row for the animal section, I press “crtl+sht+a” which copies the contents of row 11 and which is not the contents of the penultimate row for the animal section.
I have two sections to this spreadsheet: 1) the entity section; 2) the order section.
I have created a macro to insert rows in both these sections. The macro appears to work fine for the first section but not for the second section. The problem I cannot solve is in respect of cells D9:D10. The macro is as follows:
I'm trying to write a macro that inserts multiple rows by giving user a MsgBox to enter the # of desired rows. Meanwhile, I need to make sure that the formulas in certain cells are filled down (not just copied from cell directly above). I put something together but it's only adding a single row. Attached is my sample workbook.
I am trying to create a macro that will create a comma and space between every 5th character within a cell. that would be preference #1. What I decided to go with for now, instead, is to try (using the macro recorder) text to columns, fixed width, and do this after every 5th character, and selecting each cell to be formatted as text.
What I am having difficulties with is trying to tell the macro which cells to touch and which ones not to. I do not want the macro to do text to columns and fixed width on simple text. I only want it to do the macro when it is a string of numbers that, during transfer from PDF to excel, have been concatenated into one cell. I also want the macro to do this for infinite number of rows. I have excel 2007.
I am attaching a small excel file with examples of data I am working with. I have saved this excel file as a 97-2003 version for those who do not have 2007. As I stated, if anybody can help me figure out how to do a simple insert comma space every 5th character where information has been concatenated, that would be preference. Also, I am not trying to change anything within column A. Only column B. On the sample file please note B3 - B6. Rows 5 & 6 are fine as they are. Other problem rows include B11 - B13
Just a quick on i hope. Im trying to create a macro that adds a worksheet/tab and Rename it to the month after the tab already showing.
e.g
if i already have two tabs one dated january and the other named february i require the macro to say march then the next run would be april if you get my drift.
I have a bit of code which performs correctly but it ONLY works if it is pasted into the worksheet using VBA. I currently have to manually copy/paste it before it will work and I am needing a macro to handle inserting this for me.
how to get a macro to copy/paste into Sheet1 under the VBAProjects heading?
I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.
For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.
Here is the Sub Macro1() ' ' Macro1 Macro ' Macro recorded 6/30/2008 by kmayfield ' ' Range("C3:F3").Select ActiveCell.FormulaR1C1 = "Jan-2008" Range("G3:J3").Select ActiveCell.FormulaR1C1 = "Feb-2008" Range("K3:N3").Select ActiveCell.FormulaR1C1 = "Mar-2008" Range("O3:R3").Select ActiveCell.FormulaR1C1 = "Apr-2008" Range("S3:V3").Select ActiveCell.FormulaR1C1 = "May-2008" Range("W3:Z3").Select
I had a macro that worked perfectly by inserting pictures into named ranges throughout the sheet depending on user input. However when I switched to Microsoft Excel 2007, the pictures all go into one cell.
My code looks like this:
Application.ScreenUpdating = False
Dim picnme As String Dim rng As Range Dim ArrImg Set rng = Range("B5") picnme = Range("B5")
I only copied in a section of it bit it gets rather repetitive. There are over 50 pictures that get inserted into the spreadsheet and it worked fine in the prior version of excel.
in a certain macro, I generate graphs in different windows. At the end of the macro, I want to activate these windows one after the other putting a time delay in between. How can I do this? (I have found a "delay property" in the VBA help, but it only applies to forms...).
I was tired of making my list smaller to upload it here, so I uploaded the complete thing on megaupload :P Here is the link:
http://www.megaupload.com/?d=1W6PBADE
On the bottom of the list is a button; "Add New Anime". When you click on that you get a UserForm where you can enter; Title, Type, Total Episodes, D/L Eps and Watched Episodes. When you press ''Add Anime" the entered values are entered at the bottom of the list, underneath the corresponding list names on the top.
Now there are 4 list names left (Left, Status, Status 2, and Progress (%)) I still have to make a option to enter Status 2 as well in the UserForm.
Now the question:
In columns F (Left), H (Status) and I (Progress (%)) are formulas. How can I copy those formulas automatically when I add something new with the UserForm? And can it automatically insert a new row, since I have to move the buttons down with every new title.
I recorded a macro (which worked), then tried to get more out of it by adding code to add a further function (which didn't work). The code is as follows:-
Is there a way for a macro to be not active when trying to insert a row or a way to have the macro understand that it's just a row shift? I'm trying to have a time stamp that anyone changes the value in a column. The following code generates an error 1004: application or object defined error when I insert or delete a row.
I have an Excel file that contains several worksheets. I would like to add a forumla to the next available cell. For example, if coulmn F contains the set of numbers to be sum the formula should be placed beneath the last number in column F. Each worksheet has a different length, the worksheet name will vary, and the number of worksheets will vary.
I know how to accomplish this task using the code below when the sheet name is known. Since it is not known I am unsure of the code.
I am trying to create a macro that inserts a formula based on data in column A (looking up in same row) and the header (Stored in Row 2 of the column - This is a fixed cell)
Formula I can currently copy and paste in there is
(I know it's bad form to match in the vlooukup, however it works in this form...)
So far I have
Private Sub CommandButton1_Click() ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC1,'Current IW15'!$A$1:$L$1962,MATCH(Workpack!R2C,'Current IW15'!A$1:$L$1,0),FALSE)" ActiveCell.Offset(1, 0).Range("A1").Select End Sub
I am trying to make a macro that will embed images into a worksheet for my company. I researched online and put one together from stuff I've seen posted but when I email the file, the receiver can not see the image. I believe it is because "inserting" a picture only inserts a link to the picture but not the actual picture. Is there a way to embed the picture using the same macro without the sender having to send the picture and the worksheet?
Sub photo1() ' ' photo1 Macro ' ' Application.ScreenUpdating = False 'varible Picture1 is inserted down below - ***change both*** Picture1 = Application.GetOpenFilename("Picture,*.JPG,Picture,*.JPEG,Picture,*.GIF,Picture,*.BMP") 'edit "("Picture,*.*")" section to add or chanve visible file types
I have a problem with a certain tool that i've been trying to develop.
I have two sheets in my workbook. In the first one i choose a value from a list (in cell A2). After the value in the list is chosen, certain integers for that value appear in the right columns. (with blanks between them)
Next, I will press a button called "calculate" which works like this: If A2 can be found in Sheet 2 Column "Value" then the macro overwrites the integers found at the right of the A2 from those found in Sheet1.
If Sheet1(A2) is not found in the Column "value" then a new line is inserted at the end of the table like this.
A 1 3 6
Pls help me with this, I have a very close deadline and I don't think I can pull this off by myself in such little time.
I have the macro to insert rows every nth row, but need to be able to insert text into that nth row.
I have a list of addresses and every 10 addresses i need to insert a 'seed' which will be have the same details everytime. This is what i have so far...
Sub InsertRowsMod10() Dim r As Long r = 10 Do Until Len(Cells(r, 1)) = 0 Rows(r).Insert Shift:=xlDown r = r + 10 Loop End Sub
I have a the following macro that looks through a range of cells and generates emails based on the date. The macro generates the email but does not insert my signature line.
I manage to find a macro online, which aims to insert a desired formula into desired cell range. However, when I execute the macro, i get a "run-time error, 1004".
I want to insert a formula that is tied to a specific column. I know how to do formulas and have a slight understanding of macros. Can I insert the formula as part of a larger macro?
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB: Sub SplitAndTranspose() Dim N() As String N = Split(ActiveCell, ", ") ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N) End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
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?
