Copy cells to another sheet based on font properties?

sepaltoona

New Member
Joined
Aug 30, 2014
Messages
7
I have made a fairly elaborate finance spreadsheet on Sheet1 to track long term financial projections (tracking monthly bills over a year in the future). It contains currency values for known/expected bills and has many formulas. Paid bills in Sheet1 are in regular font, while future bills are in italics (so I know what has been paid vs. what hasn't). Sheet2 mirrors Sheet1, but only shows what bills are already paid up to now (manually updated to reflect current bank account values only after a bill has been paid and clears the bank). At present, when a bill has been paid I remove the italics from the value of a cell in Sheet1 (to mark it as paid), and then just copy it into the same cell on Sheet2. This is annoying to manually add the value from Sheet 1 (after removing the italics) to Sheet2 every time a bill gets paid.

I'm sure there is a way to automatically have the contents (a currency value) of a range of cells in Sheet1 added to the same range of cells in Sheet2, but only for the Sheet1 values that are italicized.

Hopefully someone here has an idea!

Thanks!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sure, there's a way. Something like this perhaps:

Code:
Sub MyCopy()

    Dim rng As Range
    Dim i As Long, lCount As Long
    
    Set rng = Range("MyRangeName")
    
    For i = 1 To rng.Rows.Count
        If rng(i, 1).Font.Italic Then
            lCount = lCount + 1
            rng.Rows(i).Copy Destination:=Sheets("Sheet2").Cells(lCount, 1)
        End If
    Next i
    
End Sub

The question is more: Why?

Why do you need to copy data into another location? Surely you can have a field for date paid, and track the paid invoices/amounts by summing invoices with a paid date, rather than creating an unnecessary copy?
 
Upvote 0
Welcome to the MrExcel board!

Firstly, I think using a font to filter values to another sheet is not a great way to go. If it were me, I would use another column in Sheet1 to show, say, "Paid" and use that to filter the data to Sheet2. If you also wany the Italics to help visually identify, you could use Conditional Formatting based on that "Paid" column to do it.

Never-the-less here is a way that I think does what you want. Test in a copy of your workbook.
I am assuming that the column in Sheet 1 where you remove (or add) the Italics is filled with constants, not formulas. If that is incorrect, please give more details.

To implement ..

1. Right click the sheet name tab of Sheet 2 and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1. Edit the "Const" line in the code to reflect the range in Sheet1 where you want to check for Italics.

3. Close the Visual Basic window & test by going to Sheet1, add/remove Italics and return to Sheet2.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Rich (BB code):
Private Sub Worksheet_Activate()
  Dim rCell As Range, rCellsWithData As Range
  
  Const sRangeToCheck As String = "A2:A1000"  '<- Range in Sheet1 to check
  
  With Sheets("Sheet1")
    On Error Resume Next
    Set rCellsWithData = .Range(sRangeToCheck).SpecialCells(xlConstants)
    On Error GoTo 0
    If Not rCellsWithData Is Nothing Then
      Application.ScreenUpdating = False
      For Each rCell In rCellsWithData
        If rCell.Font.Italic = True Then
          Rows(rCell.Row).ClearContents
        Else
          Rows(rCell.Row).Value = rCell.EntireRow.Value
        End If
      Next rCell
      Application.ScreenUpdating = True
    End If
  End With
End Sub

This code may need a bit more tweaking, so post back with more details if it isn't doing what you want.
 
Upvote 0
Here are two different possibilities. The fist uses conditional formatting, the second identifies in words which items are paid and which are not paid.
You only need put in the forecast once, and then as bills are paid you enter that data once.

Excel Workbook
ABC
1forecastpaid
2ITEM 119
3ITEM 228
4ITEM 337
5ITEM 44
6ITEM 55
7ITEM 66
8
9
10Summaryactual/forecast
11Expense Item 19
12Expense Item 28
13Expense Item 37
14Expense Item 44
15Expense Item 55
16Expense Item 66
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A111. / Formula is =$C2=""Abc
B111. / Formula is =$C2=""Abc
A121. / Formula is =$C2=""Abc
A131. / Formula is =$C2=""Abc
A141. / Formula is =$C2=""Abc
A151. / Formula is =$C2=""Abc
A161. / Formula is =$C2=""Abc
Excel Workbook
EFG
10Summaryactual/forecast
11Expense Item 19Paid
12Expense Item 28Paid
13Expense Item 37Paid
14Expense Item 44Forecast
15Expense Item 55Forecast
16Expense Item 66Forecast
Sheet1
 
Upvote 0
Thanks for the help so far, but I think you need to see exactly what I'm working with to understand completely.

Here are a few images of Sheet1 and Sheet2 so you can see exactly what I'm working with. (Note: Freeze Panes is applied at G9).


Sheet1 (Formulas)

33eo6kk.jpg



Sheet1 (Results)


fe19j6.jpg



Sheet2 (Results - mirror of Sheet1, displaying only the values not italicized from Sheet1)


102ox9c.jpg



Sheet1 displays long-term personal finances to show how a purchase affected finances in the past, or will in the future (ex: adding a car loan, mortgage, baby, etc.). Anything in italics hasn't happened yet or is not a finalized value.

Sheet2 displays only the unitalicized values from Sheet1. As of now, the values in Column L will be increased as more expenses are paid (italicized items from Sheet1). The main reason for Sheet2 is to show me the values of rows 36 and 40.

Basically I want to have the italicized values in Sheet1 automatically display in Sheet2, but ONLY after I remove the value's italics in Sheet1 (to show it has been paid). Sheet2 exists so I can more easily see when an expense has been paid and what the value in the bank account is at that exact moment (a sort of "running total" up to right now). I admit this is a strange way to do it, and maybe someone has a better idea... I am definitely open to more suggestions!

Thanks again for the assistance!
 
Upvote 0
Try this instead of my previous code. Similar instructions but different "Const" lines to check/edit.
Rich (BB code):
Private Sub Worksheet_Activate()
  Dim lc2 As Long, lc As Long, r As Long, c As Long, rws As Long, cols As Long

  Const fr As Long = 9  'First row to check
  Const lr As Long = 34 'Last row to check
  Const fc As Long = 7  'First column to check (7=G)

  rws = lr - fr + 1
  lc2 = Rows(fr).Resize(rws).Find(What:="*", After:=Cells(fr, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
  With Sheets("Sheet1")
    lc = .Rows(fr).Resize(rws).Find(What:="*", After:=.Cells(fr, 1), LookIn:=xlValues, _
      SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
    If lc2 > lc Then lc = lc2
    cols = lc - fc + 1
    Application.ScreenUpdating = False
    For r = fr To lr
      For c = fc To lc
        If .Cells(r, c).Font.Italic Then
          Cells(r, c).ClearContents
        Else
          Cells(r, c).Value = .Cells(r, c).Value
        End If
      Next c
    Next r
    Application.ScreenUpdating = True
  End With
End Sub
 
Upvote 0
Thanks again, Peter_SSs. It's not quite working as intended, but I have an idea which I think "should" be easier to implement. But, again, since I'm not great with macros in excel, I still need help...

It might be easier and more efficient to just remove Sheet2 altogether (as StephenCrump suggested), and instead have a formula adding up a total in Sheet1 somewhere on the side giving me a "current balance" value.

This would mean adding up only the values that are NOT italicized in the following formula: =SUM(G7:G34)+SUM(H4:AAA5)+SUM(H9:AAA34)

So essentially I want all italicized values to be excluded from the cells contained in the formula above.

Thanks!
 
Upvote 0
Also, the more I think about it... it may be visually easier to see what has/hasn't been deducted from the balance by having regular font across all cells (nothing italicized) and then bold the values after they have been deducted (instead of removing italics).

If someone doesn't mind showing me both ways (so I can decide which I prefer better over time), I would really appreciate it!

Thanks!
 
Upvote 0
Here's one way you could set up two sets of results in one sheet. It relies on:

- A custom Cell Styles format which I've called MyStyle (I've made mine bold, italic, greyed-out and different font)
- The user triggering the macro ToggleFormat using a shortcut key, e.g. CTRL-Shift-T, to toggle between MyStyle and normal format.
- The range names MonitoredRange (B1:D7 here) and TotalsExcluded (B10:D10).

When the macro is triggered, it adjust the formulae in TotalsExcluded, e.g. the current formula in B10 is =SUM($B$1,$B$2,$B$6)

The approach is not foolproof, as it relies on the user resisting the urge to change the cell format directly, rather than via CTRL-Shift-T.


Excel 2010
ABCD
1Values111
2222
3333
4444
5555
6666
7777
8
9Sum282828
10Exclude943
11Net sum192425
Sheet1


Code:
Sub ToggleFormat()

    Dim rngMonitored As Range, rngTotalsExcluded As Range, rngCell As Range
    Dim s As String
    Dim r As Long, c As Long
    
    Set rngMonitored = Range("B1:D7")
    Set rngTotalsExcluded = Range("B10:D10")
    
    For Each rngCell In Selection
        With rngCell
            If .Style = "MyStyle" Then
                .Style = "Normal"
            Else
                .Style = "MyStyle"
            End If
        End With
    Next rngCell
    
    For c = 1 To rngMonitored.Columns.Count
        If Not Intersect(Selection, rngMonitored.Columns(c)) Is Nothing Then
            s = ""
            For r = 1 To rngMonitored.Rows.Count
                If rngMonitored(r, c).Style = "MyStyle" _
                    Then s = s & rngMonitored(r, c).Address & ","
            Next r
            If Len(s) Then
                s = "=Sum(" & Left(s, Len(s) - 1) & ")"
                rngTotalsExcluded(c).Formula = s
            Else
                rngTotalsExcluded(c) = 0
            End If
        End If
    Next c

End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top