Cut+paste Formulas+formats To Other Cells
Nov 17, 2008
I've got two pieces of code.
1. cuts+pastes all formulas and formats to another location.
2. cuts+pastes required formulas but not cell formats to another location.
The s/s is over 330,000 rows deep which makes manual changes impossible.
I attach small s/s containing both codes.
What I want to do is copy+paste formulas+formats from cells having a number in the cell below to cells 4 columns along and two rows down. The s/s illustrates this.
View 11 Replies
ADVERTISEMENT
Dec 18, 2008
1. In whatever cell is selected when the macro is run, enter a new row.
2. Copy the information from the row directly above the new row and paste (values, formulas, formats, etc) into the new row.
3. Return to column P in the new row, i.e if the new row is row 11, then return to P11, for row 12 return to P12, etc.
I have tried recording the macro but because it is hard coded to specific rows, its not working. I have attached a sample copy of the sheet (had to zip due to the size of the file).
View 3 Replies
View Related
Jun 25, 2009
I have created an Excel template for others to use. They can enter data in certain unprotected cells and the data is linked to other places in the workbook. If they Cut and Paste something in the unprotected cells, the formulas get a REF#. There is no problem if they Copy and Paste. How can I prevent the REF# error without resorting to VBA?
View 3 Replies
View Related
Apr 25, 2006
I have a table with 15 columns and 10 rows (initially all cells are blank but with formulas and formatted) and I want a way to add automatically a new row in my table each time something is entered on first cell in each row. For example if I enter something in cell A1 (first cell in my table) then automatically add 11th row and if I delete the value in cell A1 it is ok to leave the 11th row there but if I I go on and enter a vlaue in A2 then add row 12th. Of course I want the new rows to have the same formats and formulas as the previous ones.
View 9 Replies
View Related
Jan 1, 2009
I have a spreadsheet with customised formats and formulas and the problem is that when add a new row I have to go through and manually copy all the data to the new row and as there are many columns in the spreadsheet this takes forever.
Is there anyway I could automatically copy all the formats/formulas when I insert a row?
PS: I do not want to copy any data into the cell only the formatting and formulas
View 10 Replies
View Related
Jan 26, 2008
I have a names in column A starting with row 7 as follows:
Walden, Douglas E
Haden, Michael
Wilson, Matt David
I need help with coming up with 3 formulas:
(1) That returns the First, Middle Initial, & Last Name in column P of the same row:
Cell P7 Douglas E Walden
Cell P8 Michael Haden
Cell P9 Matt David Wilson
(2) That returns the First & Middle Initial in colum Q of the same row:
Cell Q7 Douglas E
Cell Q8 Michael
Cell Q9 Matt D
However, I am so new at this, the simpler the formula the better because I want to try & understand it, not just learn it.
View 11 Replies
View Related
Apr 28, 2007
The problem: I am getting values when doing a pastespecial for formulas and formats.
Manually copying and doing a pastespecial with the mouse gives me the correct results. Below is code I got from the forum, "here", and am running in a test workbook.
Option Explicit
Sub Test2()
'
Dim rSource As Excel. Range
Dim rDestination As Excel.Range
Set rSource = ActiveSheet.Range("A1:C1")
Set rDestination = ActiveSheet.Range("A1").End(xlDown).Offset(1, 0)
rSource.Copy
rDestination.Select
Selection.PasteSpecial Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
valKill:
Set rSource = Nothing
Set rDestination = Nothing
Exit Sub
End Sub
Does anyone know why I am getting values? What do I need to do to make this paste only the formats and formulas?
View 9 Replies
View Related
Aug 22, 2006
In the sample worksheet, I need VB code to copy the formulas in column D, to paste them as values in the same cells (without removing autofilter by clicking on menu Data>Filter>Autofilter, then edit>copy>paste special>values).
sample2.xls
View 5 Replies
View Related
Aug 22, 2006
in the sample worksheet, I need help with a VB code to copy the formulas in column D, to paste them as values in the same cells (without removing autofilter by clicking on menu Data>Filter>Autofilter, then edit>copy>paste special>values).
View 3 Replies
View Related
Jan 11, 2014
I am attempting to create a template to make time and dollar budgets for various projects easier to create (20 plus projects a year with varying number of employees).
I want to have a template in which you input the number of employees working in a cell (IE. 3 employees), and with that the table expands to have enough rows to have 3 employees data input. However, only insert the number required but not overriding "totals" row below. With the new inserted rows I would like to keep the same format and formula for the row it was based on.
Example :
week 1 hours
week 2 hours
total
[Code].....
View 2 Replies
View Related
Nov 15, 2008
I have a spreadsheet that I have people fill in. A few columns have VLOOKUPS. I want to allow people to insert lines in this spreadsheet and have the formula automatically copy in the new line. It seems to already have that behavior for formatting and conditional formatting. Is there a way to also have that for formulas when inserting lines?
Name City Assigned Salesman
-----------------------------------------------------
john Mountain View (formula using VLOOKUP)
cindy Palo Alto (formula using VLOOKUP)
ronnie sunnyvale (formula using VLOOKUP)
---------------------------------------------------
Insert line between john & cindy. Formula doesn't copy.
I ask them to copy the formula but they often forget. If I can make it a little more robust,
View 11 Replies
View Related
Oct 2, 2013
I had a macro on my previous laptop that worked, but didn't have the macro backed up so it was lost.
I need a vba macro that will copy all active/selected worksheets into a new workbook. Included requirements:
* Maintain tab names
* Only bring in contents/data with all formats (including logo image, but NOT formulas or hidden rows/columns)
* Keep the file name the same except adding "- FINAL" to the end
View 3 Replies
View Related
Aug 27, 2009
In my spreadsheet, I have approx. 300 'blocks' of data, one for each 'vendor'. I am tracking 5 stats for each vendor for 12 months. Each 'block' has 5 formulas WITH conditional formatting (only one condition used) for each month, so each 'block' has 60 individual cells with conditional formatting. Excel throws an error when I try to copy the block of formulas and formats down the sheet a few hundred times. I have figured out it isn't the formulas that is the problem. It must be some kind of limit Excel 2003 has for how many cells in a workbook can have conditional formatting.
View 9 Replies
View Related
Apr 24, 2006
I have a very large table with 7000 rows and each time I open the file it takes excel about 1 minute to open it.Initially all cells in the rows are empty. Each row in the table has the same formats and formulas(eg. in J106 I have =IF(C106=1,1,"") and in J107 I have =IF(C107=1,1,"")).So the excel allocates all the memory and does the formatting and computations beforehand. How can I add new rows(preserving the formats and formulas of previous ones) dinammycally and automated? An example will be if I start enter data in a row add another row at the end of the table or a button that when I press it adds lets say 100 new rows in the table. Below is the table, the rows in the table to which I add data start form C15 to C7014.
View 3 Replies
View Related
Oct 13, 2008
I have a workbook that has several INPUT areas that are composed by many rows and columns. These areas must be unprotected because the user must be able to fill them in with different numbers.
The challenge is that these areas have different conditional formatting to try to prevent the users to make mistakes, but the users always fill out one row and then copy down the others, when they need to be the same. This is not always the case. Then, the conditional formatting is lost.
The challenge is how can I allow the user manually fill out numbers, but they should not be able to copy and paste, in any way, within these different areas in the workbook?
View 9 Replies
View Related
Aug 4, 2009
I have a code that copies a template and pastes it into another tab. However, it is copying the conditional formatting and pasting it as well. I already have conditional formatting on the other tab and do not want it to paste another set of conditional formats because the formula's are changing between the copy and pastes.
View 9 Replies
View Related
May 3, 2007
- I have a worksheet (W1) which i have filters
- I have another worksheet (W2) that is formatted for a institutional purpose where i must put the things selected in the W1, but the problem is that: in the W1 i have a cell (A1) with some data but in W2 i have a space composed with one line but with several columns and excel says that he cannot paste the information copied in W1 to put in W2.
there is any way to avoid this problem because at the moment i am making copy paste one by one, and it takes to many time and i would like to copy and paste the information selected by the filter in W1 to paste in W2.
View 9 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Jan 10, 2012
I am trying to paste formats and validations to the current row of my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targetcol, ganntfirst, ganntlast As Integer
On Error Resume Next
Application.EnableEvents = False
targetcol = Range("BudgetHrs").Column
ganntfirst = Range("Ganntfirst").Column
[Code] .....
The code in orange is the problem code, and I have a range named format which is actually Row1:1
And I want the formats and data validation pasted into the current row and also the next row.
View 2 Replies
View Related
Feb 14, 2013
For some reason the following code, when executed, pulls a named range from the source workbook (sProj_Name). I feel like I have used this exact same code before and not encountered this issue. This named range is linked to the source workbook so if I save the file and re-open I get the wonderful 'would you like to update links' pop up. I cant figure out why, when I am forcing it to paste only values and formats, it is picking up this named range. In addition the source workbook has way more than one named range, so why it doesn't pull all instead of just one is a mystery to me as well.
VB:
With Workbooks(sProj_Name).Worksheets(2)
.Cells.Copy
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
Sheet2.Cells(1, 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
End With
View 2 Replies
View Related
Nov 16, 2007
my macro pulls download in on sheet1. On sheet2 it makes a pivottable of it with horizontally displayed the suppliers and vertically the codes of products. This is of course dynamic (one month it may contain 10 suppliers + 8 products, other month 15 suppliers + 20 products). On sheet3 is the (static) lay-out of all suppliers and all products. Now, what I want is that all fields <> empty (or zero) from the pivot table are to be copied and placed in sheet3, the 'report' I have to fill in. index and match won't do the job I think.
View 2 Replies
View Related
Apr 19, 2009
the following i have 1500 rows and around 15 columns to change. format says 1.49-2
I need it to say 01:49.2
when I highlight it and go into format cells. and change it to min:ss.0 it changes to the following 48:28.8. i have included a file for you to see
View 5 Replies
View Related
Feb 20, 2007
i've inherited a spreadsheet that is causing me a problem with a custom format. I have two cells each with different custom formats. The first (cell A) is formatted as thus 0.0;0;. The second ( cell B) formatted as 0.000;0;
The number in each cell will vary. Cell A is always positive and cell B could be positive or negative. The problem occurs in Cell B when this should be a negative number as it picks this value up from another cell ,say cell C. so Cell C= -0.614 but cell B shows this as being 1. I'm sure the author of the sheet did this for a reason. Why does the cell display the number 1
View 3 Replies
View Related
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
Dec 8, 2008
I want to copy everything on several sheets to one sheet.
I do not want to carry the formulas (some are absolute)
I do want all data. formatting and layout (cell fill, merged cells.....)
View 5 Replies
View Related
Jul 28, 2009
I want to be able to open the Excel spreadsheet, and have some easy way to post formulas into that document. I was thinking a Macro would work, but I cannot seem to find a way. Columns A through V contain the data that is exported from the payroll system. I want to find a way to paste my formulas into W2:AH200. The formulas will always be the same.
I'd like the user to be able to:
1. Export the payroll information to Excel and open the document.
2. Run a macro (or some similar action) to place the formulas in W2:AH200 into the spreadsheet.
3. Input data into AH2, AH3, AH4, and AH5.
A sample document is attached. Columns A-V are what get exported by the payroll system, and W-AH are my formulas I've added to this sample (and what I want to be able to easily add to new spreadsheets).
View 2 Replies
View Related
Mar 30, 2014
How pasting values/formulas only works. I have a spreadsheet I've been using to record product sales, commission, shipping etc. It currently has three pages of records. But now I have a new spreadsheet I want to use with basically the same 'look' as the old one (same columns, entries per sheet, etc) but using better formulas. I don't want to manually input all the data from the old one and I thought I could copy all the cells from the old one and then paste values only into the new one. But when I do that, it pastes the values but also deletes the formulas in the new one. So then I thought I could copy all the cells from the new and paste only formulas into the old one but then it deletes all the values in the old one. Is this what is supposed to happen? And if so, is there a way to achieve what I want?
View 1 Replies
View Related
Jun 23, 2014
How can i change this code to paste values not formulas?
Code:
Private Sub Admin_Click()
Range("BO40:CI73").Clear
Dim rng As Range
[Code]....
View 2 Replies
View Related
Mar 6, 2007
I have setup a forecast model and now find it necessary to update many formulas
I have set period 1 formulas correctly and would like to copy through periods 2-12. However each period has already been populated with data that need to remain intact in this process
For example,
I have formulas to copy at I5 and I9. I6,7,8 should be skipped
These formulas should be pasted to K5 and K9 respectively, while leaving K6,7,8 intact
This is of course a simple example, the column contains 15 formulas to be copy/paste
View 9 Replies
View Related
Sep 30, 2007
I have a spreadsheet that is very large and formated a certain way. I want to make sure that a user cant put the cursor in the lower left corner and copy cells by dragging, copy & paste, or do anything other than input data and be able to highleght text and reinput data in case they mistyped or misspelled the data.
ex: user clicks on cell E33 and types in '1234568' and then move to next cell. The user can click back on E33 and can correct by retyping. That is all the user is allowed.
The cells in question are also in ranges if this helps. EX: 'E33:AJ67'
View 9 Replies
View Related
