0

I'm trying to create a code that when selecting a particular click box the contents of a specific cell gets copied over to a new cell on a new page. The code that I currently have is:

Sub CBCR71b_Click()
If ActiveSheet.CheckBoxes("CBCR71b").Value = 1 Then
Sheets("ELA Output").Range("CR7.1b").Value = Sheets("ELA").Range("cr1b").Value
Else
Sheets("ELA Output").Range("CR7.1b").Value = ""
End If

This works fine if I only want the value of the cells copied over. In this project that would be text. But now I need to be able to move text where some of the words have bold or italics. The above code ignores that.

The other simiar questions on this site run through a range of cells so am not sure if the clickbox is running interference. I have tried replacing .value with .font.bold, but then nothing happens.

Any help would be appreciated!

Community
  • 1
  • 1
Paul Owens
  • 1
  • you can try: Worksheets copy destination thing. See [Tim's answer](http://stackoverflow.com/questions/16698644/vba-how-to-copy-the-content-of-a-cell-without-select) – ZAT Jan 23 '15 at 17:47
  • Tim's Answer from yesterday worked copying over the bold, etc! Thanks so much. Only thing I noticed was that on the output page I shaded borders to make the print version look good. Tim's code took away the output cells borders. – Paul Owens Jan 24 '15 at 19:10
  • Try as Santosh commented in that post. Maybe you can try copy and pastespecial as well. – ZAT Jan 26 '15 at 08:20

1 Answers1

0

You could use AdvancedFilter to copy the data as blow:

Sub copyAdvancedFilter()

Dim rnSource As Range
Dim rnTarget As Range

    Set rnSource = Range("A1:A2")
    Set rnTarget = Range("C1:C2")

    rnSource.AdvancedFilter Action:=xlFilterCopy, _
                                CopyToRange:=rnTarget
End Sub

Just adjust in your function.

Please let me know if it worked.

Cheers! :wq!

Evis
  • 571
  • 8
  • 22
  • I apologize for this, but I am very new at this and am unsure of how to do this. Do I put this code into the existing code? Also, for the Range ("A1:A2") can I just put the name of the cell like "CR7.1b"? – Paul Owens Jan 23 '15 at 21:51
  • Hello Paul, I think you probably had already solved... right? If not, please confirm if the range you mentioned is named as 'CR7.1b'. If so, substitute the 'SET' range name to your range name and substitute your line: 'Sheets("ELA Output").Range("CR7.1b").Value = Sheets("ELA").Range("cr1b").Value' with the code: 'rnSource.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rnTarget', ok? Cheers! – Evis Jan 28 '15 at 16:33