Pasting Data From One Sheet To Another With Formatting And Cell References
Feb 11, 2010
I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.
One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.
I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.
Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.
A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.
By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.
I am making template and trying to preserve formatting when users paste into a cell. From my research, I have found that this is impossible with simple format protection, it works to prevent changing formating, but if something is pasted (with normal paste), the format will change.
I have found a few solutions, but can't get them to work correctly
Solution A: Disable normal paste and substitute Paste Special - Values
How will this work with pasting via CTRL + V and the right click menu? I need both to function.
Solution B: Allow users to paste normally, but use a macro to copy formatting from a hidden sheet
I actually found code for this, but I couldn't figure out exactly how to impliment it. The sheet that users will be pasting into is called "Batch Summary" and I was trying to pull formatting from a sheet names "Batch Summary Format", but was getting an error.
Is it possible to preserve original text formatting when copying and pasting only part of a cell. Like, for example, a cell may contain ten words one of which is in red and underlined. How do you copy that one word and paste it in another cell so that it remains in red and underlined?
I have browsed for two days looking for material on effecting this. The technique quoted most is watch for undolist for paste & auto fill then undo the change and paste the value again with specific formatting needs. This technique can be implemented in a worksheet_change event handler. The problem that I am facing is that the user can copy and past while my VBA is running. Once it is caught inside my own application class object event handler all that undo stack is already cleared. Therefore I have nothing that tells me if the user has entered the value via a paste action. By the time the value is pasted, all that formatting would have been entered. For example, a value that is interpreted by another workbook as DATE will have my destination formatting changed to DATE as well even though it is designed to be TEXT at design time.
So far I have not been able to think of a scheme to deal with this.
My basic intention is to always ensure the destination cell formatting remains as TEXT. If something is already interpreted as DATE after the paste even if I can convert to TEXT the string will appear totally differently. I have to find a way to paste the whole thing as TEXT in order to keep the string the same. The problem is that there is no intrinsic PASTE event in VBA. Without a method to undo that paste I cannot catch this at all.
How to copy and paste conditional formatting with our changing the cell values. My first row of data starts in row 4 and here is what I have in D4
If D4 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D4
Now I want to copy that format and paste it for all rows in column D. The problem I'm having though is when I paste the formula it doesn't change to match the row I'm on. For example, when I paste it in D5 this is what I get
If D5 is less than or equal to AA4, AW4, BS4, CO4 then it will highlight D5
How do I paste it so that it will update to show AA5, AW5, BS5, and CO5. I want to be able to do this for 500 rows.
I have this fairly simple formula which decides whether to shade a cell or not
=AND($X$1<>"TBD",R3<>"None",AC3="Y")
This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.
I have several sheets with one master sheet. I have a trigger on each sheets that manipulates the data on that particular sheet. Here is the code I'm using:
Code: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim cell As Range
Set r = Intersect(Target, Range("A:A"))
[Code] .....
Now the point where i put new code is where i want the information from that particular row, column a thru column m to be special pasted linked to the first blank row from the top down to be pasted starting over from Column B. so A1 would be pasted on B1. I ran the recorder and got this as the code ActiveSheet.
Paste Link:=True so i do i incorporate what i want.
I have multiple worksheets and I am looking to take specific information from those worksheets and paste them into a new one, thereby compiling the information. The "C" column is populated with certain cells that say "Not Acceptable". This varies from sheet to sheet, but all in the C column. I'm looking for a code that will run through all the sheets, find the cells that say "Not Acceptable", copy that row of information it is in, and paste it onto a specific sheet that we'll call "Summary"
I am using 'Dim' to define a string for SheetName, a variable.
I have tried with no success various ways to try and paste data to a sheet with this variable. I know the variable is correct as I am using it to make a new sheet then rename it and also move it.
I am trying to create a macro that will take inputs entered into a range of cells in 1 worksheet and then paste it into another range on another sheet. The target range is selected based on a condition and once the values have been pasted then the target range should be locked.
I have been trying the following code but keep getting the Run-time error 1004 telling me that the PasteSpecial method of range class has failed. Can some one explain what I may be doing incorrectly?
Code:
Dim i As String i = Range("Month").Value
Select Case i
Case "January" Sheets("INPUT SHEET").Select Range("Input_In").Select Selection.Copy
X Y Z Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep A B C
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
I'm trying to write very simple code that copies totals from one worksheet and pastes to a different worksheet. Normally, it is a one cell to one cell copy/paste. For instance, I want to copy the total found in cell "C4" in Worksheet "BalanceSheet" and paste it into cell "C14" in Worksheet "SpreadFinancials." I'm able to do this fine, and the code below is what I've been using.
Occasionally though I'm needing to sum up 2 cells in Worksheet "BalanceSheet" and only copy/paste the summed total into one cell in Worksheet "SpreadFinancials". For instance, I want to sum the totals found in cell C8 and C9 in Worksheet "BalanceSheet" and then paste that summed total into cell "C8" in Worksheet "SpreadFinancials". I'm looking for simple code that does this. Here's an example of what I've been doing so far for one cell to one cell copy/pastes:
Sub PopulateFinancials() Sheets("BalanceSheet").Range("C4").Copy Sheets("SpreadFinancials").Range("C14") Sheets("BalanceSheet").Range("D4").Copy Sheets("SpreadFinancials").Range("E14") Sheets("BalanceSheet").Range("E4").Copy Sheets("SpreadFinancials").Range("G14") End Sub
I have code that selects and copies data from one sheet onto another sheet. the sheet that is getting data copied too has borders defined. When i run the code i get a debug error. Here is the issue.... when i remove all the borders it works fine. I have tried everything i can think of to solve the problem but have had no luck. anybody have an idea what is causing this. i attached the workbook file so you can see what is going on.
I have a macro which opens a csv file, moves a few columns about then pastes the data into a master workbook. The problem I have is that even though there is only around 1,500 rows of data on the csv file, the sheet on the master workbook is showing up to row 1,048,576 after the data has been pasted in! This is causing issues as I use this data later on in the macro and there are a couple of 'find and replace' commands that take an age to run because of the number of blank rows.
I am looking at trying to develop a workbook for my job that would make things alot more efficient.
Firstly, I download data from a web based app into excel. I now want to take certain full rows and paste them into another sheet depending if they have certain text in a cell. So i want to search the entire coloumn for a value of "OR" and if this is present copy the entire row into a new sheet ?
I have just opened a sheet in work and this phenomenon has occured. Basically the data validation: list appears to be referencing a sheet that doesn't exist! My initial thought is that the original sheet name had been changed, but on trial the list seems to change reference. I've checked that the sheets aren't hidden
Any way to always include the sheet name before the cell address in a formula? For example, in my "Summary" sheet, I have a formula:
Code: =A2 + B4/B5
However, without typing anything else, I'd like the formula to populate the sheet name since I will be extracting the formula as text and need the full reference:
Code: =Summary!A2 + Summary!B4/Summary!B5
A way to get around this is to enter the equal sign, then temporarily select another sheet, then go back to the "Summary" sheet where all cell references will include the sheet name, but if there is a quicker way to do it using some kind of property or event in VBA, that would be ideal.
I have a macro that copies all my sheets onto a new sheet called combine. It is pasting onto the combine sheet starting on cell A2 instead of cell A1. Following is my macro.
Sub Combine() Dim J As Integer Sheets(1).Select Worksheets.Add Sheets(1).Name = "Combined" On Error Resume Next For J = 2 To Sheets.Count Sheets(J).Activate Range("A1").Select Selection.CurrentRegion.Select Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2) Next End Sub
Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?
This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I regularly copy into notepad then copy that into excel because I don't want to carry over the text formatting. Is there a better way to do this? To set up the cells so that they don't try to carry the formatting over?
Note: The "X"s are actually not in the cells, they are for illustration purposes only. Instead of the "X"s, I want to fill the cells marked with X with the color red. The cells in sheet2 have no value except row 1 and column 1.
I am looking for the formula to fill into the conditonal formatting field for sheet2.
The formula should detect how many letters there are in row 1 of sheet2 (for example here we have 4, namely A, B, C and D). If I add a new column E later, I just want to re-apply the formula to those new cells, not edit the formula itself. If there is a new column E, it will also be added on sheet1, but not necessaryly to the right side of the column with the header "A", maybe it will be added between "B" and "D".
The number of rows will not change, they stay static at 1-5
Here is the underlying task.
Sheet2: A column that has all days of the year, from Jan 01 to Dec 31, from top to bottom, starting in A2 going down to a366. In column B, C, D and so on, in row 1, I write the names of different countries. Sheet1: In row 1 I write the same country names as in sheet2, but not in the same order. Below the country names I list the official pulic holidays of the whole year.
I need the formula for conditional formatting that will color the intersecting cells of Country names and days of the year. And I need the formuly to be dynamic so that I can add more countries later without have to adapt the formula for new columns manually.
So far I have the formula to count the filled cells in row 1 on sheet1. I also have the formula to find the country name in sheet2 and return the column number of the same country in sheet1. Last but not least, I can make this work with a static formula, but replacing the static pieces with the dynamic pieces mentioned above just won't format the cells as desired.
I created an excel workbook with multiple cell reference throughout the various worksheets. I did this because I was creating a dependent drop down list. When I try to sort my data alphabetically, all my cell name references get changed. How I can sort alphabetically without losing my cell name references?
I am in a situation where I have to copy-n-paste text from a webpage into Excel. When I do this, the pasted text keeps the formatting of the webpage. The font is the webpage font. The background color is the background color of the webpage. Etc.
I know that I can paste just the text itself, without any of the formatting, by right-clicking, selecting "Paste Special...", and then selecting "Text" instead of "HTML". I am wondering if there is an option somewhere that will do this automatically when I hit CTRL-V instead of requiring me to pull up the Paste Special dialog box.
I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.
What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.
My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.
