I have several cells which I usually need to copy and paste. These cells contain single characters with a different format than the rest, as shown here:
inventar
not just with one word, but several words within a cell. The problem is that when I try to paste a part of a cell having multiple formats, it always pastes the text in uniform format, disregarding the format of those letters having a different format.
Does anyone know of a way to paste text having multiple formats when it consists of a part of a cell's content and not the full cell?
I have a template that works from Excel, but is saved in a .csv by default because .csv is the only compatible file format with the database we use. I have one column that I need to be in a "text" format, and another column that must be in a number format with no decimal place approximations (whole numbers). I know how to go in and format these columns myself, but it is clear that .csv does not like it. Every time I open the file, it resets the formatting in my columns. So, I have to re-format these columns every time I open or re-open the file. If there's information in this file, I have to re-format my columns and re-enter my information so that it is formatted correctly. For example, my first column must be text because it will have things like 001,002,003 etc. I tried using a custom format ###, which did not work. If I save the file and leave it alone, it preserves the format when I import into the database. If I have to re-open it to add something or make changes, all of my first column info is now standard numbers like 1,2,3 etc. So, I reformat the columns, and I have to re-enter 001,002,003 etc. This gets annoying after awhile, especially if my sequences (numbers) go into the triple digits.
Obviously, there's no easy way around this due to the features of a .csv file. So, I would like to add code that executes on file start-up (or open). I want this code to format my columns as stated before, one column must contain text format, another has numerical format with whole number approximation.
I have two cells. Cell A1 contains ‘The date is ’ cell B1 is formatted in the date format, 2007-01-24. I need to concatenate these two cells so that on cell C1 I have ‘The date is 2007-01-24’ but whatever I try excel keeps on changing the date to a text value and hence I am left with: ‘The date is ‘This is the date'39141 or this is the date 39141. how to get to the result that I am after?
I have two files: let's say File1 and File2.File1 has one column: datas are in a text format.I copy this column. I want to paste it in my second file.File2 is closed.I open it and i paste my column. Here's my problem. If i let my first file opened, there's no problem(!!): my column is pasted as i want. My datas keep their original format. But, if i close my first file and after, i open File2 and i paste my column, then my datas lose their original format.My column takes a numeric format.I can see it because my datas are on the left in the column.And i can chek it by a sum: if i do 400+200, for example, the result is 600. In the first case(text format), the result is 0.I don't understand why.If someone can explain me this situation, i'd be happy.I repeat: my second file is closed. I open it after my copy in the first file.
I 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?
I have switched from an ageing Mac Powerbook, Excel 2004 for Mac, and an out-of-date version of Firefox to an IMac with Excel 2011 for Mac and Safari as a browser. The only reason why I mention this is because I don't know if it was easier to do this on an older platform (one of those bizzare situations where going forwards is actually a step backwards).
In short I like doing things with Sports data in Excel. Usually I would hit my favourite website for pulling sports data (which incidentally I don't know if this has changed as I've been too busy to do much of this kind of thing for about 4 months - or if it is just a different format on a newer machine/different browser);
[URL] ....
The link should be showing NFL results for this year so far (I like the site as it has a handy amount of other sports etc.).
I would usually then go edit/paste special - unicode text and then hit the paste options, use the text import wizard, go delimited, tabs - and usually everything would come in on a row and then I could do what I need to do with it (e.g. use Excel's text functions to get the data - taking spaces out for example - to where I want it to do other things).
The problem I seem to be having now is that when I do the above - things no longer seem to come in on the same row (which makes life difficult for doing things easily with the data). Once things end up on different rows it becomes a pain to do basic things.
In short is there a way round this (or do I need to find another source for my data with tables laid out in a more friendly way to "borrow" data)?
I have a column filled with time data that I want to copy in to a column formatted to a time format (hh:mm:ss), the thing is when i copy the data to it the format does not apply unless i double click each of the brackets, there are 14000 lines so doing it manually is a hastle to say the least, is there a way to apply the time format immediately?
I have backwards co-workers that can't figure out how/why to use conditional formatting and have deigned it "not acceptable".
They've requested I fill in a HUGE spreadsheet and change the font color to red if it meets a criteria.
Simple enough to do with a conditional format that compares my data.
HOWEVER, now I need the individual cells to stay red and remove the conditional format (because conditional formatting "confuses" them). Removing the condition causes the color to go back to black. I've tried all sorts of paste specials (formula/format) to no avail...
I have a consolidation workbook and source files.I would like to convert the data from the source files into a list format in the consolidation workbook.
I have attached a sample of the sheet format of the source files in the attached file, called 'Page 5'. The other 2 sheets are 'Template' and 'Instructions'. The 'Template' sheet is what I imagined would be the list format of the data copied from the 'Page 5' sheet. Instructions is where the lookup table for currency is.
So basically starting from row 8 in 'Template' sheet, I would like to copy and paste from 'Page 5' sheet to 'Template' sheet: - H2 to A8 & B8 - B2 to C8 - According to the list of currency in 'Instructions' sheet, lookup the currency according to operating unit in C8 and paste to D8 - D5-I5 to E8 - row A8-A23 to column F-N
I actually have had a similar problem before, which Derk has helped me here - link: Use Access or XL? (I've decided to start a new thread because this is a more relevant forum).
I have tried to modify the code but I am rather lost as to which part I am supposed to modify.. Note that in this code, the source data is in separate file instead.
Sub add()
Dim wb As Workbook, f As Worksheet, t As Worksheet, j As Integer, k As Integer, n As Integer Dim mty As String, yr As Integer, d As Date, bu As String, cur As String, sTodo As Variant
sTodo = Array("Page 5") 'finish adding the names Application. ScreenUpdating = False Set t = Workbooks("Example1.xls").Worksheets("Template") i = t.Cells(65536, 2).End(xlUp).Row
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.
can anyone help me on how to create a formula or a macro that will automatically sort while preserving the relationships in rows without having to press any button? I have attached a sample workbook. Basically column ABCD under row 8 to 15 contains manual inputed values. While column ABCD beginning from row 24 to 26 should contains the automated sorted preserved relationship values.
I'm having a devil of a time trying to preserve the formatting on a pivot table that I made in Excel 2003. As near as I can tell, I've done the appropriate things --- check the preserve formatting box, uncheck autoformat --- but my formatting is not preserved when I pivot.
Adding to the mystery is the fact that some formatting is preserved but not all. I'm guessing either I'm missing something obvious or this is just an unruly, rogue pivot table that refuses to be formatted.
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.
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data Column B: B2 of 'Source' Column C: B2 of 'Source' Column D: B1 of 'Source' Column E: row 6, relevant column column F: column K column G: row 5, relevant column column H: the specific amount
So basically I'm creating an entry for every amount in the table.
Is it possible to lock in the formatting that would require 12 digits even if someone was copying and pasting to the cell?
We are looking to make our users add information into cells in the format 0-00000-00000-0. They are often copy and pasting, thus overwritting the custom format we have in those cells.
I've looked at validation, but custom only works for a formula.
For simplicity sake if I wanted to paste the letter A in to a cell that already had the letter B in it to make the cell read AB how could I achieve this?
I have a macro that is checking to see if a column meets my criteria. If it does I have it set up to copy the entire row and paste it to the next sheet. How can I write the code to tell it to paste to the next available row on that sheet?
If I want to "Insert Copied Cells" in place of Yellow on Row1 and shift the cells down, the rows would look like this:
[Code] .....
How can I make it so the rows would be the following during such a paste:
[Code] .....
This is because I have hundreds of cells that I need to insert as columns all the while pushing complete rows down. If you're trying to understand why, it will be for some online marketing purpose as demonstrated here in this sheet: [URL] ...
I have the following which works as it should. However I want to tweak it so that instead of the Environ Username getting pasted into the sheet under the last used row in Column U, it pastes the environ username in the first blank row it finds in Col U starting from row 1 and looking down.
I've got cells which change after I use Solver to do simulations, These cells after the calculation is run I'd lke to paste into a seperate sheet and row for example (in preparating of charting them for example)..
I would like to paste it as folllows for example... .. but problem is, let's say the below list are in column A of the new sheet, than column B would be the first output of the calculation in the other sheet, column C would be the second output from the calculation in the other sheet. and so on..
I don't know the first thing on scripting, but I'd like to click on button where the calculations are made which when clicked on ,will paste to the other sheet in this sort of manner below.
Each calculation will use another column.. so the script must recognise there is other columns to the left already filled, ie. B, C, D, E F, coresponding to the results of calculations already performed.
I have 2 excel sheets with Donor ID numbers on both sheets. What I need to do is match them up I need 3 rows Original # Assigned # Invoiced #
the original number and the invoiced number will be the same number, but I need to copy the invoiced number to the other sheet that has the original and assigned #'s on it and match it up.
I am trying to use the code below to select certain information from different areas of each sheet in the workbook and paste it onto the List sheet one after the other so I end up with a long list made up of short lists off each sheet. The macro runs through each sheet OK. each sheet has the correct area I want to copy highlighted, so I know it's selecting the right area for copying, but the 'List' sheet is blank, i.e. nothing has been copied to it.
This macro is one I'm trying to put together from other macros I already have, and they all work
Sub Generate_Repair_Kit_List() For Each ws In Sheets ws.Activate Const testColumn = "A" ' change for your data Dim lastRow As Long Dim topCell As Range Set topCell = Range(testColumn & 1) ' top of column lastRow = Range(testColumn & Rows.Count).End(xlUp).Row 'work up from the bottom until we hit 'a cell that has a border on the bottom edge. Do While lastRow > 1 lastRow = lastRow - 1.........
I want to either retain validation in a cell when I paste data into it - or
I want to apply a validation to a cell which already has data in it.
The validation only seems to work when you apply the rule to an empty cell then manually enter data into it. I ideally want to be able to paste multiple columns into validated columns to check for format compliance.
I need to paste Entire row in one sheet( Sheet1) to the previous row in other sheet(Sheet2) ex: If i copy 3rd row from one sheet(sheet1) than it must be copied to 2nd row in other sheet(sheet2)
Sub try() Sheets("Sheet1").Select Range("b2").EntireRow.Copy Sheets("Sheet2").Select Range("b2").EntireRow.Previous.Select ActiveSheet.Paste End Sub
