I have a template A that has lines on it, sometimes I want to overlay template B which has other lines on it, however when I past template B on top of A the formats of A are overridden by the formats of template B.
Is there any way of "overlaying" B on top of template A without losing the template A formats?
I have found this code by searching witch is perfect
Code:
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target 'With the cell that was selected,
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
The point is that i want to retain the previous formatting of cells when i select another cell. And also i would like to create a switch to turn the Worksheet_SelectionChange event on and off. The tricky part is here i guess, becouse i would like when turned off to retain the previous formatting also.
I have four different cell.
Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.
Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.
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 have a spreadsheet containing all sorts of formulas & data in Columns A - H, and a formula copied down in Column I generates a number that drives a few conditional formats in the preceeding columns.
I'm trying to copy-paste the values and formats of Columns A - H into a new workbook with this pretty simple macro...
Range("A1:H195").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
When the formats are pasted across it's pasting the conditional formatting, but they're not displaying correctly because Column I isn't coming over to the new workbook.
Is it possible to copy-paste the cell formats as they stand regardless of whether there's any conditional formatting behind it?
I am creating a worksheet that uses a series of if formulas to determine the correct cell to use in a table outside the printing/viewing area. Something along the lines of "if X, Y, and z happens then use what is in cell AY34". I have the formula working, so I can get it to go pick up what is in the cell I want.
The problem I am having is that there is a heading to the text in cell AY34 that is bold, but the rest of the text in the cell is not bold. Is there any way to drag the formatting along with the data and not just the values?
I am trying to use an "IF" statement to either apply a formula or retain the last value of the cell, is there a way to accomplish this.
E.g. = IF( A1=B1, "retain last value", D1)
last value = the value of the cell prior either to A1 or B1 being changed.
I have a workbook that has multiple tabs with data already entered. I have also built a series of tables/forumlas to display summaries of the data. My desire is to be able to copy this table with all formulas/formatting/cell sizes to the various existing tabs.
View 3 Replies View RelatedI am linking a row of data in worksheet x to a column of data in worksheet Y and would like to be able to drag down the cell reference in worksheet Y in this way:
=MIN(RuleofOriginData!AR$4:AR$63)
=MIN(RuleofOriginData!AS$4:AS$63)
=MIN(RuleofOriginData!AT$4:AT$63)
However, when I drag it down, the formula is automatically absolute for both the row and the column number instead of just the row as I indicated. Can anyone tell me why this occurs and whether there is a way to fix it?
This error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).
I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?
My workbook has about 15 worksheets with each fits to 2 printing pages.
How to copy the cell formats of the copied range in the vba.
Code:
Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook
With Application
[code]......
In effect the code looks at sheet 'data' and creates new tabs depending on the contents of column f and then pastes data from the entire row into those tabs as appropriate - this is what I asked for and works wonderfully. What I would like it to do is to also copy the format of the row that it is copying. (most importantly the cell borders and colours)
I can (I think) identify the copy and paste elements of the code but have no idea what if anything I can change to make this work.
The code is as follows
HTML Code:
Sub CopyData2()
application.ScreenUpdating = False
Call Sample ' this inputs data into column f
Dim rCell As Range
Dim lastRow As Long
[Code]....
I have a relatively complicated spreadsheet 'process' which involves a number of different people filling in Excel based progress reports, in similar formats (created from the same template), and then invoking a common "publish" VBA macro which opens a common "master" workbook and copies their sheet into the master one ( deleting any previous one of the same name). The master sheet thus contains around 30 similar sheets published from different people, plus a couple of summary sheets. Note I also then copy cells in order to get around the 255 character limit on worksheet copy
All works fine except this week it decided to raise the "Too Many Cell Formats" error when trying to open the master workbook. I didnt know about this error, but apparently there's a limit of around 4000 cell formats per workbook. Given that the individual sheets are similar, I presumed that they wouldnt all have their own cell formats. I dont know how to check the number of different cell formats (it's not the same as checking the number of styles) or how to condense them to avoid any limit.
Looking at the actual styles defined in the master workbook, it seems that styles are duplicated when sheets are copied - I have multiple "normal" and "hyperlink" styles. I dont know why, or how to avoid this.
So - any ideas on how to avoid multiple cell formats (or even styles) when doing a worksheet copy into a different workbook? It needs to be an automated solution (i.e. macro) - I'm not able to go in and perform manual operations every time someone publishes (i.e. copies in) a new worksheet
Is there a way for your linked data to cary over formatting such as Bolds, borders, fonts and colors?
View 3 Replies View RelatedI would like to display the following number of hours as day hour minute format
48.0 hrs as 2d 00h 00m.
When I try any command I get a message such as "too many different cell formats." Is my 1mb workbook simply too large and/or complex?
View 3 Replies View Relatedi'm having trouble formating a cell to accept either user input or a equation i've made. i would like it to accept the user input and when there is no input to use the equation i have.
View 8 Replies View RelatedI have a report which has list of outstanding invoices on Sheet1 and I enter invoice nos and Notes on to DATA sheet then by using Vlookup function I transfer the Notes to Sheet1 under "Notes Column K",
Some reason vlookup doesn't work because of the invoice number's format on Sheet1 doesn't match with the invoice numbers on the DATA sheet because they come from SQL server data base table,
So every time I refresh the Sheet1's data then I need to do Text To Colum on "Colum D" to make it work but this is not a solution because every time I refresh the Sheet1's data Vlookup formula doesn't work.
Is there a SQL formula that I can add to my SQL code which can automatically do Text to Column on the "Column D"?
In a macro, I am copying data from another sheet and most of the time I only require the actual data or the values as per below.
View 2 Replies View RelatedI do have a 2 tabs with certain sets of data which I need to compare. I have one tab called "NEW" and another one with "PREVIOUS" and unique values in column C in each of them.
I now look for a formular / macro which tries to find the value in cell C2 of the "NEW" tab in the "PREVIOUS" tab. In case there is a match, the cells of columns T to W (of the row where the value was found) should be copied from the "PREVIOUS" tab into the "NEW" tab just to the same place. When there is no success, "NEW PD" should be entered into cell T (of the respective row).
The lists are growing week by week. I can do an offset / match function which will import the plain data, but I cannot get the formats, too. I believe I will need a macro for that but I am too unexperienced to get this to work on my own.
I have a column of about 1000 records. In each field is a long integer, that has been obtained through a concatenation formula. I have copied the results to a new column and used "paste special, values" to do so. I now have the results in a new column, but the cell format is text and is giving me an error. Even if I pre-format the cells that I will paste into to be a number they do not stay as a number after being pasted into, they still have an error.
I need to run a vlookup on these results but because of the error in the cells the vlookup wont work until they have been changed to a number format. I am able to change the format of the cells to number, no deciaml places, one by one. I have about 1000 records and need to be able to change the cells to number format in one go, as it is too time consuming otherwise.
If I change the format of the cells all together in one go to a number, they do not change unless you double click in them and press enter one by one. Is there a way to change the cell format for all the cells in one go without this laborious process?
I need to change the date format in a cell which also contains text.
The full formula is as follows:-
="Report period:
"&TEXT(Control!B9,"dd mmmm yyyy")&" - "&TEXT(Control!B10,"dd mmmm yyyy")
This gives me the following:-
Report period:
01 October 2012 - 30 September 2013
What I would like to have is the following:-
Report period:
1st October 2012 - 30th september 2013
It's only a minor change but I think it looks better. Without a hideously complicated formula, if this is possible using one of the standard cell formats?
I understand that exceeding approx 4000 cell formats causes the message "Too many cell formats" and a freezing up Excel.
Are there versions of Excel (2007?, Vista?) for Windows XP that significantly increase the 4000 limit, and if there are, what are their limits on the number of cell formats?
I have got several different workbooks that I use that contain macros that format cells. However, just recently I have had countless problems with the macros telling Excel to format certain cells. There seems to be no logic or order to this. Also, if I then try to format the cells by hand, I get the following error message: "Too many cell formats"
View 3 Replies View RelatedIn a single cell, I would like to show 2 values: a currency and a percentage. So A1 and A2 has values, say 50000 and .10 respectively (these are derived via formula, if it's of any relevance). What I ultimately want is "$50,000 and 10%". I've originally thought about adding the "$", " and ", and "%" separately, but I need the currency value to include commas.
View 2 Replies View RelatedI've just recently ran into this Error (Runtime error 1004) "Too many different formats." I'll admit i have a ton of cell formats within my single worksheet.
perhaps most are for artistic purposes.
Still, my worksheet as it is causes this error, when running my scripts. When I wipe out all my format color, boarders, etc. my scripts run fine. Is there a maximum amount of cell formats that can be used? I'm using Excel 2003
Is there a way to circumvent this?. Not sure if using Excel 2007 is an option:
I've tried opening my project in 2007, but found that all my buttons were missing, plus it seemed to run considerably slower.
In the below link u will find each state driving license formats of ..example for alabama 7 Numeric..for ALASKA Up To 7 Digits..what am i looking is if we select a specific state and then insert any driving license of that state in next cell if the format matches i want that next cell to be true if not false ..wonder if we can create anything like this ..there are about 50 states with different license formats..
Can we write VBA for this .. [URL] .......
I am trying to find dates & time within text in a cell & return to a separate cell. The issue I have is that the date format varies frequently. I also can't rely on searching for "Sent:" as this also varies frequently
e.g. From: ########
Sent: 17 November 2011 11:57
I would like to return 17/11/2011 11:57
From: #######
Sent: 01/11/2011 11:50:13
I would like to return 01/11/2011 11:50
From: ########
Date: 05/11/2011 09:45:13
How can i search for various forms of dates and return them into a cell?
I have the below concatenate formula and it works for what I need..well, almost. I know I don't have the format cells option when using concatenate, but I need the answers to the formula to come out as a percentage or to two decimal places. I have searched and honestly don't understand how to do this in my formula....
View 6 Replies View RelatedI 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].....