Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Conditional Copy-Paste for Stock Trading project

Hi everyone,
I'm brand new to programming in Excel, and currently building a system that will help me trade equity options (similar to trading stocks). So far, I've put together the ranking system I want to use based on a live data feed that updates in real-time (during market hours). For every possible trade in my 'universe' (currently about 600), Excel provides a live score (based on my formulas and weightings) as well as prices and other info about the trade (options strikes, symbols, expected return, etc.).

What I'd like Excel to be able to do is keep a separate sheet where a trade (row) that scores over an arbitrary threshold, say, 100 'points,' is recorded as a static line (instead of continuing to dynamically update), like a snapshot of that line. I'll use this list of trades to submit to my broker for trading.
If it was as simple as a 'triggered' copy - paste special - values, that would do just fine. The trigger would also need to see if that trade has already been recorded for the day, so I don't get an infinite list of the same trade.

After a trade makes it into this list, I'll need to compare it to what I already have in my portfolio (which I'll also need to build) so I don't become too heavily weighted in any one trade. (Just thought I'd let you know where this will be going, too).

I'm sure I left out some important details, so please ask away! Thanks for your help!

-Tony


Post your answer or comment

comments powered by Disqus
Hi all,

After two weeks of developing and testing, my macro is almost ready! There’s just one small component left that I can’t seem to figure out. It involves conditional copy-pasting. Here are some more details:

The macro should search column A starting with A2. Each time a cell in column A contains the string “Status,” it should:
1. Move down 1 cell
2. Move 10 cells to the right
3. Copy that cell
4. Move 10 cells left, 2 cells up (1 above “status”)
5. Paste
6. Move down 2 cells so that it can continue searching the column for “Status”

Any ideas or suggestions are much appreciated!

Hi,
I need to make a macro that copies selective info from one sheet and pastes it in multiple sets in another worksheet. Here's what my main table looks like (column headings):

<BPC#> <MGL#> <COG ID> <Patient Initials (L,F)> <Specimen Type> <7 columns not shown> <Code>

The code given to each specimen--1, 2 or 3--will dictate how the specimen data is treated. I need to record a macro to make tube labels by taking the COG ID, Patient Initials and Specimen Type and pasting it in the separate "Labels" sheet. It should paste 10 sets for the samples coded with a "1" and 8 labels for the samples coded with a "2" or"3." Essentially, this is what I need to know:
1) conditionally copy and paste based on the code
2) paste the data a certain number of times based on the code
3) paste the data in the next blank cell and only in blank cells

I have tried different things that haven't worked and I'm getting really desperate! Please help!!

Thanks in advance,
Jenny

I am completely illiterate in visual basic, and my boss wants me to help him with some excel macros.. I have some history in programming Java and C, but I still need someone to help me with this. Please break this down for me, as if I was a 2 year old.
I need a conditional copy and paste macro (I'm not sure it even needs to be a macro, we just need to add functionality into an excel sheet). What I need to happen is that when you hit this button on a sheet, it checks a certain cell on the sheet. If this cell has a value greater than "0", copy and paste all the cells in range from "aa" to "bb", and paste them into a second sheet. Then move onto a different cell from the first sheet, and if that cell has a value greater than "0", copy and past all cells from range "yy" to "zz", and paste them on the second sheet, below the earlier paste. This needs to be repeated to check a certain number of cells.
Any help is GREATLY appreciated.
~Adam D.

Can someone help with this please? I need a macro to compile a statement consisting of details from multiple invoices.

Idea for method.
Find & copy 5 cell values from each sheet in the workbook that match two conditions, then paste them into the end sheet.

Full instructions included on attached workbook.

Hello,

Could you please help me out with an issue i have been trying to get around for the past day? I am unfortunately completely new to macro writing, so i am struggling a fair amount.
I have a file with 5 columns. one with date, time, latitude, longitude and amplitude.
I need to conditionally select data from the latitude and longitude columns and paste them into a new workbook, with a new file name.

The file has a total of 32001 rows, including the title rows, however the attached file has fewer rows so that i could upload the document. Also note, that it is possible that neither of the conditions are satisfied, for which nothing till be pasted into the new workbook.

The conditions that i need are the latitude values should be within -37.9382 and -32.004 and the longitude values should be within 136.7754 and 141.0698. all rows which do not satisfy this should be deleted.

i am completely new to macro excel writing and i am completely stuck.
I have attached the excel file that i am trying to work with.

Extremely looking forward to your reply,
Kind Regards, Trini100

I'm trying to copy paste from one worksheet to another via VBA. My first
attempt resulted in an error as soon as a blank cell was encountered. So I
came up with the following code. The For...Next and If...Then work fine -
the problem is with the copy/paste lines. I'd appreciate the help (and a
little explaination so I don't gum it up again the next time). Here's the
<snipped> code:

myAddresses = Array("A12:A35,A37:C48,L12:O48,L49:M56,L58:L59")
For RangeCounter = LBound(myAddresses) To UBound(myAddresses)
Set BCRange = Nothing
On Error Resume Next
Set BCRange = wsh1.Range(myAddresses(RangeCounter))
For Each cell In BCRange.Cells
If cell.HasFormula = True Then
wsh1.Range(myAddresses(RangeCounter)).Range(cell).Copy
NewWbk.Worksheets("Basic Components Pricing").Activate
wsh2.Range(myAddresses(RangeCounter)).PasteSpecial _
xlPasteFormulas
ElseIf cell.Value > 0 Then
wsh1.Range(myAddresses(RangeCounter)).Range(cell).Copy
NewWbk.Worksheets("Basic Components Pricing").Activate
wsh2.Range(myAddresses(RangeCounter)).Range
_(cell).PasteSpecial xlPasteValues
Else
End If
Next cell
Next RangeCounter
--
rpw

I've got an app that uses sheet protection to prevent addition of columns or rows, and protects cells with formulas but leaves other cells open to enter data. I'm also using a worksheet_change event handler to do things based on changes, such as formatting, setting cells protected, programatically creating formulas, etc.

The problem I'm having is that when a user copies a range of cells (from this or another workbook) and tries to paste them into an unprotected area of the worksheet, an error occurs and the values are deleted.

I'm using the 'target.value' element to check to see if the value is numeric, but target.value becomes an array when multiple cells are selected. I first do an undo and capture target.value (the old value), then do another undo to reset it to the new value. When target is an array, value becomes empty because it is really multiple values underneath this element. (am I making sense?).

Anyone have a suggestion as to how to get around this and allow multiple copy/paste for unprotected cells?

Hello all,
What I am trying to do is automate a series of select copy and select paste satements based off a cell value. The cell value that I am refrencing is months(Jan, Feb, Mar,etc...) I would like to have a button that when pressed looks at the cell B2's value then looks at the case for the appropriate value and performs the corresponding select, paste routines. I am not sure if the procedure I am using to perform this routine is the best method. Any help that any one could provide would be greatly apprciated. Thanks in advance

Bob

I won't post all the code because it is repeated for each month I will just include Jan through Mar. That way I won't exceed the character limit.

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' This routine was written by Bob Kunkle 8/28/08 for the Windsor Plant                                             '
' What is being accomplished is an automation of copy paste for months end to the apporpriate department sheets    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub copy_paste()
Dim target As Range

If Not Intersect(target, Range("B2")) Is Nothing Then
Application.EnableEvents = False
Select Case LCase(target.Value)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' Start Jan Logic                                                                                                  '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Case "Jan"
   Sheet5.Range("b8:b22").copy
   Sheet13.Select
   Sheet13.Range("b2").Select
   Sheet13.Paste
   '
   Sheet5.Range("c8:c22").copy
   Sheet14.Select
   Sheet14.Range("b2").Select
   Sheet14.Paste
   '
   Sheet5.Range("d8:d22").copy
   Sheet15.Select
   Sheet15.Range("b2").Select
   Sheet15.Paste
   '
   Sheet5.Range("e8:e22").copy
   Sheet16.Select
   Sheet16.Range("b2").Select
   Sheet16.Paste
   '
   Sheet5.Range("f8:f22").copy
   Sheet17.Select
   Sheet17.Range("b2").Select
   Sheet17.Paste
   '
   Sheet5.Range("g8:g22").copy
   Sheet18.Select
   Sheet18.Range("b2").Select
   Sheet18.Paste
   '
   Sheet5.Range("h8:h22").copy
   Sheet19.Select
   Sheet19.Range("b2").Select
   Sheet19.Paste
   '
   Sheet5.Range("i8:i22").copy
   Sheet20.Select
   Sheet20.Range("b2").Select
   Sheet20.Paste
   '
   Sheet5.Range("j8:j22").copy
   Sheet21.Select
   Sheet21.Range("b2").Select
   Sheet21.Paste
   '
   Sheet5.Range("k8:k22").copy
   Sheet22.Select
   Sheet22.Range("b2").Select
   Sheet22.Paste
   '
   Sheet5.Range("l8:l22").copy
   Sheet23.Select
   Sheet23.Range("b2").Select
   Sheet23.Paste
   '
   Sheet5.Range("m8:m22").copy
   Sheet24.Select
   Sheet24.Range("b2").Select
   Sheet24.Paste
   '
   Sheet5.Range("o8:o22").copy
   Sheet27.Select
   Sheet27.Range("b2").Select
   Sheet27.Paste
  
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' End Jan logic                                                                                                    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' Start Feb Logic                                                                                                  '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Case "Feb"
   Sheet5.Range("b8:b22").copy
   Sheet13.Select
   Sheet13.Range("c2").Select
   Sheet13.Paste
   '
   Sheet5.Range("c8:c22").copy
   Sheet14.Select
   Sheet14.Range("c2").Select
   Sheet14.Paste
   '
   Sheet5.Range("d8:d22").copy
   Sheet15.Select
   Sheet15.Range("c2").Select
   Sheet15.Paste
   '
   Sheet5.Range("e8:e22").copy
   Sheet16.Select
   Sheet16.Range("c2").Select
   Sheet16.Paste
   '
   Sheet5.Range("f8:f22").copy
   Sheet17.Select
   Sheet17.Range("c2").Select
   Sheet17.Paste
   '
   Sheet5.Range("g8:g22").copy
   Sheet18.Select
   Sheet18.Range("c2").Select
   Sheet18.Paste
   '
   Sheet5.Range("h8:h22").copy
   Sheet19.Select
   Sheet19.Range("c2").Select
   Sheet19.Paste
   '
   Sheet5.Range("i8:i22").copy
   Sheet20.Select
   Sheet20.Range("c2").Select
   Sheet20.Paste
   '
   Sheet5.Range("j8:j22").copy
   Sheet21.Select
   Sheet21.Range("c2").Select
   Sheet21.Paste
   '
   Sheet5.Range("k8:k22").copy
   Sheet22.Select
   Sheet22.Range("c2").Select
   Sheet22.Paste
   '
   Sheet5.Range("l8:l22").copy
   Sheet23.Select
   Sheet23.Range("c2").Select
   Sheet23.Paste
   '
   Sheet5.Range("m8:m22").copy
   Sheet24.Select
   Sheet24.Range("c2").Select
   Sheet24.Paste
   '
   Sheet5.Range("o8:o22").copy
   Sheet27.Select
   Sheet27.Range("c2").Select
   Sheet27.Paste
  
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' End Feb logic                                                                                                    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' Start Mar Logic                                                                                                  '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Case "Mar"
   Sheet5.Range("b8:b22").copy
   Sheet13.Select
   Sheet13.Range("d2").Select
   Sheet13.Paste
   '
   Sheet5.Range("c8:c22").copy
   Sheet14.Select
   Sheet14.Range("d2").Select
   Sheet14.Paste
   '
   Sheet5.Range("d8:d22").copy
   Sheet15.Select
   Sheet15.Range("d2").Select
   Sheet15.Paste
   '
   Sheet5.Range("e8:e22").copy
   Sheet16.Select
   Sheet16.Range("d2").Select
   Sheet16.Paste
   '
   Sheet5.Range("f8:f22").copy
   Sheet17.Select
   Sheet17.Range("d2").Select
   Sheet17.Paste
   '
   Sheet5.Range("g8:g22").copy
   Sheet18.Select
   Sheet18.Range("d2").Select
   Sheet18.Paste
   '
   Sheet5.Range("h8:h22").copy
   Sheet19.Select
   Sheet19.Range("d2").Select
   Sheet19.Paste
   '
   Sheet5.Range("i8:i22").copy
   Sheet20.Select
   Sheet20.Range("d2").Select
   Sheet20.Paste
   '
   Sheet5.Range("j8:j22").copy
   Sheet21.Select
   Sheet21.Range("d2").Select
   Sheet21.Paste
   '
   Sheet5.Range("k8:k22").copy
   Sheet22.Select
   Sheet22.Range("d2").Select
   Sheet22.Paste
   '
   Sheet5.Range("l8:l22").copy
   Sheet23.Select
   Sheet23.Range("d2").Select
   Sheet23.Paste
   '
   Sheet5.Range("m8:m22").copy
   Sheet24.Select
   Sheet24.Range("d2").Select
   Sheet24.Paste
   '
   Sheet5.Range("o8:o22").copy
   Sheet27.Select
   Sheet27.Range("d2").Select
   Sheet27.Paste
  
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' End Mar logic                                                                                                    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    
End Select
End If
Application.EnableEvents = True

End Sub


Ozgrid,

Long-time lurker, first-time poster...

It has been 2 years outside of any real VBA and I'm not having any luck with my usual "chopping" code from forums approach.

Background:
I have three factory sites that provide a forecast of workforce data (supply and demand) by week (x-axis) for every position (y-axis). Note: Workforce is interchangeable and have assigned common categories/positions across sites.

Problem: (Reference attached for worksheets)
Alls well and good until a site's supply and demand don't match, so i'm trying to solve the problem of surpluses and shortfalls in workforce. On the worksheet.hide I have created two sets of tables, one set to display gaps within an individual site's supply and demand, as well as another set of tables to cross reference the origin site's shortfalls against the other site's surpluses (i.e., identify where a sites surplus of workforce can augment another sites shortfall).

First, I am developing a UserForm to request the following input variables: origin site, range to dates (mm/dd/yyyy to mm/dd/yyyy), sites to be cross-referenced, and workforce positions/categories to be looked at.

Next, Based on the inputs given from the origin site (i.e., "what rage of dates do I want to look at", "what sites to I want to cross reference against", and "what positions/categories do I want referenced"), I want to go thru the following logic:

1a) Based on the "Origin" and "Positions" inputs from worksheet.Inputs (e.g., "Site One" and "2", "28", and "46")
1b) Find, Copy, and Paste rows from the origin's table/range ("siteone" range A2:BT75) to "worksheet.Report" based on the condition that that row meets the following positions (i.e., "position" column = 2, 28, or 46)

2a) Based on the "References" and "Positions" inputs from worksheet.Inputs (e.g., "SiteOne:SiteTwo", "SiteOne:SiteThree" and "2", "28", and "46")
2b) Find, Copy, and Paste rows from each of the reference's table/range ("onetotwo" range A228:BT301, etc.) to "worksheet.Report" based on the condition that that row meets the following positions (i.e., "position" column = 2, 28, or 46)

3) Sort the copied rows in worksheet.Report based by position, then copy/paste the table header from worksheet.Hide to worksheet.Report.

4) Delete columns whose headers date ranges fall before the begin date and after the end date provided in worksheet.Inputs

I know there are a lot of variables floating around here but if y'all could help me out with some basic find copy paste coding it'd be greatly appreciated.

Thanks guys!

-tim

Waterfront Manning - Help.xlsm

x post to Excel Forum - http://www.excelforum.com/excel-prog...94#post2844094

Hi all,

I was wondering if anybody could help me out with this head-scratcher. I have 9 worksheets in a book with sheets 1-8 used for data entry, and sheet 9 used for a weekly data upload. Sheets 1-8 are all formatted the same, they just represent different vendors. I need a macro to look through column F (invoice number) on sheets 1-8 and see if the value of each cell already appears in column c on sheet 9. If the value is not found and the row value for column M (sheets 1-8) is >0, I need to copy the data from columns D:K and paste special values into the next blank row on sheet 9.

Basically I'm trying to see if I've already paid an invoice, and if not then I need to automatically add the data for payment. I've tried to mess around with a VBA code myself and ended up with a migraine every time. I've also tried to use the advanced filter, but the cell values I need to copy over contain formulas and conditional formatting...not to mention I can't delete the data on sheet 9 that has already been added.

Any help or advice on how to pull this off would be greatly appreciated.

Thanks,

Micah

I am setting conditional formatting on a rather large spreadsheet for sum
values by month.

Cell A1 is the Resired resource request (data input)
Cell A2 =Sum(A3:A5)
Cell A3 through A5 are data input fields.

I can set conditional formatting to change fill based on greater than/less
than. What I cannot do is perform a bulk operation that will change my
conditional value for columns B through AH. Copy/paste special...formats
will add the conditional format, but KEEP the original reference.

Any way to change the in the same fashion as a traditional formula does when
you copy/paste?

Cheers....

I have a workbook that uses conditional formatting (cell color) based on
values in named ranges. Now I need to copy one of the sheets out of the
workbook for a user. I can't provide the whole workbook (even with that data
hidden) because data in other parts of the workbook are confidential.

I tried copy/paste that sheet to a new workbook (thinking I could just not
update the links) but the colors in te cells of the destination workbook
only maintain their coloration while the old workbook is open (and therefore
the named ranges are available for evaluation). Once the other workbook
closes, the conditional formatting can no longer access those named ranges
and they lose their color.

Is there a way to copy/paste a worksheet while retaining the coloration,
similar to [paste special/values] for cell contents? Ultimately I need
something like [paste special/current color] which doesn't exist, but
hopefully that helps explain what I'm looking for.

Any ideas?

Thanks,
Keith

How can I set up a "conditional" copy and paste procedure?

I need to copy certain values from a column and paste them into another
depending on the date (that is settled in another column) associated to them.

Example:

ColumnA ColumnB ColumnC
Date Delay(nÂş of days) Where to paste

13-01-06 15 ........
22-02-05 20 ........
24-01-06 30 ........

So..., I need to copy the values in Column B to the Column C if the
corresponding values in Column A are greater than 01-01-2006 for example?

Taking into account that I have an huge list in which I'm adding new rows
every now an then, I would like to find a way that every time I added a new
row, depending on the values in column A it would copy the values in column B
and paste them to the column C.

I could really use some help!

Many Thanx!

Hi,

I have created a file with several sheets, one for each month of the year.
Each sheet has a database table with several records....what I am trying to do is to find a routine able to check, for each filled up record, if the cell related to one specific Column is either empty or not. In fact, the absence of data in that specific cell of each row, has an important meaning. In case that cell of the row, corresponding to that specific column is empty, the routine itself should copy the whole record and paste it on the first empty record row of the next month sheet, keeping each formatting setup.

How can I manage that?

I am really looking forward to receiving your help, thanks

Mark

I am setting conditional formatting on a rather large spreadsheet for sum
values by month.

Cell A1 is the Resired resource request (data input)
Cell A2 =Sum(A3:A5)
Cell A3 through A5 are data input fields.

I can set conditional formatting to change fill based on greater than/less
than. What I cannot do is perform a bulk operation that will change my
conditional value for columns B through AH. Copy/paste special...formats
will add the conditional format, but KEEP the original reference.

Any way to change the in the same fashion as a traditional formula does when
you copy/paste?

Cheers....

Hi there

Firstly, I'll give an image example of my worksheet:

http://i29.tinypic.com/s2u0p0.jpg

The cell that is coloured with a red fill has three conditional statments in it, which are:

If highlighted cell value is greater than C2 then fill is green
If highlighted cell value is less than C2 then fill is red
If highlighted cell value is equal to C2 then fill is blank

So basically if "6" in this case is lower than "9" then fill the cell red, which it has done. But how do I copy this conditional format to all the other cells, ie: D2, F3, H2, B2, D3 and so forth (the first number of every group on every row)... when I tried to copy + paste the cell to another one I thought that Excel would automatically change the value itself of the number it was look at, but it doesn't and I copy it, it looks like this:

http://i26.tinypic.com/a3h9jb.jpg

Which is wrong because each of the copied cells is still using C2 for it's original value, instead of the value of the cell to the right of it, so looking like this would be correct (I just manually coloured all the cells for the example):

http://i27.tinypic.com/eqxyps.jpg

Is there an easy way to do this, or do I have to manually create hree conditional format rules for every single cell? (which would be very harsh, because the screenshots are just examples and the proper workbook has hundreds of columns)

Cheers!

The title of this post may be a little misleading, but hear me out.

I have a LOT of rules for my conditional formatting... and 12 "Sheets" (Jan-Dec)
How do i copy/paste ALL the conditional formatting from "January" and paste it into "February-December"?

OR

How do i shorten this list of formatting rules? is there any way to combine any of them?

1. =$N2="CSS" (Turns columns K and L green)
2. =$N2="CSS Sigs" (Turns columns K and L green)
3. =$N2="Rater" (Turns columns K and L green)
4. =$N2="Rater Rater" (Turns columns K and L green)

i know it seems redundant... but there are a lot of checkpoints for presentations we have, and once they hit a certian point, we still need them to show "good" (green)

is there a way to combine those 4?

Would be very grateful for some assistance coding the following:

1) Begin in cell A4
2) Loop through column A until a blank cell is encountered.
3) Copy the cell directly above the blank cell and paste the contents to the cell that is down 1 row (the row with the encountered blank space) and to the right 5 columns (col J).
(ie. condition for copy/paste to take place is: encountered cell is blank and cell immediately above has text)
4) Return to col A and continue with the loop until the end of the data is reached.
5) Delete columns A and B.

I have attached an example file with a "before macro" tab and a "after macro" tab for additional clarity.

Hi all,

I have found a few threads which show how to cut/copy & paste with set conditions or filters - this i can manage.
I am having a real hair puller of a prob with (See attached example attached)
Sheet 'ALLDATA' I want to copy last 200 rows of data meeting criteria;
- Col DIA = 12
- Col EG = EG2
Then paste into sheet 'EG2'.

The prob is only selecting the last 200 rows (or up to 200 max in some cases there is less) results.

Thanks in advance for any help

Hi,

I'm really struggling with Excel 2007 conditional formatting - I like the new features, but am very frustrated with how it is applied.

In earlier versions of Excel, I could select a single cell (or subgroup of cells) within a range that had conditional formatting, and remove it from that single cell. But it seems in Excel 2007 that if I remove conditional formatting from that one cell, it deletes it from the entire range as well.

For example, just say range A1:D10 has conditional formatting that colors the cell fill green if the value is 1. And then if I select cell C3 and try to remove the conditional formatting from that one cell by deleting the rule, the conditional formatting is removed from the entire range A1:D10, not just C3. I can get around this by going into the Applies To field and changing the range by manually selecting 4 areas surrounding that one cell. But what if I have multiple rules, or multiple non-contiguous cells from which I want to remove the conditional formatting - in earlier versions I could simply select the cells and delete the conditional formatting rules from them, but the process in Excel 2007 seems so much more complex.

And a related problem - just say I have complex conditional formatting applied to the same range (A1:D10) involving 5 rules. If I want to copy this formatting and paste it to another range (eg F1:G10) it does so. But if I want to make a slight change to the conditional formatting in the new range (F1:G10) like changing one number in the conditional format formula, the change affects both the original range and the new range. If I want the minor change to apply only to the new range, I have to change the Applies To range of the original range, and add the 5 rules manually again to the new range.

This is driving me crazy. Am I missing something? Is there a way to copy, paste and edit conditional formatting in a similar way to previous versions of Excel?

Tim

I wrote a code to search column H of every row from the last populated row and copy the cell only if the contents were not blank and then paste those contents in a running cumulative into another worksheet. Here is the code:


	VB:
	
 Warehouse() 
    Sheet3.Select 
     'Find last row of data
    FinalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row 
     'Loop through each row
    For x = 2 To FinalRow 
         'Decide if to copy based on column H
        ThisValue = Cells(x, 8).Value 
        If ThisValue  "" Then 
            Cells(x, 8).Copy 
            Sheet2.Select 
            NextRow = Cells(Application.Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheet3.Select 
        End If 
    Next x 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the Macro nothing happens...I'm lost as to what's not working here. Thanks for helping me with my ignorance!

Justin

Hello VBA Wizards,

The title pretty much explains it. I currently have a macro (admittedly poorly written) that can copy/paste values and save the results as a .xls file from a single file. However, I need to be able to run a single macro that does the same (copy/paste all values [not formulas] from a .xlsx workbook into a .xls workbook) for all the workbooks (roughly 66 per folder) across many folders (roughly 73) all within the same top-level folder. Any help would be appreciated. Thanks!!

Hi guys,

I am hoping that someone might be able to help me out here.

I am trying to copy and paste a range on multiple criteria.

Conditional copy warrant
Find Sheets(Database).Range(A1).value in Sheets(Assumptions).Range(G3:G31) and if the value is found copy the range(Nxx:AHxx) where x is row number of the found value. lets say I was finding A and it was found in G5 then copy the range N5:AH5

Conditional Paste Warrant

now in Sheets (Database), if the Sheets(Database).Range(A1).value = Sheets(Assumptions).Range(G3:G31) and column D value is true then paste the copied range 21 cells above as value and transpose.

the macro have to run the loop to find the values in sheets (Database).Range(A1:A10000)

Hope it makes sense?

Kind Regards and million thanks in advance

Regards,

Same query has also been made on MrExcel, please see link below

http://www.mrexcel.com/forum/showthr...74#post3135974

Hello,

I'm not sure if this is possible, but I thought I'd ask and see if any feedback would help move this project along.

I have this macro to copy/paste data from sheet1 to sheet2. However, it was requested that a form be placed into sheet2 from Microsoft Word. This form is too complex for Excel to handle and I imported it as an object. Is there any way to move the the "object" to the background and have this macro populate the cells required so that it would show on top of the object?

Or perhaps I am missing a step that could properly port such a document into Excel that would allow a copy/paste macro to do its job?

Thank you for any help!


No luck finding an answer? You could always try Google.