I have to copy the formatting from "rows G&H" for 40,000+ lines. Is there an easier way than doing the formatting then highlighting the first two cells (G4 and H4) then dragging the bottom right hand corner down 40,000 lines?
View 5 RepliesIs there a way to copy the formatting of a cell automatically? For example, if you just do "=B1", then the cell with this formula will automatically update with whatever cell B1 contains whenever it is changed.
But is there a way to do the same thing with formatting? So if cell B1 was bolded, or changed the cell color, or conditional formatting, is there a way the cell that is referenced to B1 would also automatically change to whatever the formatting change is?
I am having trouble copying conditional formatting down my spreadsheet. When i copy it the number dont change. the formula i have in the conditional formatting is =O3
View 9 Replies View RelatedCell (A1) is formatted differently from the other cells of the same column. I added to it a conditional formatting (based on a formula) which I want to copy [alone] to the rest of the cells, without getting the other different formatting of cell (A1) copyed as well.
View 9 Replies View RelatedI use excel 2003. To improve readability, I have formatted each line to have a slightly different background color (white for odd ines, light gray for even). Sometimes when I edit, I use Control + D to fill down a column with the data or formula from the 1st cell. However, when I do that the background formatting is copied too and it messes up the alternating colored lines. Is the a way to fill down only the data/formulas and not the color/pattern?
View 3 Replies View RelatedI am working on a check box based list for work. The requirement is for the check box to be linked to the cell that it is in and then for conditional formatting to be done so that if the box is checked it goes green and if it is blank it goes red. This is fine for individual cells, but when i try and copy the formatting and cells throughout the worksheet it links all the cells back to the original cell.
View 7 Replies View RelatedI have a set of values on Sheet1. These 'original' values will never change.
I then have a set of values on Sheet2. The 'updated' values get updated every month.
On Sheet3 I want to show the updated values again, pretty much a copy of Sheet2, but where a value is higher than that on Sheet1 or lower than a value on Sheet1 I want to use conditional formatting to show this.
Is there a simple way to use conditional formatting in such a way? I have a long list of values so don't want to individually create a rule for each cell.
is it possible, after inserting new sheet rows, to automate the copying of formulas and cell formatting into the newly created space, instead of manually copying the formulas and formatting down into each column?
View 6 Replies View RelatedI have a piece of code that runs through various excel files and takes the data (minus the header) and pastes that into a separate workbook. The piece of code that does the actual copying i think is this (i have used code from the msdn website)
Code:
With sourceRange
Set destrange = destrange. _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
I have tried replacing the sourceRange.Value with sourceRange.Text however when i do that it will copy nothing any more.
i am trying to import an excel document and then copy over some columns to another worksheet from the imported worksheet, using a macro. Everything imports and copies over fine except for one column that has cells that comprise of a list of number separated by commas. When these cells get copied over some of the values retain the commas but some of them (specially when there are a lot values separated by comma) gets changed to this format
From: 1,229,124,012,441,230
To: 1.22912E+15
From: 1044,1048,1052,1053
To: 1.0441E+15
From: 1279,1282,1286,1295,1299
To: 1.27913E+19
From:926,929,938
To: 926929938
I am trying to figure out whats happening here! Is there a max number of letters this column can take and if it exceeds that value, excel converts them to this format? Or is the formatting gets changed to something else while copying and pasting?
I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.
Sample: =IF($C$5/$C$295>$R$5,TRUE,FALSE)
I want to copy to $D$5/$D$295>$R$5,TRUE,FALSE) without having to paste the new formula in each time.
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'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1
Test 2 32, 343, 63, 34 email2
Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1
Test 2 32 email2
Test 2 343 email2
Test 2 63 email2
Test 2 34 email2
Test 3 4934 email3
Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code:
Option Explicit
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
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 a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I'm using excel 2010.
Excel 2007.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager
Rule
Format
Applies to
Stop if true
[Code] ......
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
For example:
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
For example:
Conditional formatting rules manager
Rule
Format
Applies to
[Code] .......
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.
I have a macro which I have been successfully using for a while and it's served me well. But now I have far too much data on my source file (55-60k rows) and I only need data the data if Column C is "AB". I'm the first to admit that I know nothing about macros.
If anyone can tell me how to make this only copy the rows where Coulumn C is "AB".
Do While fn ""
If fn ThisWorkbook.Name Then
With Workbooks.Open(myDir & fn)
With .Sheets("3-Queues by Agent").Range("A1:R60000")
ThisWorkbook.Sheets(5).Range("a" & Rows.Count).End(xlUp)(2) _
.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
.Close False
End With
End If
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
I have a spreadsheet (see attached) that has city names, accessories sku number and quantity sold for each week. I run this report every week and when the spreadsheet has empty rows which I delete, but there are some rows that also have partial data that can be deleted. Anyway, I would like to copy name of first store (los Angeles) in this sample and copy down till the next city comes up (San Diego) and do the same all the way down. I have total of 20 stores and I wondering if you guys know of a formula or vba code that could help me with this.
View 9 Replies View RelatedFor example, I have an excel worksheet which has a main "master" list that has all of the cars that are sold along with the name of the salesperson who sold it all the way to the right (Column 6)
What i am trying to accomplish:
I would like excel to automatically take all of the rows which have the same salesperson name in column six and sort them into separate sheets, but i would also like these rows to automatically update if information is changed in them or if they are deleted on either the salespersons sheet or the master sheet.
How do I go about copying rows starting at A1 and counting down until there is a blank row in VBA?
I.e.
Row 1 - value
Row 2 - value
Etc etc
Then Row 13 is blank
I just want to copy all the data above the blank row, which I will then paste on another xls?
I have a workbook into which a number of rows will be imported (a variable number). These will be edited then I want all the rows to be stored in a ever growing database. At the moment I simply copy these rows open a second workbook and paste the rows into the first blank row in the second sheet.
Is there a way to do this automatically.
1) select and copy all rows containing data
2) open second workbook & find first blank row
3) Paste data into second workbook
4) Save and close second workbook
I think with some effort I could manage the copying and pasting but I have no idea how to open and close the second sheet automatically.
i am trying to copy rows from several worksheets into one master worksheet. if column F of any row has a value greater than zero then the rows should be copied to the master worksheet with all data.
View 5 Replies View RelatedI am trying to copy rows that contain a specific word from one worksheet to another.
View 9 Replies View RelatedI'm working on a project requiring the matching of data from multiple worksheets and got some useful advice from someone on here a few months ago to get it up and running.
At the moment I'm using SUMPRODUCT with an IF argument to check rows on one sheet for matches on 5 others and return a "Yes" or "No" value, which is working fine. The next bit of analysis however requires copying the value from another column on rows that match, and pasting it into the first sheet.
I was wondering if it is possible to copy a row without having the numbers ascend? I've attached an example spreadsheet and am trying to copy row 2, columns C - J to rows 3 - 16, but the numbers ascend when I do.
View 6 Replies View RelatedI have an excel sheet that I'm trying to format. Basically, the excel book contains sheets (with the months), and each sheet contains a list of clients, their package, their status, date assigned, and date updated.
I've made another sheet (Sheet2) that fetches everything and consolidating it to one sheet. Sheet 2 also cleans up the data to display only the items that are 'completed'.
The issue I'm having now is in splitting the list (assuming it has been cleaned up to show only the 'completed' items. I would need to split the list first by the date they were updated, and then if they were assigned the same date or not. In short:
--Get all items with date updated = Month1
----If date assigned = Month 1: List items under table 1
----If date assigned < Month 1: List items under table 2
I've used macro on the excel (and I'm not sure it's efficient enough. I'm not proficient with VB, so...). I'm not sure if it will work on Mac.
I have a sheet with a lot of different data. In column M in workbook "Maximo report" there are 5 different values. I want to copy the rows that contain CDC_HVAC and that have the value "INPRG" in column G to the sheet HVAC_INPRG. I want to do this for all 5 values. Once the rows are in their respective sheets I want to filter them based on a formula and then sort from oldest to newest. I dont want the file to be too big and I think that VBA is the way to do it. I am new to VBA
View 6 Replies View RelatedI have a spreadsheet with various details sorted by date and i am trying to create a macro that will run whenever the workbook is opened and search the column "Renewal Date" for a date between now and 3 months time. For all rows with a date that meet this criteria it will copy the relevant rows and paste them to a new worksheet with the header. The renewal date column could be added to so i need the macro to run until it meets an empty cell. I have attached an example workbook with cutdown data and my attempt at the macro.
View 4 Replies View RelatedWhat I'd like to do is to have the block of rows starting from "Agent:" and ending with "Total" copied into a separate worksheet and then have the worksheet named using the agent's name. I have highlighted the data in yellow in the attached reference file. I would like to have this done for all 58 agents. I could copy and paste manually but that wouldn't be the smartest way to go about this. I figure I probably need a looping structure for this but I am not yet proficient enough to figure this out quickly. I am supposed to have this done today.
Edit: The last part of this would be to have blocks of rows from the "sections" worksheet which use identical agent names also pasted below the data from the "questions" worksheet. I have attached the workbook I am working on now cutting and pasting manually.