PasteSpecial looses formatting from single cell copy to Merged cell Group

spring-innovation

New Member
Joined
Aug 11, 2010
Messages
5
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
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?

Code:
Sheet8.Range(varStepData).Copy Range("F8")
Have you tried just using Copy instead of PasteSpecial?
 

spring-innovation

New Member
Joined
Aug 11, 2010
Messages
5
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 11, 2010
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 11, 2010
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,298
Office Version
  1. 365
Platform
  1. Windows
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.:eek:

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

New Member
Joined
Aug 11, 2010
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,255
Messages
5,657,624
Members
418,404
Latest member
Sneijders

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
Top