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 RepliesI 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 have a spreadsheet for recording property maintenance. All properties are grouped by a city zone in a specific colour, and there is conditional formatting on this. Also drop down lists for properties, contractors etc.
This all worked well, until I needed to start inserting rows. All the conditional formatting and lists didn't replicate on the newly inserted rows.
Also, we need to assign a unique reference number to each row but currently manually inputting them - very inefficient way of doing this as they will inevitably get mixed up and confused as rows are removed.
Spreadsheet.jpg
I have created a multiple choice quiz maker that randomizes the questions and responses. This means that the quiz questions/responses must be copied and pasted each time a new quiz is generated. But in doing so, Excel loses formatting (such as super and sub scripting) and some special characters (like pi, alpha, the degree symbol).
Is there a way to get such things to copy properly from cell to cell, sheet to sheet, using Excel?
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 made a spreadsheet and used pivot tables to summarise the data by project numbers (Row field of pivot table). I deleted the orginal data and copied in data from another spreadsheet. When I look at the detail in row field (click on the down arrow of the project button in the row field of the pivot table) it still shows the original project numbers plus the new project numbers. The data displayed in the pivot table looks like it only refers to the new projects but is not correct. Any ideas why the old project numbers still appear? Refreshing the data doesn't help. The only way I can get the correct data is to delete the pivot table and then recreate it.
View 4 Replies View RelatedIn Excel 2007, is it possible to copy & paste a pivot table, and have the result look like a pivot table, but not actually be a pivot table? I want to keep the values and the formatting (the colors and borders, etc) but I want it to not actually be linked to the data or have the ability to change with dropdowns, etc. I've tried the usual copy & paste special (values) thing, and the other otions in the paste special box, but it doesn't keep the formatting.
View 10 Replies View RelatedIn excel 2010, I'm using the following to copy and paste values and formatting from a pivot table, but i lose the formatting (TableStyle2 = "PivotStyleLight8"):
VB:
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I have tried to add, xlPasteFormats, but to no avail...?
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 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?
How to change these defaults...
It involved opening a new workbook and changing the number of worksheets in a new workbook, cell sizes, font, and font sizes... and then saving the workbook in one of the system folders under the name "workbook" to change the default workbook attributes, or "worksheet" to change default worksheet attributes.
i want to paste a worksheet with amended cell sizes and also some clipart pasted...to another workbook in same style(excel 2007). when i do that, it says 'data on clipboard may not be of same size.......' and paste in different size.
View 1 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?
I have a form made that needs to copy a value from another sheet. The Form is 10 rows 5 columns and and in format for easy print. On sheet2 I have a column where each row is filled with a name.
I need to put this name into sheet1, so in the the appropriate cell I put =Sheet2!B2 and it gives me the value (name) from the other sheet.
Then I want to copy the form bellow the 1st one so that I have the same form but with the next name, which means I want =Sheet2!B3 to appear when I copy, but since my form is 10 rows when I copy it the formula copies to =Sheet2!B12 instead of B3.
I need to make close to a thousand of this forms ready for printing and I would like to avoid having to manually set the formula for the next cell.
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've created a group of user forms used for recording jobs and displaying details of jobs and have come up against a problem I think you may be able to help me with.
When the user creates a new record, I've written some code to stamp txtdate text box with the current system date and this information is then copied onto a sheet called Data into a specific column.
This part works fine and as the column is formatted to handle dates it displays fine.
However, when I pull the data back in, it displays the date as the serial number date, not formatted as dd/mm/yy. I've tried every fix I can find to sort this but none of them seem to work.
The code I use to pull the data in is as follows:
ComboBox1.BoundColumn = 11
txtdate.Value = ComboBox1.Value
The idea is that the user selects a job number from combobox1 and then the appropriate column is pulled into each txt box on the form.
At the same time, I'm trying to do the same for the system time as well. Unsurprisingly I have a very similar problem here and use the same code as above. I've tried formatting both date and time txt boxes in the userform, the data transfer code etc etc with no success.
I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedI am trying to copy a column of cells from one sheet to another, but also want to keep all the formatting. The origin sheet has times, but when I copy these to the destination sheet they are displayed as decimal numbers (using the code snippet below). I can change these back to times by formatting the cells using the format painter after the macro completes but I would like the VBA to do this for me. (using 2002 SP3).
View 4 Replies View RelatedI'm basically trying to copy the text and formatting (colour fill) from a single cell into all cells I select. So far I managed to get the auto fill to work but the text only copys to the first cell I select of the selection.
Here's the macro I have:
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+f
'
ActiveCell.FormulaR1C1 = "1 Completed"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Is there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values? As in this for example: Copy this value and formatting
I would like to keep the formatting, (color and bold), but I have not figured out how to do this. I read a couple other posts but could only copy the values but not get the formatting to also copy over.
[URL] .....
[Code] .....
If I wanted to copy a cells formatting and content (Text) to another cell on a separate sheet, what code would I have to embed? I am a fresh neuling, so give it to me straight.
I have attached an example to this post.
I want to transfer the red and bold attributes of the words within the ( ) in sheet 1 to the cells in sheet 2 that I have linked to the cells in sheet 1 with =.
I am having a problem with a excel spreadsheet. There are no formulas or anything, just text that I used excel to get everything lined up. I haven't had any issues before, I was copying and pasting within a document. It was all text with different row heights set. I ran into a problem now where the text copies and pastes fine but the row heights aren't coping into the cells I am pasting into, just the text. What do I have to do so they will copy? I don't want to have to go an individually adjust each cell.
View 2 Replies View RelatedI 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.
Here is what I am trying to do in lay terms:
If A2 and B2 on Sheet("Master") are not highlighted, copy and paste them into Cell A3 and B3 on Sheet("Job List - Numeric"). Do this up to Row 50 on Sheet("Job List - Numeric"). Once Row 50 is filled, jump to pasting to D3 and E3 on Sheet("Job List - Numeric") and continue up to Row 50 and so on....
I need to do this on one sheet in numerical order based upon Column A and another sheet in alphabetical order based upon Column B.
Here is what I have in code so far:
Sub Final()
Application.ScreenUpdating = False
Dim c As Integer
Dim x As Integer
For Row = 2 To 145
Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 1)).Select
[Code] .......
I'm trying to find the last column of my table that will change from week to week and copy the contents of that cell and the one beneath it and paste them into the two cells to the right of them.
Here's what I've tried:
Sheets("LY - TY Chart - Tot. Sell $").Select
Dim lastTableColLY As Long
Dim lastTableColTY As Long
Dim nextTableColLY As Long
Dim nextTableColTY As Long
lastTableColLY = Cells(109, Columns.Count).End(xlToLeft).Column
lastTableColTY = Cells(110, Columns.Count).End(xlToLeft).Column
Range.Cells(lastTableColLY, lastTableColTY).Select.Copy
Range.Cells(nextTableColLY).Select.Paste
I'm getting the following error:
Compile error: Argument not optional at the
Range.Cells(lastTableColLY, lastTableColTY).Select.Copy
i'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 am building up a formatted invoice, line by line all of which is working well from the data point of view but no quite so well from the presentation point.
My text is all in Arial 12pt and I have the row height set to "Auto" and cell alignment to wrap text (at least one cell per row often has 3-4 lines of text so I need "text wrap" on)
My issue is that the printed output from a multi row invoice looks "crowded" and as this is the view the client gets to see (and hopefully pay ) the look is important. I tried to set the row heights to say .71cm but that causes the text wrap to stop working correctly.
I think I am trying to get some form of "inside top" and "inside bottom" adjustment / fill / padding (which is what I would do if I was using some form of documentation tool like Madcap Flare for example) but I cannot see how to do it in excel.
Vitamin B1 capsules come in three sizes - 100mg, 500mg and 1000mg
Our doctor prescribes dosages from 100mg up to 5000mg a day
I want to update our stock of tablets for each dosage level.
For example 600mg is 1 x 500mg capsule + 1 x 100mg capsule 0 1000mg capsules,
4900mg = 4 x 1000mg caps + 1 x 500mg + 4 x 100mg
We give out a kit which has in total 100 x 100mg capsules,60 x 500mg and 75 x 1000mg capsules
I want a set of formulas to calculate the nubers of each tablet at the daily dosage level between 100mg and 5000mg
Cell A1 = Daily dosage in mg
Cells A2 = number of 100mg capsules in that daily dosage - what is the formula ?
Cell a3 = number of 500mg capsules in that daily dosage - what is the formula ?
Cell a4 = number of 500mg capsules in that daily dosage - what is the formula ?
can buy stock glass in several standard sizes. Specifically:
12x16
16x20
20x24
22x28
24x30
24x36
26x32
32x40
36x48
40x60
I use these stock sizes - to cut custom sized pieces. Example - if I have a piece that calls for a 15x18 - I will take a 16x20 piece and cut it down. I have all different kinds of size combinations that come up and am looking for a way for Excel to calculate the most efficient stock size to start with. Trying to do so using a series of Vlookups and if then's - but seems to be overly complicated and not always accurate.
I have a report with graphs and slicers and I have a 22 inch monitor. All of the graphs and slicers fit on my screen size but when others open it everything doesn't fit because they have a smaller monitor. Is there a way to auto adjust the report to adjust to individual screen sizes. I want the data from A36-V36 to fit on the screen at the same time. Not sure if this is possible though.
View 5 Replies View Related