Copy certain cells format to a different sheet using VBA

Hi.

I haven't done that tutorial but imagine it's up to Don's usual high standard :-)

Try this:

Sub CopyAllToCell()
    Worksheets("Form Sheet").Range("A1").Copy
    Worksheets("Data Sheet").Range("B1").PasteSpecial Paste:=xlPasteAll  
    Application.CutCopyMode = False ' just removes the dash "copy" lines around the original cell
End Sub

but change the bits in bold above (A1 to a cell in Form Sheet you've formatted and B1 to where you want the same contents to appear in Data Sheet.

The Paste:=xlPasteAll bit means you copy cell contents, format, colour. If you change that for Paste:=xlPasteFormats, then whatever is in B1 will just get the formats from A1. 

The ranges need not be just a single cell but the code above illustrates the method. Hope this helps.

REVISION 1: Note that Paste:=xlPasteAll will make the second cell the same as the copied one (including colour/size/formatting at a character level e.g. "Tell me") BUTPaste:=xlPasteFormats will copy cell colour, borders etc, but not formatting at a character level e.g to a different string. 

REVISION 2: Following the discussion points below, please see attached a spreadsheet where I've modified the code from the questioner.

The second line of the code above does not work with Cells(x,y) since a Range is required for this methodbut that can be fixed easily...

In the code below, the bold bits are changes to the user's code. Essentially a new variable nextCell is created (for the range to paste to) as column "A" & (=add string) the nextRow number from Don's code.

In the Form sheet, I've made a wierdly formatted cell F4 (to be copied) and added a green shape (which can be clicked to run the macro CopyAllToCell() (behind that sheet. You can check the results in the Data sheet- all the weird formatting is copied :-)

Sub CopyAllToCell()

Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer, nextCell As String

Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Data")

'get next unused row in dataSheet
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
'create a string for the range to paste to
nextCell = "A" & nextRow

    sourceSheet.Range("F4").Copy

    dataSheet.Range(nextCell).PasteSpecial Paste:=xlPasteAll ' xlPasteFormats won't copy character formatting

    Application.CutCopyMode = False

End Sub

Character formats are not included in the cell formats you can transfer from cell to cell or range to range using Copy/Paste. Instead, character formats are applied to each character individually and the only way to get at them is to examine each character.

So, in theory, you could take a string from a cell's value, check each character's colour, and then apply the same colour to the matching character in the copy. This effort might be acceptable if you know which cell has coloured characters, and it might be reduced if you know which characters (by their positions) are coloured, and which colours are involved. If the task is to check all copies and act only where applicable the time taken will make the method prohibitive.

Bear in mind that reading from the worksheet is a very slow process. It takes several times longer than reading from a variable in the computer's memory. While this process is limited to a few cells the extra time really doesn't matter. But since you can't copy character formats into memory you would have to refer to the worksheet for each character's colour and the time lag is likely to become noticable.

Therefore it's best to forget about reading existing character colours (or other formats, such as Italics or bold). Instead, focus on the rules by which such colour is applied, examine if the rules are met and apply the colour whether the original had it or not.