PasteSpecial looses formatting from single cell copy to Merged cell Group

[spring-innovation]

When I copy data from one single cell to another single cell, everything is perfect.

When I copy data from one single cell to a merged group of cells, I get the text, however I loose the the text colors and highlights.

Somehow it appears that the merged cells loose formatting. How can I correct this?

<------------------------------------------------------->

On Error GoTo ErrorHandler:

If Intersect(Target, Range("A8:A25")) Is Nothing Then Exit Sub


If Target.Text = "" Then Exit Sub

varDataRange = Sheet8.Range("A1:A200")
varRownum = Application.Match(Target.Value, varDataRange, 0)
varStepData = "C" & Trim(Str(varRownum))
varNoteData = "D" & Trim(Str(varRownum))
Sheet8.Range(varStepData).Copy
Range("F8").PasteSpecial



Range("F23").Value = Sheet8.Range(varNoteData)

ErrorHandler:

Resume Next

 

[Norie]

This is one reason that it's a good idea to avoid merged cells where possible.

What exactly are you trying to do with this code?

Is it event code? ie code triggered by a change in a worksheet

What ranges are merged?

Sheet8.Range(varStepData).Copy Range("F8")

Have you tried just using Copy instead of PasteSpecial?

 

[spring-innovation]

Thanks Norie for your quick response.

On Sheet1, I have Rows 8 through Row 25 listing multiple tasks in a project.
The A column is the task number, B is the task Title

I then have an area F8:F22 for the task details and F23:F28 for any special notes.

My Sheets number from 1 - 7 and named after each project phase
On Sheet 8 (All Tasks) I have all my tasks and descriptions for all phases.

My objective is to click on the task number and have the information matched from Sheet 8 (All Tasks) and populate F8:F23 on my active sheet.


It works however, I loose my color and highlights.

My customer wants to be presented with an excel spreadsheet that lists all the phases, I thought this would be the cleanest method to present within Excel.
--------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ErrorHandler:

If Intersect(Target, Range("A8:A25")) Is Nothing Then Exit Sub


If Target.Text = "" Then Exit Sub

varDataRange = Sheet8.Range("A1:A200")
varRownum = Application.Match(Target.Value, varDataRange, 0)
varStepData = "C" & Trim(Str(varRownum))
varNoteData = "D" & Trim(Str(varRownum))
Sheet8.Range(varStepData).Copy Range("F8:F22")
Range("F8").PasteSpecial



Range("F23").Value = Sheet8.Range(varNoteData)

ErrorHandler:

Resume Next

End Sub

 

[Norie]

What cells are merged and why are they merged?

Also why are you using PasteSpecial anyway?

That's particularly confusing because you don't specify any arguments for it, so you'll end up with the defaults.

The first argument determines what you are pasting and the default is xlPasteAll, which means everything is pasted.

Is that what you want/need?

If you just want values you could try xlPasteValues

 

[spring-innovation]

What cells are merged and why are they merged?

The destination Sheet has the merged Cells F8:F23.
They are merged to provide an "Expanded Viewable Area of the content" without affecting the row heights. It also reduces the need to scroll during a presentation.

Also why are you using PasteSpecial anyway?

I went down a rabbit trail and thought that "PasteSpecial" would capture not only the text but also the font color & formatting. The code works perfectly when I copy one single cell to another single cell. However when I copy one single cell to an area on the spreadsheet where there is a merged cell group, it copies text only and looses the formatting.

I can email you the file.

Once again I appreciate your help, knowledge and quick responses to my inquiries.

 

[Norie]

I'll PM you with my email address - I've not seen a workbook using merged cells recently, might be interesting.

It might be worth looking up the various arguments for PasteSpecial in help.

Like I said without specifying any you'll get the defaults.

If you do specify them then you can do things like paste values, paste formats etc.

In your situation it might be that you'll need more than one paste special operation, eg one for the values, one for the formats etc.

 

[spring-innovation]

Great! now that you have the file.

Verify that your macros are enabled.
On the deployment tab, click on step 101, then click on 102 - notice how the information changes in "F8" (Merged Cell Area)
Everything appears to be working fine.

However, if you work your way to the Steps Sheet. Search for step 101 and 102. Notice in the Details column that the content in "C5" and "C6" has colored text.

On Deployment:F8 Steps:C5 and Steps C:6 removes the color. I need the color to appear.

 

[Norie]

Eh, I don't think I need to enable macros - there kind of constantly enabled here, along with a few other things. eg break on all errors.

I sort of worked out what some of the code was doing after a closer look and can see why you are using SelectionChange, I think anyway.

I tried a few things and some worked.

But I was also getting #VALUE! errors on the worksheet and actual VBA errors.

In fact at one point I had to shut Excel down.

How extensively are you using the merged cells?

I'l take a closer look later - have you considered using textboxes?

Or heaven forbid a userform.

 

[spring-innovation]

Thanks again.

One each Sheet, I have two merged cell areas for "Details" and "Notes"


No - I didn't know we could use textboxes. Now that you see what I'm trying to do, I'm open to any other method. Would text boxes automatically display a scroll bar if I have extensive detail?

How do I go about using textboxes?

I also noticed that Excel crashes when I select an entire row.
Not sure why that's happening.