Writing 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'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
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.
Macros that will insert formulas into cells, and I've been able to modify the macros to insert additional (simpler) formulas well enough on my own. But I can't seem to find the pattern of where to put the quotation marks! Here's the macro code that will enter my formula, including the formula (and the portion of code that the debugger highlights):
r.Offset(, 1).FormulaR1C1 = _
"=VLOOKUP(rc[-1]"",""'Blend List'""!""A2"":""E250"",5,FALSE)"
And here's the code in it's entirety:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Columns("d")) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect
For Each r In Intersect(Target, Columns("d"))
If r.Row 1 Then
If Not IsEmpty(r.Value) Then...........
I was searching through this site and found this macro ...
View 9 Replies View RelatedI need to paste the format and formulas of the last row of data into a new row beneath it. I've seen a bunch of different end row functions, but which one is best and how do I only paste the formulas and formatting?
View 8 Replies View RelatedI am creating a time sheet for a supplier & when the supplier enters a date into column A I would like a new line to be inserted below & include the formatting, formulas & data validation from the row above.
View 7 Replies View RelatedWorksheet full at Column IV how can I insert more
View 1 Replies View RelatedI need some VBA code that puts in formulas to pull data in various columns within row 2 of another worksheet.
The formulas need to be entered below some other data, which is populated from another prior macro.
The below code works well but as the prior macro can populate a different number of rows on different days, the Row count then also changes and doesn't always refer to row 2 of 'HLDRT before' tab.
ActiveCell.FormulaR1C1 = _
"=IF('HLDRT before'!R[-22]C[-18] = ""A17"",RIGHT('HLDRT before'!R-20C[14],3)&'HLDRT before'!R-20C[9]&'HLDRT before'!R-20]C[-13],"""")"
Is there a way I can tell the above code to always use row 2 from the 'HLDRT before' sheet, while keeping the column number lookups the same?
I think there is a way to do it by removing the [] signs but I can't get it to work. I also then need the macro to copy these down so I'm not sure if using $ will cause problems?
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
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.
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 RelatedI 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 RelatedWhat I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
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.
I am using the following ....
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
I have a worksheet that has several columns.
Column C = Project numbers with a validation list to choose from ("PN1, PN2, PN3, PN4")
Column D = Expense Category with a validation list to choose from ("ODC, TVL, MAT")
Column E = The account number that is associated with each specific project number/expense.
Now for several projects, the ODC and MAT go on the same account number. But on another ODC and MAT have their own account numbers.
For Example,
If PN1 / ODC then E = 1.001
If PN1 / MAT then E = 1.001
If PN1 / TVL then E = 1.002
If PN2 / ODC then E = 2.001
If PN2 / MAT then E = 2.001
If PN2 / Mat then E = 2.002
If PN3 / ODC then E = 3.001
If PN3 / MAT then E = 3.002
If PN3 / TVL then E = 3.003
The range of the rows are 4 thru 500.
Is there a way to write a macro to automatically populate the account number in Column E based on the criteria selected in columns C and D?
I've got a large excel spreadsheet that is used for cost calculations and I need to be able to replace the formulas in three specific columns with the value of 0.01. I also need to be able to change the values back to their original formula. My problem is getting the formula back into the cell. The formula begins with "=IF(And" which seems to be where I'm getting most of my errors. So right now I run the code and the value of the cells in the three columns change to 0.01 and when I run it again I'm getting errors. Here is the formula. How should I be entering this in VBA to get this line into my cells? The row numbers will be changing as I go down, but the rest of the formula does not. =IF(AND(E48<>"",AH48<>0),(AH48/E48)*100,0)
View 3 Replies View RelatedThere 2 columns, 1 in which you input data and the other which has a formula which uses the data to do a calculation. There are about 30 rows already filled in.
When I go to insert a new row of data, the formula just appear after I type in the input column. If I click on the cell where the formula appeared before I typed in the data, there was no formula there. It is almost like an autofill, excel knows that I am going to copy down the formula. (It also copies down the formatting)
delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.
Account Number
211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row
211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount
[code]....
I am trying to modify the macro listed below for the following example. It would work when I have only numerals in the cell but this new query, the cell has both letters and numbers.
I want to compare column "M" from worksheet one to column "B" in worksheet two. If the information matches, then copy the value adjacent from worksheet two column "A" to worksheet one column "L".
Here is the macro that worked for me using a search of only numerals.
figuring out why the data doesn't copy?
Macro:
Sub merge_accession_PS_rad_productivity()
Dim rng2 As Range, c2 As Range, cfind As Range
Dim x, y
With Worksheets("Imaging_Summary")
'N4=Accession on Imaging Summary worksheet
[Code]....
I have a macro that searches a column for a date and then selects the appropriate date. The next step is to then copy the row and insert a new one below the copied one and paste only the formulas and none of the data. I have tried several iterations but all of the seem to paste the data too.
View 6 Replies View RelatedWhat I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
4) repeat requirement 3 till no more Scenario's
I have a simple command button in my worksheet that will insert a blank row and copy formatting, but not the formulas. How do I adjust the code so that the formulas are copied to the new blank row? This is currently what I have:
Private Sub CommandButton2_Click()
Sheets("WAWF Track").Range("A9").Select
ActiveCell.EntireRow.Insert Shift:=x1Down
End Sub
What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg).
Sub ChkDates()
Dim c As Range
Dim DelRng As Range
Dim ArcRng As Range
Dim i As Long
Dim l As Integer
Application. ScreenUpdating = False
Worksheets("Report").Activate
For i = 60 To 8 Step -1
Set c = Cells(i, 33)
If IsDate(c) Then ..............
I am brand new and I don't know a ton about macros and programming in Excel.
What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.
Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is difficult.
See attached : TEST2_2014 Sales & Commission Spreadsheet.xlsx‎