Insert / Delete A Column - Copy Formula And Apply To Other Worksheet
Jul 19, 2013
I need to do something to my workbook, and I need to do this task:
When I insert/delete a column between E & F in sheet 1, the formula (not the value) in the column E was applied too to the new column I've inserted/deleted..
Then, when that happened to the sheet 1, It would happen too to the other sheet automatically..
So I don't need to insert/delete the row and copy the formula manually for each worksheet..
I know that I could simply solve it with grouping the sheet tab..
But I have plenty of data that needed to be inserted and applied with the formula..
I will attach the little example : insert.xlsx
And one more thing, I received this VB code from [URL] ..... for inserting the column:
VB:
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
[Code] .....
And this code for deleting the column:
VB:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim lngRow As Long
Dim ws As Worksheet
If Target.Row = 1 Then
Cancel = True
I have a spreadsheet where I need to insert a column then a formula that performs a calculation on the previous column, all the ranges change; so far I ahve managed to insert the column to the right of the one I want to perform the calculation on and insert the formula in the first line, but i'm having a problem copying this dowm to the end of the data range.
I 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.
The data to be filtered is in several sheets, and once filtered is to be copied to a destination sheet (in this case "Temp"). The criteria for advanced filter is on an altogether different sheet (in this case "Reports"). The macro is actually simplified for the purpose of the question, and I want to re-use the code several times, hence the use of variable "filterRng". When I run it, I get the subject error at the bolded line in the code below. I'm thinking that the Advanced Filter doesn't like a variable as a range reference, as it runs perfectly well if the commented out line below the problem line is used instead.
Sub Test()
Dim i As Integer Dim rngData As Range Dim filterRng As Range
Set filterRng = Sheets("Reports").Range("A121:K124")
I am trying to apply named cell from another worksheet to existing formula.
For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message "Microsoft Office Excel cannot find any references to replace" when I try to apply the names using Excel menu "Insert - Name - Apply".
see the attached spreadsheet including the data. In Column AA I have created a formula that looks at Column B, and pulls each unique value from that column. add to this formula, or propose a new one, that pulls unique values into column AA if at least one of the rows has an "Actual Finalization Date" in column D that is in 2014?
Hello, below is a sample of my excel sheet. The number of rows keep changing daily and the Month and year column has to be populated automatically as and when there is a new row inserted. I tried the formula =MONTH(A:A) and I paste it on the entire column. it works for rows with data but I also get 104,000 odd blank rows added to my excel because I pasted the formula on the entire column.
way to put a simple formula in a colum that will apply to the entire column, no matter how many rows without having to physically copy it to the number of rows required?
I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???
I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.
I am trying to come up with a macro that will find rows that contain the words �as % of Revenue� and then calculate and apply the appropriate formula to the actual and budget columns. I have attached a sample of what the source data look like.
The formula divides the category�s total expense by the revenue in row 4. For a complete macro, I would want to also subtract the budget result from the actual result and put its result in the �better (worse)� column. I need to use this on 12 or so different workbooks which is why I�m wanting a macro.
Here is my attempt to accomplish this task; however, I quickly realized it is woefully inadequate because I neither understand how to apply it to the different columns or how to find the next instance of �as % of revenue�).
VB: Sub Macro1() ' ' Macro1 Macro ' Range("B10").Select Cells.Find(What:="as % of Revenue").Activate Range("B20").Select ActiveCell.FormulaR1C1 = "=R[-1]C/R10C" Range("B21").Select End Sub
I have a worksheet with many different formulas in many different cells. When I insert or delete a row, there is one formula in one cell that does update to reflect the change in rows. The cell is located several rows below the section where rows are changing. It is a simple formula too. Here is the formula: "= SUM($E$3:E11)*0.09". If I delete more rows that cause the highest row number to be less than 11, this formula updates. If I insert more rows and go beyond row 11, the formula does not update. I have tried using $E$11, $E11, and E$11 to no avail.
I have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50 Balloons 12567 Blue Princess balloon .86 Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27 Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
etc..
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.
writing a code where i can copy a worksheet (Sheet1), insert a new worksheet at the END (as the last worksheet), and paste to that new worksheet (which will have a different name each time a new one is added). I am using the code below, but it adds a worksheet after Sheet1 instead of at the end, and it also adds another weird worksheet that says "Dim Worksheet" in one cell, and "Set newsheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)" in another. This is not in the VBA window, it is just text in a cell in another inserted worksheet. I only want one worksheet added at the end that I can paste too (knowing that the inserted sheets will always have new names).
Code: Sub CreatePercentageSheet() ActiveWorkbook.Sheets("Sheet1").Copy _ After:=ActiveWorkbook.Sheets("Sheet1") ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) ActiveSheet.Paste End Sub
i have a workbook with over 100 odd worksheets of different names... i want to insert 1+ columns in every worksheet . For Example ... in worksheet 1 name abc .. insert 1 column in column B (put clumn b data in column C) in worksheet 2 name def .. insert 2 column in column B (put clumn b data in column d) in worksheet 3 name ghi .. insert 3column in column B (put clumn b data in column e)
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
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.
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?
I have a macro that was working fine until I added a new column to my active sheet. Now it fails with a run time error 1004, insert method of class failed. The code is as follows:
Private Sub UpdatePart_Click() Dim S As String Dim r As Excel. Range Range("A2").Activate S = InputBox("Enter the part no. you wish to update") On Error Resume Next If S = "" Then 'Exit Sub 'If StrPtr(S) = 0 Then MsgBox "Update Cancelled" Else Set r = Columns(1). Find(What:=S, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False)...............
I would like to disable cut/copy/paste and maybe delete inside a worksheet. Reading earlier threads on this subject provided the code to put in the ThisWorkbook module, where it works great for all my worksheets. However, there is one worksheet where it is necessary to have copy/paste, either with control c or right click/copy. Here is part of that code provided by Tom Urtis:
Private Sub Workbook_Activate() Application.CutCopyMode = False Application.OnKey "^c", "" Application.CellDragAndDrop = False End Sub
What do I replace Application with to work on just the worksheet level?
I'm trying to create a fairly dynamic report in excel based on queries imported from access into a excel template (the queries are imported into separate worksheets). So what I want is simply use a =Count to find the number of rows in my imported query and based that count value insert an additional row beneath the first line of the report worksheet and copy down the formula.
I have a worksheet containing code to hide itself when the sheet is deselected:
Private Sub Worksheet_DeActivate() Sheet16.Visible = xlSheetVeryHidden End Sub
After unhiding this sheet and modifying it, I copy it. This new copy becomes the 'production' copy, and the self-hiding sheet is blanked, leaving me with a hidden blank template and a new sheet. However, the self-hiding code is copied along into the new sheet. I'm looking for a way to delete the sheet code from the copy. The best I've found is how to remove a module from the workbook.
Sub New_Sheet() ActiveSheet.Copy After:=Sheets(1) ActiveSheet.Name = Range(" F8").Value Sheet16.Visible = True Sheet16.Select Range("B13:F22").ClearContents Sheets(2).Select ActiveSheet.Shapes("Button 1").Delete Range("A8").Select ActiveSheet.PageSetup.PrintArea = Range("A1:R35").Address Call Sort_Sheets End Sub
I am trying to write a macro that should be able to copy existing row and insert values and formula into a new row just below the row. As an example below:
A B C D E
001 ADESF 500.00 1001210 EMF
The values in column C and D for the new row will be the same.
I am trying to finish off a form that allows a user to insert a row below the selected cell and copy the formula from the line above if one exists. The code inserts the line but does not copy the formulas if they exist.
Application. ScreenUpdating = False
Dim cRow Dim j As Long
cRow = ActiveCell.Row
With ActiveCell .EntireRow.Insert End With
For j = 1 To Cells(1, 255).End(xlToLeft).Column If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j) Next j
I'd like to have done is to have a blank column inserted between columns W and X(these values change so the VBA statement should reference the end of the columns) and the values that are now in column Y(April 17th values) pasted as values into the now empty column X. I would like to do this for tabs Ann-Sheet 2. I'm having a bit of trouble with setting up the loop that would go through the desired sheets.
I have another little VBA macro problem that appears to be beyond my coding knowledge.
I have attached a small excel spreadsheet, with a macro recorded (CTRL-P) of what I'm trying to accomplish. Basically, If the data in column A matches a single entry in column F, I need to copy and insert the row (columns F-L) that matches.
I have a table where random records that are automatically inserted when you press the command button 1, I managed to set all columns exept the last column (column O) because I don't know what shall i do to enter the following formula: =
This formula should be inserted in the button code in order to automatically calculate the value of O7 after that when i press the button he must calculate O8 and so on.
It is important to gradually increase the formula references cells +1, so it would be for the cell:
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
