Copy certain cells format to a different sheet using VBA

0

Hi, I followed all the steps to create the "Idiots proof form for Excel" that is publishes in TeachExcel.com, It is a great tutorial!!  and everything worked to perfection, but I have a question as Im willong to adapt something to my form.. There are some cells that Im using that sometimes I select just part of the text and format it (different colors, or bold or italic), but when I submit the data from the "Form Sheet" to the "Data Sheet", the format is not copied to the "Data Sheet", Any help or advice will be greatly appreciate it. By The way, this is my first time dealing with VBA.

Answer
Discuss

Answers

0

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
Discuss

Discussion

Thanks John_Ru, I'm not actually using the "Copy" command. As I mentioned, I used Don's instructions and adapt it to my needs with other cells. Here is the "macro" that I'm using:

===
 
Code_Goes_Here

Sub Store_Data() ' TeachExcel.com

Dim sourceSheet As Worksheet Dim dataSheet As Worksheet Dim nextRow As Integer   ' Make some sheet variables so we can use those instead of hard-coding sheet references in the code. Set sourceSheet = Sheets("Form") Set dataSheet = Sheets("Data")   ' Get the next empty row from the Data sheet. nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row   ' Input the form values into the Data sheet. dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F4").Value dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("J4").Value dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F6").Value...   'Clear Data sourceSheet.Range("F4").Value = "" sourceSheet.Range("J4").Value = "" sourceSheet.Range("F6").Value = ""...  
End Sub  
Code_Goes_Here

===
As you can see, Im copying the whole module so you can review it better. I did add in this post 3 dots ("...") so you can see that as Im using more than 3 cells, but it is the same code.

Everything works perfect, except that it is not carrying the format tothe "Data Sheet". Thanks again!
dmerikansky Nov 25, '20 at 5:00 pm
I'm struggling to see the code, in future please paste it between the ] and [ square brackets so you have [ CODE]<<your code>> [CODE ].

When you have a line like this:
dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F4").Value

... it will only copy the value (not the formatting).

Try this-  replace it with the following:
sourceSheet.Range("F4").Copy
dataSheet.Cells(nextRow, 1).PasteSpecial Paste:=xlPasteAll

That should copy formats too.

Once you've changed the others you need in a similar way, put the line 
Application.CutCopyMode = False

before End Sub.
John_Ru (rep: 502) Nov 25, '20 at 6:26 pm
Thanks again John_Ru.
Im sorry for not pasting the code as I was supposed to, As I mentioned Im completely new to "coding" and it is becoming a nice challenge for me. Now Im willing to learn more!!. I did what you suggested, but unfortunately it didn’t work. I get no errors, the content is being copied to the DataSheet, but the formats (text color, bold, italics, etc is not being carried out. Ill copy the new code from that module, hoping that this time I'll do it right, and again thanks for your help.
Sub Store_Data()
' TeachExcel.com, Takes data from one worksheet and stores in in the next empty row on another worksheet.
 
Dim sourceSheet As Worksheet
Dim dataSheet As Worksheet
Dim nextRow As Integer
 
' Make sheet variables to use instead of hard-coding sheet references.
Set sourceSheet = Sheets("Form")
Set dataSheet = Sheets("Data")
 
' Get next empty row.
nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row
 
' Input form values in Data sheet.
'Help form John_Ru https://www.teachexcel.com/talk/4861/
 
sourceSheet.Range(“F4”).Copy
dataSheet.Cells(nextRow, 1).PasteSpecial Paste:=xlPasteAll
sourceSheet.Range(“J4”).Copy
'…
 
'Clear Data
sourceSheet.Range("F4").Value = ""
sourceSheet.Range("J4").Value = ""
'…
 
'From John_Ru same URL as above
Application.CutCopyMode = False
End Sub
dmerikansky Nov 26, '20 at 2:13 pm
Thanks for that. Please see Revision 2 to my Answer (and the attached speadsheet). If it works, I leave you to change other bits of your fuller code in a similar way (I won't!) but please Select my Answer.

Good luck!
John_Ru (rep: 502) Nov 26, '20 at 3:31 pm
Thanks again John_Ru!... The code you wrote on your spreadsheet is just exactly what I needed. Im studing your code right now, and I allready have some doubts that I hope I will be able to figure out and solve them by myself. As in your code, you are writing the data in a column (in the dataSheet), and I need to write the data in a row (as there are over 20 fields in the form that Im populating, and every time I submit the form, it needs to be writen in the next available row. Definetly, I dont want you to do it for me! :) ... Hopefully Ill be able to do so in a self learning way. I learnd a lot watching Don's videos and now with your help. Once again, Thank you vey much. I hope I wont need to bother you again. And definitelly, your answer is now going to be selected!! Variatus: Thank yo as well for your effort and help!
dmerikansky Nov 27, '20 at 12:14 am
Great!.If the code is "exactly what" you need, kindly mark my Answer as Selected.

Normally we just answer a question (and any exta questions must be dealt with separately) but here are a few small of tips to you:

1) It's much simpler if the destination sheet is pre-formatted (by column say) so there's no need to do full cell copying (including character level copying). You can then use statements like dest.Cells(R,C ) = source.Cells(r,c ), even skippnig the .Value bit.

2) If you're populating a sheet with several columns, you can increment the "c" bit of Cells(r,c ) to step through (or target specific) columns

3) See Don's tutorial  Loop through a Range of Cells in Excel VBA/Macros
John_Ru (rep: 502) Nov 27, '20 at 1:52 am
Forgot to say, if you really need to use my code (for character-level formatting), the fact that it uses a Range means it can copy several cells at once and so may be useful in copying data over several columns. You can even copy a non-contiguous set of cells...

Say row 3 of your source sheet has 10 columns but you only want to copy row cells from A to E, (skip 2 columns) plus H and I, then change the appropriate line of the code for this:
sourceSheet.Range("A4:E4,H4:I4").Copy
If the next row in Data is 5, those cells (and all their different  formatting) will be copied into Data, starting at cell A5 :-)
John_Ru (rep: 502) Nov 27, '20 at 4:33 am
Add to Discussion
0

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.

Discuss

Discussion

Variatus. Agreed, I don't think character formatting within a cell is helpful and copying such formatting between cells doesn't work (though I've used it to highlight searched words in narrative cells within VBA search results, which is a little time consuming as you say). 

My suggested sub (with xlPasteAll) WILL copy the whole cell from the other sheet, including character formattting. (I tested it with a string "This is a test" where only "This" was in italics, "is" was in red "a" was in blue and a larger font and "test" was in bold).

I agree too that swapping the operator to xlPasteFormats (in that sub) does NOT work for character formatting when copying between sheets. You get cell colour, borders etc. but the text will still be plain in the target cell.
John_Ru (rep: 502) Nov 23, '20 at 5:14 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login